Executing SQL via MDAC ODBC multiple statements?

[Richard Adams]

Is it possible to execute more than one statement in SQL via MDAC
ODBC? I have a fairly complex select I wanted to create a view with,
but trying to send it all as one string with terminators ';' between
doesn't work (invalid character.) How can I do this kind of thing
from VB.NET?

Thanks in advance
Richard

[Justin Cave]

Re: Executing SQL via MDAC ODBC multiple statements?

ackthpt@concent ric.net (Richard Adams) wrote in message news:<c2352af0. 0401091510.9904 358@posting.goo gle.com>...[color=blue]
> Is it possible to execute more than one statement in SQL via MDAC
> ODBC? I have a fairly complex select I wanted to create a view with,
> but trying to send it all as one string with terminators ';' between
> doesn't work (invalid character.) How can I do this kind of thing
> from VB.NET?[/color]

You can only execute one PL/SQL block at a time in Oracle. You may be
able to combine a number of statements into a single anonymous PL/SQL
block, though-- i.e.

declare
<<list of variables>>
begin
SQL Statement 1;
SQL Statement 2;
SQL Statement 3;
end;

When you start wanting to do this sort of thing from a client
application, however, I would strongly recommend writing a stored
procedure that does whatever you want to do and calling that stored
procedure from the client.

Justin Cave
Distributed Database Consulting, Inc.

https://www.ddbcinc.com/askDDBC

[FaheemRao]

Re: Executing SQL via MDAC ODBC multiple statements?

What I understand from you question is that you want to send multiple
SQL statement at a time.

What my question is that why you want to send all sqls in same call ,
you can rather make multiple calls to database.

Please explain in more detailed that why you need to execute all sqls
in same vb.net API call.


Faheem



jcave@ddbcinc.c om (Justin Cave) wrote in message news:<c83193c7. 0401100016.2843 c98a@posting.go ogle.com>...[color=blue]
> ackthpt@concent ric.net (Richard Adams) wrote in message news:<c2352af0. 0401091510.9904 358@posting.goo gle.com>...[color=green]
> > Is it possible to execute more than one statement in SQL via MDAC
> > ODBC? I have a fairly complex select I wanted to create a view with,
> > but trying to send it all as one string with terminators ';' between
> > doesn't work (invalid character.) How can I do this kind of thing
> > from VB.NET?[/color]
>
> You can only execute one PL/SQL block at a time in Oracle. You may be
> able to combine a number of statements into a single anonymous PL/SQL
> block, though-- i.e.
>
> declare
> <<list of variables>>
> begin
> SQL Statement 1;
> SQL Statement 2;
> SQL Statement 3;
> end;
>
> When you start wanting to do this sort of thing from a client
> application, however, I would strongly recommend writing a stored
> procedure that does whatever you want to do and calling that stored
> procedure from the client.
>
> Justin Cave
> Distributed Database Consulting, Inc.
> www.ddbcinc.com/askDDBC[/color]