on 2017 May 16 9:03 AM
Hi, I wanted to ask, is it possible to have a conditional mobilink upload script? Example: upload_insert script: if ({ml r."column1" = 'specific_value'}) then insert into "Table1" ("column1", "column2") Values ({ml r."column1"}, {ml r."column2"}) end if;
This script is unable to be executed, is there any other solution?
Thanks in advance
Request clarification before answering.
You'll have to write the script as a stored procedure.
call ml_add_table_script( 'v1', 't1', 'upload_insert', 'call t1_ui( {ml r.c1}, {ml r.c2}'); create procedure t1_ui( in @c1 integer, in @c2 integer ) begin if ( @c1 = specific_value ) then insert into t1 values ( @c1, @c2 ); end if end;
Reg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I'm not 100% convinced you can't code the if directly, if you actually get the braces in the right place...
if ({ml r."column1"} = 'specific_value') then insert into "Table1" ("column1", "column2") Values ({ml r."column1"}, {ml r."column2"}) end if;
...and if the host consolidated server doesn't choke on the syntax.
Coding procedures for multi-column tables is a [cough] arduous [/cough] task on a good day 🙂
I receive the following error:
E. 2017-05-17 12:01:05. <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)
W. 2017-05-17 12:01:05. <1> [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode
E. 2017-05-17 12:01:05. <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-17 12:01:05. <1> [-10072] Unable to insert into table 'CUSTOMER' using upload_insert
moreover, I noticed that the problem is not in having a condition in the script, and also noticed that everything within the condition block was correct. I have just replaced the first line of condition and it simply worked (w/o doing my intended filter): if (1=1) then insert into "Table1" ("column1", "column2") Values ({ml r."column1"}, {ml r."column2"}) end if;
I can figure out that the syntax {ml r....} is not allowed in the condition!!!!
Reg's answer is spot on. I'll add one general clarification that may help you: you must unconditionally pass in the {ml ...} values in the script that calls the stored procedure, and within the procedure itself you conditionally handle the parameters to the stored procedure. As you found out, within the stored procedure there is no access to the {ml ...} values.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Do you think that I can do it without writing an if condition at the begining of script? Instead writing so a crazy line:
insert into "Table1" ("column1", "column2") Values ({ml r."column1"}, {ml r."column2"}) where {ml r."column1"} = 'specific_value'
Is it acceptable to write a conditional input statement?
INSERT does not take a WHERE clause unless you use the INSERT ... SELECT variant... - I don't know whether you could turn your input parameters into a dummy FROM clause, such as
insert into "Table1" ("column1", "column2") select ({ml r."column1"}, {ml r."column2"}) from sys.dummy where {ml r."column1"} = 'specific_value'
FWIW, I'd recommend to use mlsrv10 -vt to display the "translated" script for further studying such script tests... - AFAIK, parameters will still be shown as such and not with their value but you should be able to see whether that leads to valid syntax on the consolidated or not...
User | Count |
---|---|
46 | |
9 | |
8 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.