Oracle question

[stockboy]

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 December 21, 2006 by stockboy

[ptrex]

@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

[stockboy]

@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...

[ptrex]

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

[stockboy]

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 December 21, 2006 by stockboy