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

asked 16 May, 08:03

Sarkis's gravatar image

Sarkis
24051028
accept rate: 0%


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

permanent link

answered 16 May, 08:52

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.4k33579
accept rate: 39%

Thanks!! I was just forgetting this oppurtinity, that I can call a procedure.

Thank you.

(16 May, 09:19) Sarkis
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, 11:23) Breck Carter

Did you like those weasel words, "not 100% convinced"? :)

(16 May, 11:30) Breck Carter

I tried this one but it didnt work!!!

(16 May, 16:58) Sarkis

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:55) Volker Barth

it is SQL Anywhere 10

(17 May, 04:09) Sarkis

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, 06:03) Sarkis

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, 06:13) Sarkis

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, 06:23) Sarkis
Replies hidden

Can you show me the script you've defined and the stored procedure you've written.

(17 May, 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, 14:22) Sarkis
showing 4 of 11 show all flat view

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.

  • Russ
permanent link

answered 17 May, 08:49

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030
accept rate: 18%

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

  • Russ
(17 May, 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, 14:28) Sarkis
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, 14:50) Volker Barth

Is the stored procedure not working?

(17 May, 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, 15:16) Sarkis

I will try this option.

(17 May, 15:17) Sarkis

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, 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, 09:48) Reg Domaratzki
showing 4 of 9 show all flat view
Your answer
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:

×299

question asked: 16 May, 08:03

question was seen: 275 times

last updated: 18 May, 09:48