SAP SQL Anywhere

Question

SQL Anywhere Network Server Version 17.0.10.5963 / Power Builder Classic Version 12.1 build 6639.
I get the following error when working with Embedded SQL:

string ls_res
string ls_delim
ls_delim = char(13)
SELECT LIST ( "dummy_col", :ls_delim) INTO :ls_res FROM "dummy";

SQLCODE=-1
SQLDBCODE=-156
SqlSyntax: SELECT LIST ( "dummy_col" , ? ) FROM "dummy" 
SQLSTATE = 37000
[SAP][ODBC Driver][SQL Anywhere]Invalid expression near 'LIST(dummy.dummy_col,:?)'

When working with ISQL, using a variable as a delimiter occurs without problems. For example:

BEGIN
DECLARE @ls_res  long varchar;
DECLARE @ls_delim varchar;
set @ls_delim = char(13);
SELECT LIST ( "dummy_col", @ls_delim) INTO @ls_res FROM "dummy";
END;

Answer 1

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...

SELECT LIST ( "dummy_col", ':ls_delim' ) INTO :ls_res FROM "dummy";

~~Commas get all the publicity, but sometimes quotes matter too :)~~

SAP SQL Anywhere

Embedded SQL: Invalid expression near LIST

Follow this question

Related questions