edit oracle database

[stockboy]

This script connects to an oracle database, gets the version of the program 'CI' and writes it in version.log.

My question: what do I have to change, in the script, to PUT data in the database instead of GET data.

For example: "update mnbarm set MNBARMART60MAXSOCECONEUR = 21356.63 where mnbarmmaand >= '01-JAN-07'"

instaed of "select upgrade_version ||''||upgrade_build from ci_toepassing where kode like 'CI'"

I know... I don't know much 'bout databases, shame on me ;o)

------------------------------------------------------------------------------

Dim $oMyError

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$ado = ObjCreate( "ADODB.Connection" )

With $ado

.ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='mig2';User Id='cipal';Password='xxxx';")

.Open

EndWith

$adors = ObjCreate( "ADODB.RecordSet" )

With $adors

.ActiveConnection = $ado

.Source = "select upgrade_version ||''||upgrade_build from ci_toepassing where kode like 'CI'"

.Open

EndWith

While not $adors.EOF

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

FileWriteLogOracle("c:\version.log",$adors.Fields( $i ).Value & @TAB)

Next

$adors.MoveNext

WEnd

Func MyErrFunc()

Endfunc

Func FileWriteLogOracle($sLogPath, $sLogMsg)

EndFunc

[ptrex]

@stockboy

In order to add the data to a database you need to use the INSERT command instead of the UPDATE.

See here for more info : INSERT

Regards

ptrex

[stockboy]

@ptrex

I mean: 'edit' the database (insert, update, etc...)

If I use: "select upgrade_version ||''||upgrade_build from ci_toepassing where kode like 'CI'" the script works.

If I use: "update mnbarm set MNBARMART60MAXSOCECONEUR = 21356.63 where mnbarmmaand >= '01-JAN-07'"

it gives this error: "operation is not allowed when the object is closed"

[ptrex]

@

In order to update or change record via ADOR you need to use the EXECUTE command.

see more info here EXECUTE

Groeten,

ptrex

[stockboy]

CODE

$ado = ObjCreate( "ADODB.Connection" )

With $ado

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

.Open

EndWith

$adors = ObjCreate( "ADODB.RecordSet" )

With $adors

.ActiveConnection = $ado

.Execute ("UPDATE PRN_SYSTEEM SET WAARDE = '060204' WHERE CODE = 'PROGRAMVERSIE+'")

.Open

EndWith

While not $adors.EOF

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

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

Next

$adors.MoveNext

WEnd

@Ptrex (or other experts ;o)

Something like this?

What am I doing wrong?

It still gives an error

TNX

Stockboy

[ptrex]

@stockboy

Run this again, added some error checking routine

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

$ado = ObjCreate( "ADODB.Connection" )             With $ado       .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source=" & $SID & ";User Id='xxxx';Password='xxxx';")       .OpenEndWith$adors = ObjCreate( "ADODB.RecordSet" )            With $adors        .ActiveConnection = $ado        .Execute ("UPDATE PRN_SYSTEEM SET WAARDE = '060204' WHERE CODE = 'PROGRAMVERSIE+'")        .OpenEndWithWhile not $adors.EOF    For $i = 0 To $adors.Fields.Count - 1      FileWriteLogOracle($pad6 &"Parn_060204.log",$adors.Fields( $i ).Value & @TAB)    Next    $adors.MoveNext                                               WEnd

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

Let me know which errors come out into SciTe and COM error routine.

regards,

ptrex

[stockboy]

@ptrex

It gives the following error's:

1)

2)

3)

What's wrong ?

Tnx

Stockboy

[ptrex]

@stockboy

look here for Ora-14007 error code OraErrors

There seems to be something wrong with the command syntax you used.

regards

ptrex