Submitting batches of SQL statements to Oracle via MDAC ODBC driver/ADO .Execute method? problems!

Submitting batches of SQL statements to Oracle via MDAC ODBC driver/ADO .Execute method? problems!

Post by Alastair Camero » Thu, 20 Sep 2001 07:46:58



I have a VB6 application which reads blocks of SQL statements from a text
file, and executes them via the ADO .Execute method. The command that is
being sent through to the .Execute method will contain multiple SQL
statements.

This works perfectly for SQL Server. The application has now being modified
to work also on Oracle (the SQL is very similar).

A typical batch of statements is shown below, and typically consists of
CREATE/SELECT/UPDATE/INSERT INTO statements. Nothing fancy. Each statement
may span multiple lines; in addition the statements are obviously separated
by CR/LF and the TAB character appears in places.

I've had problems with the MS Oracle ODBC driver returning invalid character
after the ADO .Execute method has been submitted.

Do I need to include the ORACLE statement terminator (;) after every
statement in the batch? are -- comments acceptable?

Do I need a COMMIT; after a CREATE TABLE and before any other commands (such
as SELECT) which use it in the same batch?

Anything else I need to be aware of when submitting batches of statements?

I've only ever executed single statements at a time, but the format of the
file is such that I don't want to parse it statement by statement, and
besides both Oracle and SQL Server supports execution of batches of SQL
statements, and the SQL server version of the App is working perfecrtly.

Many thanks.

Alastair

------------------------------------->

-- This is a comment
-- This is another comment

CREATE TABLE grde_cbe (
 cbe       VARCHAR2(10),
 supertype     NUMBER(1,0),
 default_nam_scm   VARCHAR2(10),
 default_nam_scm_name VARCHAR2(255),
 cbe_name      VARCHAR2(255),
 cbe_sys_lbl     VARCHAR2(255),
 acc       VARCHAR2(10))

-- This is a third comment

INSERT INTO
 grde_cbe
SELECT
 t.object_id,
 1,
 rns.thing3,
 nstx.text_value,
 tx.text_value,
 lbl.value,
 NULL
FROM
 thingt  t,
 thingt  rns,
 textt  tx,
 thingt ns,
 textt nstx,
 identifier_details lbl

 
 
 

Submitting batches of SQL statements to Oracle via MDAC ODBC driver/ADO .Execute method? problems!

Post by Arne Ortlinghau » Thu, 20 Sep 2001 15:11:21


Hello Alastair,

I had to parse all the statements to execute them by Oracle via ODBC.
Perhaps it is possible to execute them as a anonymous block (togehter with
begin and end):
begin
   statements
end;
Create table and other commands which change definitions do not have to be
committed.

Arne

 
 
 

Submitting batches of SQL statements to Oracle via MDAC ODBC driver/ADO .Execute method? problems!

Post by Alastair Camer » Sat, 22 Sep 2001 18:40:16


Arne,

I just couldn't multiple statements to work, and had to parse my SQL
file statement by statement.

It seems to work fine.

One more point. I really need to define an ORACLE PROCEDURE using
CREATE OR REPLACE PROCEDURE this way as well and sent it through the
ADO .execute method.

Defining a procedure is slightly different in that with the
CREATE/BEGIN/END Oracle may treat it as a single 'statement'.

Do you know if I can pass through the whole procedure declaration as
one statement via ADO/ODBC?

If not, then presumably I can't pass through each line of the
procedure declaration, can I?

Alastair


> Hello Alastair,

> I had to parse all the statements to execute them by Oracle via ODBC.
> Perhaps it is possible to execute them as a anonymous block (togehter with
> begin and end):
> begin
>    statements
> end;
> Create table and other commands which change definitions do not have to be
> committed.

> Arne

 
 
 

1. Submitting batches of SQL statements to Oracle via MDAC ODBC driver/ADO .Execute method? problems!

I have a VB6 application which reads blocks of SQL statements from a text
file, and executes them via the ADO .Execute method. The command that is
being sent through to the .Execute method will contain multiple SQL
statements.

This works perfectly for SQL Server. The application has now being modified
to work also on Oracle (the SQL is very similar).

A typical batch of statements is shown below, and typically consists of
CREATE/SELECT/UPDATE/INSERT INTO statements. Nothing fancy. Each statement
may span multiple lines; in addition the statements are obviously separated
by CR/LF and the TAB character appears in places.

I've had problems with the MS Oracle ODBC driver returning invalid character
after the ADO .Execute method has been submitted.

Do I need to include the ORACLE statement terminator (;) after every
statement in the batch? are -- comments acceptable?

Do I need a COMMIT; after a CREATE TABLE and before any other commands (such
as SELECT) which use it in the same batch?

Anything else I need to be aware of when submitting batches of statements?

I've only ever executed single statements at a time, but the format of the
file is such that I don't want to parse it statement by statement, and
besides both Oracle and SQL Server supports execution of batches of SQL
statements, and the SQL server version of the App is working perfecrtly.

Many thanks.

Alastair

------------------------------------->

-- This is a comment
-- This is another comment

CREATE TABLE grde_cbe (
 cbe       VARCHAR2(10),
 supertype     NUMBER(1,0),
 default_nam_scm   VARCHAR2(10),
 default_nam_scm_name VARCHAR2(255),
 cbe_name      VARCHAR2(255),
 cbe_sys_lbl     VARCHAR2(255),
 acc       VARCHAR2(10))

-- This is a third comment

INSERT INTO
 grde_cbe
SELECT
 t.object_id,
 1,
 rns.thing3,
 nstx.text_value,
 tx.text_value,
 lbl.value,
 NULL
FROM
 thingt  t,
 thingt  rns,
 textt  tx,
 thingt ns,
 textt nstx,
 identifier_details lbl

2. Migration from MS Access to SQL Server 2000

3. Monitoring all SQL statements

4. ADO problem with multiple SQL statements using execute method

5. showplan on

6. Gurus will slam me for this post... VB 6 code

7. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

8. How do I submit a batch of statements to SQL server

9. MS ODBC for Oracle from MDAC 2.7 is slower than the MDAC 2.6 driver

10. Submit lengthy SQL script file using ADO .Execute?