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

asked 18 May '17, 11:55

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 18 May '17, 11:57

Thanks for bringing that up... I'd think in/out refers to user-deined parameters ("{ml.u ")...

(18 May '17, 13:31) Volker Barth
Replies hidden

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?

(18 May '17, 14:01) Breck Carter
1

On the surface, this does not appear to be the expected behavior. We are investigating and will report back.

(19 May '17, 11:48) Chris Keating
Comment Text Removed
1

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.

(30 May '17, 09:24) Chris Keating
Be the first one to answer this question!
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:

×2

question asked: 18 May '17, 11:55

question was seen: 1,113 times

last updated: 30 May '17, 09:24