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