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;

asked 19 Dec '19, 09:06

Ilia63's gravatar image

Ilia63
1.2k505578
accept rate: 44%


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 :)

permanent link

answered 20 Dec '19, 09:21

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 20 Dec '19, 14:01

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
1

Yeah, one of the most useful functions at all...

(22 Dec '19, 13:08) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×229

question asked: 19 Dec '19, 09:06

question was seen: 673 times

last updated: 22 Dec '19, 13:08