Multi-statements SQL and stored_procedure calls

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();

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 Received on Sun May 08 2005 - 14:22:20 CEST