Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.2k515782
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.5k5417261050
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:

×246

question asked: 19 Dec '19, 09:06

question was seen: 763 times

last updated: 22 Dec '19, 13:08