|
Why does the following upload_insert script get the (in)famous "Not enough values for host variables" error message in 16.0.0.2419? The Help says "With the exception of in/out parameters, you can specify the same named parameter more than once within a script." Clearly, I am missing / have forgotten some fundamental point... :) (BTW, how does one tell if a parameter is in/out, in this context?) CALL ml_add_table_script ( 'v1', 't1', 'upload_insert', '
IF {ml r."non_key_1"} = ''MagicValue'' THEN
INSERT t1
( key_1,
key_2,
non_key_1,
non_key_2 )
VALUES ( {ml r."key_1"},
{ml r."key_2"},
{ml r."non_key_1"},
{ml r."non_key_2"} )
ELSE
INSERT t1
( key_1,
key_2,
non_key_1,
non_key_2 )
VALUES ( {ml r."key_1"},
{ml r."key_2"},
{ml r."non_key_1"},
''SorryLoser'' )
END IF;' );
I. 2017-05-18 11:33:52. <1> (,1) upload_insert t1
IF {ml r."non_key_1"} = 'MagicValue' THEN INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, {ml r."non_key_2"} ) ELSE INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, 'SorryLoser' ) END IF;
I. 2017-05-18 11:33:52. <1> (,1) Translated SQL:
IF ? = 'MagicValue' THEN INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, ? ) ELSE INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, 'SorryLoser' ) END IF;
E. 2017-05-18 11:33:52. <1> (,1) [-10002] Consolidated database server or ODBC error: ODBC: [Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables (ODBC State = 07002, Native error code = -188)
I. 2017-05-18 11:33:52. <1> (,1) System event on synchronization connection:
rollback to savepoint it1
I. 2017-05-18 11:33:52. <1> (,1) Translated SQL:
rollback to savepoint it1
W. 2017-05-18 11:33:52. <1> (,1) [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode
I. 2017-05-18 11:33:52. <1> (,1) upload_insert t1
IF {ml r."non_key_1"} = 'MagicValue' THEN INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, {ml r."non_key_2"} ) ELSE INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, 'SorryLoser' ) END IF;
I. 2017-05-18 11:33:52. <1> (,1) Translated SQL:
IF ? = 'MagicValue' THEN INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, ? ) ELSE INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, 'SorryLoser' ) END IF;
E. 2017-05-18 11:33:52. <1> (,1) [-10002] Consolidated database server or ODBC error: ODBC: [Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables (ODBC State = 07002, Native error code = -188)
E. 2017-05-18 11:33:52. <1> (,1) [-10072] Unable to insert into table 't1' using upload_insert
I. 2017-05-18 11:33:52. <1> (,1) Error Context:
I. 2017-05-18 11:33:52. <1> (,1) Remote ID: 4e842b86-5b13-4463-b427-5f4299896243
I. 2017-05-18 11:33:52. <1> (,1) User Name: 1
I. 2017-05-18 11:33:52. <1> (,1) Modified User Name: 1
I. 2017-05-18 11:33:52. <1> (,1) Transaction: Upload
I. 2017-05-18 11:33:52. <1> (,1) Table Name: t1
I. 2017-05-18 11:33:52. <1> (,1) Insert Row:
I. 2017-05-18 11:33:52. <1> (,1) 1000000001
I. 2017-05-18 11:33:52. <1> (,1) 1
I. 2017-05-18 11:33:52. <1> (,1) MagicValue
I. 2017-05-18 11:33:52. <1> (,1) Kryptonite
I. 2017-05-18 11:33:52. <1> (,1) Script Version: v1
I. 2017-05-18 11:33:52. <1> (,1) Script: IF {ml r."non_key_1"} = 'MagicValue' THEN INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, {ml r."non_key_2"} ) ELSE INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( {ml r."key_1"}, {ml r."key_2"}, {ml r."non_key_1"}, 'SorryLoser' ) END IF;
I. 2017-05-18 11:33:52. <1> (,1) End of Error Context
|
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.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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:
question asked: 18 May '17, 11:55
question was seen: 1,185 times
last updated: 30 May '17, 09:24
Thanks for bringing that up... I'd think in/out refers to user-deined parameters ("{ml.u ")...
Yes, I see that. There are also some (well at least one) "s." parameters that are documented as INOUT like s.authentication_message in the authenticate_parameters event.
However, that doesn't answer the question about the r.column_name parameters... are they always IN?
On the surface, this does not appear to be the expected behavior. We are investigating and will report back.
Status update: We confirmed that this behavior only appears to occur in SQL Anywhere and have a repro that does not involve MobiLink showing the issue. We continue to investigate.