Conditional MobiLink script, revisited

[Breck_Carter]

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

[VolkerBarth]

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

[Breck_Carter]

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?

[chris_keating]

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

[chris_keating]

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.