Run Oracle Script from VB ODBC

Karey · 02-20-2002, 12:14 PM

Is it possible to run an Oracle script from VB 6? I am working through an
ODBC and need to run a scripts to create a number of tables for a user.
The syntax I am using is :

connection.Execute "@c:\scriptname"

I receive and "Invalid SQL Command" error when it is run, but the command
works when pasted in SQLPlus....

All suggestions appreciated!

Karey · 02-20-2002, 01:37 PM

Yeah, I thought of doing that but the string is over 250 lines long, so I
was hoping to avoid that

! Thanks a lot!

Brian Camire · 02-20-2002, 01:51 PM

Try reading the entire contents of your script file into a string and then
passing the string to the Execute method of the Connection object.

"Karey" <kkyle@catch4.com> wrote in message news:3c73cb7b$1@10.1.10.29...
>
> Is it possible to run an Oracle script from VB 6? I am working through an
> ODBC and need to run a scripts to create a number of tables for a user.
> The syntax I am using is :
>
> connection.Execute "@c:\scriptname"
>
> I receive and "Invalid SQL Command" error when it is run, but the command
> works when pasted in SQLPlus....
>
> All suggestions appreciated!

hylton · 02-20-2002, 10:55 PM

There are API calls to shell to a windows command line and wait for whatever
command is executed to finish...a standard shell command won't wait, but
the API call will...basically, you just shell to SQLPlus w/ the file name
as a command line parameter...if you don't need your process to wait for
it to finish, you can use a regular shell VB command/statement.

The downside of this is that you can't determine success or failure, unless
you are writing that to a log file or table in your script...which is how
we handle it...

I don't have an example here at home of the API w/ wait functionality...I'll
try to remember and post what I use at work tomorrow...doing the exact same
thing from an app there...

Chris

"Karey" <kkyle@catch4.com> wrote:
>
>Is it possible to run an Oracle script from VB 6? I am working through
an
>ODBC and need to run a scripts to create a number of tables for a user.

>The syntax I am using is :
>
>connection.Execute "@c:\scriptname"
>
>I receive and "Invalid SQL Command" error when it is run, but the command
>works when pasted in SQLPlus....
>
>All suggestions appreciated!

Ilamurugu.S · 03-02-2002, 01:43 AM

Hai,
Have u got the solution for the Oracle trigger Execution thru Vb Coding
. Can u Pleae Send me the Details.

With Regards,
Ilamurgu.S

"Karey" <kkyle@catch4.com> wrote:
>
>Yeah, I thought of doing that but the string is over 250 lines long, so
I
>was hoping to avoid that

! Thanks a lot!

Ilamurugu.S · 03-02-2002, 01:47 AM

Hai,
Can u Please send me the API function for Trigger Execution thru VB.
If it is possible please send me with examples.

With Regards,
Ilamurugu.S

>
>There are API calls to shell to a windows command line and wait for whatever
>command is executed to finish...a standard shell command won't wait, but
>the API call will...basically, you just shell to SQLPlus w/ the file name
>as a command line parameter...if you don't need your process to wait for
>it to finish, you can use a regular shell VB command/statement.
>
>The downside of this is that you can't determine success or failure, unless
>you are writing that to a log file or table in your script...which is how
>we handle it...
>
>I don't have an example here at home of the API w/ wait functionality...I'll
>try to remember and post what I use at work tomorrow...doing the exact same
>thing from an app there...
>
>Chris
>
>"Karey" <kkyle@catch4.com> wrote:
>>
>>Is it possible to run an Oracle script from VB 6? I am working through
>an
>>ODBC and need to run a scripts to create a number of tables for a user.
>
>>The syntax I am using is :
>>
>>connection.Execute "@c:\scriptname"
>>
>>I receive and "Invalid SQL Command" error when it is run, but the command
>>works when pasted in SQLPlus....
>>
>>All suggestions appreciated!
>

hylton · 03-03-2002, 08:12 PM

The code being used to execute a BATCH file, Windows command line statement,
etc from VB and wait on it to return is simply an API call to the WaitForSingleObject
API...see the MS knowledgebase article at...

http://support.microsoft.com/support.../Q129/7/96.ASP

Basically, all I do is use this API to execute a sqlplus commandline statement...or
a batch file w/ multiple sqlplus commandline statements...whatever...doesn't
matter...it shells to the commandline, runs it, waits, and comes back when
it's done...VB waits for it...

Should work perfectly for you...

Chris

"Ilamurugu.S" <murugu@sierratec.com> wrote:
>
>Hai,
> Can u Please send me the API function for Trigger Execution thru VB.
>If it is possible please send me with examples.
>
>With Regards,
>Ilamurugu.S
>
>
>
>>
>>There are API calls to shell to a windows command line and wait for whatever
>>command is executed to finish...a standard shell command won't wait, but
>>the API call will...basically, you just shell to SQLPlus w/ the file name
>>as a command line parameter...if you don't need your process to wait for
>>it to finish, you can use a regular shell VB command/statement.
>>
>>The downside of this is that you can't determine success or failure, unless
>>you are writing that to a log file or table in your script...which is how
>>we handle it...
>>
>>I don't have an example here at home of the API w/ wait functionality...I'll
>>try to remember and post what I use at work tomorrow...doing the exact
same
>>thing from an app there...
>>
>>Chris
>>
>>"Karey" <kkyle@catch4.com> wrote:
>>>
>>>Is it possible to run an Oracle script from VB 6? I am working through
>>an
>>>ODBC and need to run a scripts to create a number of tables for a user.
>>
>>>The syntax I am using is :
>>>
>>>connection.Execute "@c:\scriptname"
>>>
>>>I receive and "Invalid SQL Command" error when it is run, but the command
>>>works when pasted in SQLPlus....
>>>
>>>All suggestions appreciated!
>>
>