SQL Anywhere Network Server Version 17.0.10.5963 / Power Builder Classic Version 12.1 build 6639.
When working with ISQL, using a variable as a delimiter occurs without problems. For example:
|
It looks like the host variable mechanism works OK when the SELECT is simple SELECT :ls_whatever INTO :ls_res FROM dummy; but it doesn't "reach down" into the LIST() call SELECT LIST ( "dummy_col", :ls_delim) INTO :ls_res FROM "dummy"; possibly because according to the SQL Anywhere Help for LIST() "The delimiter string must be a constant". (Yes, that violates The Rule "SQL Anywhere does things the way they should be done") If you like kludges, here's one... string ls_res string ls_delim string ls_sql ls_delim = "," //char(13) SQLCA.DBMS = 'ODB'; SQLCA.DBParm = "ConnectString='DSN=" & + "Inventory17" & + ";CON=RRLoadTest;ASTART=NO'" & + ",ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'" CONNECT USING SQLCA; ls_sql = "CREATE OR REPLACE VARIABLE v_res LONG VARCHAR;" EXECUTE IMMEDIATE :ls_sql USING SQLCA; ls_sql = "SELECT LIST ( row_num, '" + ls_delim + "' ) INTO v_res FROM sa_rowgenerator ( 1, 10 );" EXECUTE IMMEDIATE :ls_sql USING SQLCA; SELECT v_res INTO :ls_res FROM dummy USING SQLCA; MessageBox ( 'ls_res', ls_res ) RETURN Here's the solution that looked like it worked, but only because the LIST only had one entry...
1
This is being investigated as a defect. The LIST should permit the delimiter to be a constant or equivalent to constant. The host variable should qualify as the later.
(21 Dec '19, 21:32)
Chris Keating
Replies hidden
2
> investigated as a defect OK but . . . . .. it's too valuable! :)
(22 Dec '19, 09:28)
Breck Carter
|