on 2019 Dec 19 9:06 AM
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;
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
SELECT LIST ( "dummy_col", ':ls_delim' ) INTO :ls_res FROM "dummy";
Commas get all the publicity, but sometimes quotes matter too 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
> investigated as a defect
OK but . . .
. .. it's too valuable! 🙂
Yeah, one of the most useful functions at all...
User | Count |
---|---|
64 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.