|
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 |
|
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 Thanks!! I was just forgetting this oppurtinity, that I can call a procedure. Thank you.
(16 May '17, 09:19)
Baron
Replies hidden
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 :)
(16 May '17, 11:23)
Breck Carter
Did you like those weasel words, "not 100% convinced"? :)
(16 May '17, 11:30)
Breck Carter
I tried this one but it didnt work!!!
(16 May '17, 16:58)
Baron
What DBMS is your consolidated? The required script should be valid syntax when run via an ODBC connection (with substituted ML parameters, apparently), so you might need to adapt the syntax - if it is supported at all... Just wild guessing on my part.
(16 May '17, 17:55)
Volker Barth
it is SQL Anywhere 10
(17 May '17, 04:09)
Baron
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
(17 May '17, 06:03)
Baron
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!!!!
(17 May '17, 06:13)
Baron
I know that another choice is to write the condition in the publication on the remote side, but just wanted to regulate it from a single point on the cons., as I have several remotes connecting to the same cons.
(17 May '17, 06:23)
Baron
Replies hidden
Can you show me the script you've defined and the stored procedure you've written.
(17 May '17, 07:53)
Reg Domaratzki
I accepted the answer from Reg, but I did not apply it (i.e. I did not write a call to a procedure inside the script). From inside the script was also unable to handle this condition!! Should I conclude that there is no way to have a selective upload_insert script?
(17 May '17, 14:22)
Baron
|
|
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.
But whay about Breck's suggestion to use a code block instead of a procedure call. Does that work including an if statement testing a row value parameter?
(17 May '17, 09:55)
Volker Barth
MobiLink just passes in the values as parameters. What happens afterwards is specific to the consolidated database. If you can get the MobiLink-passed-in value into a local variable in the block, then that should let you do the conditional processing.
(17 May '17, 10:10)
RussC_FromSAP
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?
(17 May '17, 14:28)
Baron
Replies hidden
1
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...
(17 May '17, 14:50)
Volker Barth
Is the stored procedure not working?
(17 May '17, 15:07)
Reg Domaratzki
I didn't try calling stored procedure from within script, but I think it will be more difficult than writing it directly in the script.
(17 May '17, 15:16)
Baron
I will try this option.
(17 May '17, 15:17)
Baron
It is likely more difficult trying to do this in an event script depending on how you plan to construct the condition. I would follow the advice offered to make use of a procedure.
(17 May '17, 16:33)
Chris Keating
Given the trouble you are running into, and the amount of effort put into this thread already, I believe that writing a stored procedure will be easier.
(18 May '17, 09:48)
Reg Domaratzki
|
