Sign in to follow this  
Followers 0
stockboy

Oracle question

5 posts in this topic

#1 ·  Posted (edited)

I connect to the Oracle database as follows, works fine:

Dim $oMyError

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Initializes COM handler

$SID = $var1[1][1]

$ado = ObjCreate( "ADODB.Connection" ) ; Create a COM ADODB Object with the Beta version

With $ado

.ConnectionString =("Provider='OraOLEDB.Oracle';Data Source=" & $SID & ";User Id='xxxxx';Password='xxxxx';")

.Open

EndWith

$adors = ObjCreate( "ADODB.RecordSet" ) ; Create a Record Set to handles SQL Records

With $adors

.ActiveConnection = $ado

.Source = "select waarde from parnassus.prn_systeem where code = 'DATABASEVERSIE'"

.Open

EndWith

While not $adors.EOF

For $i = 0 To $adors.Fields.Count - 1

FileWriteLogOracle($pad6 &"parnassus.log",$adors.Fields( $i ).Value & @TAB)

Next

$adors.MoveNext ; Go to the next record

WEnd

$ado.Close

My question: instead of this function (select waarde from parnassus.prn_systeem where code = 'DATABASEVERSIE')

I want to run an external sql-file (parnassus.sql) and log the result...

Anyone can help me?

TNX a lot

The external sql-file (parnassus.sql) contains for example:

DROP TRIGGER PRN_TRG_TEKSTEN_COMMENTAAR;

DROP SEQUENCE PRN_SEQ_TEKSTEN_COMMENTAAR;

DROP TABLE PRN_TEKSTEN_COMMENTAAR;

ALTER TABLE PRN_TEKST_COMMENTAAR ADD (

CONSTRAINT PRN_TEKST_C_PRN_LID_FK FOREIGN KEY (LID_NRQ)

REFERENCES PRN_LEDEN (NRQ));

ALTER TABLE PRN_TEKST_COMMENTAAR ADD (

CONSTRAINT PRN_TEKST_C_PRN_BIJLAGE_FK FOREIGN KEY (TEKST_NRQ)

REFERENCES PRN_TEKST (NRQ));

Edited by stockboy

Share this post


Link to post
Share on other sites



@stockboy

In AutoIt you have to user the RUN command see help file.

But before you make .BAT file the calls the SQLPLUS cmd.

In order to test if you have sqlplus installed, go to the CMD and type SQLPLUS, and see what happenes.

If the SQLPLUS is ok.

Go here to see how to run a SQLcommand from the SQLPLUS.

http://www.techonthenet.com/oracle/questions/script.php

Enjoy,

ptrex

Share this post


Link to post
Share on other sites

@ptrex

I understand what you mean but I want to execute the sql-script with oledb (so without starting sqlplus).

But I'm not sure if its possible...

Share this post


Link to post
Share on other sites

Hi

It is not possible to run as script through the OLEDB.

The objectove of OLEDB is run SQL commands against a DB.

So you run each command in a OLEDB statement, so you don't need the script.

An way around you problem is that you write these command in a kind of TXT file.

Than user AUTOIT to read that file use FILEOPEN and FILECLOSE. And assign each line to a Variable in your AU3 SCript.

This way each command in the text file is read by the script before it is executed.

Than make your script this way thet it executes the SQL lines (in the Variable) by the OLEDB commands.

This way it should definitely work.

Regards/Groeten,

ptrex

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

That's all I needed to know.

I'm going to try it the way you described above.

I'm also pretty sure it should work.

You rule :-)

TNX A LOT !!

Edited by stockboy

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0