Hello guys,
I would like my app to generate and execute cross platform (sybase and
oracle) multi-statement SQL in the form:
(situation A)
SQL += BEGIN();
SQL += SP("my_stored_proce", "arg1, arg2, arg3") + EOS();
SQL += "INSERT INTO my_table VALUES(1)" + EOS();
SQL += END();
dbExecute(SQL); // ODBC
or only:
(situation B)
SQL = SP("my_stored_proce", "arg1, arg2, arg3")
dbExecute(SQL); // ODBC
In sybase, the functions BEGIN, END, SP, and EOS are:
BEGIN() returns "BEGIN"
END() returns "END"
SP(my_sp, args) returns "EXEC " + my_sp + " " + args
EOS() returns "\n"
In oracle, I define these functions:
BEGIN() returns "BEGIN"
END() returns "END;"
EOS() returns ";\n"
But the problem is with the definition of SP
- if SP(my_sp, args) returns "EXEC " + my_sp + " (" + args + ")"
then (situation B) works but (situation A) fails because apparently you
cannot have EXEC inside a begin...end block
- if SP(my_sp, args) returns my_sp + " (" + args + ")"
then (situation A) works but (situation B) fails because oracle does not
understand this is a sp call
- if SP(my_sp, args) returns "BEGIN "my_sp + " (" + args + "); END;"
then (situation B) works but (situation A) fails because you can't have 2
semi columns (;;) in a statement.
Does anyone have any suggestions of how one could define SP so that it works
in both situations above (A and B)?
Many thanks in advance & Regards,
SerGioGioGio