ADO - Oracle

Mensagem por **asimoes** » 06 Jun 2014 18:34

Exemplo de INSERT em uma tabela com retorno do RowId

#include "adordd.ch"
#include "hbcompat.ch"

FUNCTION Main()
Local oConnection := Win_OleCreateObject( "ADODB.connection" )
Local oRecordSet  := Win_OleCreateObject( "ADODB.recordset"  )
Local cConexao
   cConexao:="Provider=OraOLEDB.Oracle;Data Source=ds01;User ID=aluno;Password=aluno;FetchSize=200;CacheType=Memory;PLSQLRSet=True;TNS=DS01;OWNER=aluno;"

   oConnection:Open(cConexao)
           
   oConnection:CursorLocation := adUseClient

   oCommand:=nil
   oCommand:=CriaObjetoCommand(@oCommand) 
   oCommand:ActiveConnection:=oConnection

   cQuery := " INSERT INTO EMP(EMPNO) VALUES(8000)   "
   cQuery += " RETURNING rowid INTO ?"

   oCommand:CommandText := cQuery
   oCommand:CommandType := adCmdText

   oPrm0:=oCommand:CreateParameter('oPrm0',adVarChar, adParamOutput,20,"CROWID")
   oCommand:Parameters:Append( oPrm0 )

   TRY 
      oConnection:BeginTrans()
      cRecordSet:=oCommand:Execute()
      cRowId:=oCommand:Parameters(0):Value
      oConnection:CommitTrans()
   CATCH
      oConnection:RollbackTrans()
   END
   oCommand:activeConnection:close()
   QUIT

RETURN Nil

FUNCTION CriaObjetoCommand(oObjeto)
LOCAL oObjetoCommand:=Win_OleCreateObject( "ADODB.Command" )
   IF oObjeto!=Nil
      oObjeto:=Nil
   ENDIF
   oObjeto:=oObjetoCommand
RETURN oObjetoCommand