ADO with Oracle

[Mulyadi]

Hi All...
can someone help me....?
I try to migration database file from a DBF to Oracle database file.
but i cannot execute query : Insert and Update.

my code like this:

#include "fivewin.ch"

static oRs, oErr

func main

local cSQL, cConnStr
local i, txtKode := "01x", txtKet := "test it"

cSQL := "SELECT * FROM MyTable"
cConnStr := 'Provider=MSDAORA.1;Data Source=dblocal;User ID=mulyadi;Password=1234'

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic

TRY
oRs:Open( cSQL, cConnStr )
CATCH oErr
MsgInfo( "Error in Opening Oracle table" )
RETURN(.F.)
END TRY

oRs:MoveFirst()

// add record --> is Oke
for i := 1 to 5
oRs:AddNew()
oRs:Fields(0):Value := "01" + alltrim(str(10 + i))
oRs:Fields(1):Value := "test " + oRs:Fields(0):Value
oRs:Update()
next i

// now i try to use a Query
cSQL = "INSERT INTO MyTable (KODE,KET) VALUE( '" + txtKode + "','" + txtKet + "')"

//
FW_OpenRecordSet( cConnStr, cSQL ) /* result ---> no action */

// or
HB_AdoSetQuery( cSQL ) /* result ---> no action */

oRs:Close()

return nil

Hi friend... what wrong with my Code....?

[nageswaragunupudi]

Code: Select all  Expand view

oCn := FW_OpenAdoConnection( cConnStr )
cSql := "INSERT INTO MyTable (KODE,KET) VALUE( '" + txtKode + "','" + txtKet + "')"
oCn:Execute( cSql )
// other code, example
oRs := FW_OpenRecordSet( oCn, "SELECT * FROM MyTable" )
xbrowse( oRs )
oRs:Close()

oCn:Close()
 

[Rick Lipkin]

Just curious .. I was never a fan or used sucessfully the oledb provider MSDAORA.1 With Oracle, I found the best success using the Oracle OLEDB provider directly from Oracle and since I had to use a 3rd party Oledb client ( not native to ms Windows ) I just went to the source.

http://www.oracle.com/technetwork/datab ... 88126.html

Don't forget you will have to make sure you have your .ora files configured correctly for your connection.

Note Rao's correct advice using a oCn 'connection' rather than a recordset ..

My 2 cents
Rick Lipkin

[nageswaragunupudi]

Oracle OLEDB provider

Microsofts Oracle OLEDB provider works better than even the one provided by Oracle.

[Mulyadi]

Halo...

I try sample code from mr.nageswaragunupudi like :

oCn:Execute( cSql )

result : Error description: Error BASE/1004 Class: 'NIL' has no exported method: EXECUTE

Why...?
.

for Mr.Rick Lipkin,
many thanks, i get and try the code from this forum,
and I has install Oracle 10g R2, ODAC ver 10.2.0.2.0
when i develop using Visual Studio 2010 is oke, no problem
with the oracle database and other setting file.

statemen in connection string 'MSDAORA.1' i get from this forum
but to use original driver from oracle, i dont know the file name.

Help me please.....

[Rick Lipkin]

Try the connection either way with MSDAORA.1 .. it should work..

Code: Select all  Expand view

cConnStr := 'Provider=MSDAORA.1;Data Source=dblocal;User ID=mulyadi;Password=1234'

oCn := CREATEOBJECT( "ADODB.Connection" )
TRY
    oCn:Open( cConnStr )
CATCH oERR
   SAYING := "Error in Opening connection to Oracle"
   MsgInfo( SAYING )
   RETURN(.F.)
END TRY

cSql := "INSERT INTO MyTable (KODE,KET) VALUE( '" + txtKode + "','" + txtKet + "')"
 
Try
   oCn:Execute(cSql)
Catch
   oCn:Close()  
   MsgInfo( "Error in Insert" )
   RETURN(.F.)
End Try

oCn:Close()
 

Rao's code should work also

Code: Select all  Expand view

cConnStr := 'Provider=MSDAORA.1;Data Source=dblocal;User ID=mulyadi;Password=1234'
oCn := FW_OpenAdoConnection( cConnStr )
cSql := "INSERT INTO MyTable (KODE,KET) VALUE( '" + txtKode + "','" + txtKet + "')"

Try
    oCn:Execute( cSql )
Catch oErr
    MsgInfo( "Error in Insert")
    oCn:Close()
    Return(.f.)
End Try

oCn:CLose()
 

Both should work .. If the code breaks at the oCn:Execute() statement, you probably have a syntax error in you Sql Statement.

Rick Lipkin

[Mulyadi]

To: Mr Rick Lipkin,
Sorry with my English...

My complete file like this:

table: MYTABLE in Mulyadi schema
--> now i try connect with 'SYSTEM' user previlage 'SYSDBA'

tnsnames.ora
------------

DBLOCAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dblocal)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


second opinion string connection :
i change connection string with original driver from oracle like this:
----------------------------------------------------------------------
cStr := 'Provider=OraOLEDB.Oracle;Data Source=dblocal;User ID=system;Password=itboss'


and my completed code like the samples on the top isue like this :



#include "fivewin.ch"

func main

local oCn, cSql, cStr, SAYING, txtKode := "01x", txtKet := "test..."
local oErr

// cStr := 'Provider=OraOLEDB.Oracle;Data Source=dblocal;User ID=system;Password=itboss'

cStr := 'Provider=MSDAORA.1;Data Source=dblocal;User ID=system;Password=itboss'

oCn := CREATEOBJECT( "ADODB.Connection" )
TRY
oCn:Open( cStr )
CATCH oERR
SAYING := "Error in Opening connection to Oracle"
MsgInfo( SAYING )
RETURN(.F.)
END TRY

cSql := "INSERT INTO mulyadi.MyTable (KODE,KET) VALUE( '" + txtKode + "','" + txtKet + "')"

Try
oCn:Execute(cSql)
Catch
oCn:Close()
MsgInfo( "Error in Insert" )
RETURN(.F.)
End Try

oCn:Close()

return nil

--------------------------
Result: Error in Insert


What the problem with this code.....?

[Rick Lipkin]

Since you made it through the initial connection without it breaking .. I think your .ora files are correct.

Try this Sql Statement ( added s to the word Value ) and added a space after VALUES

cSql := "INSERT INTO mulyadi.MyTable (KODE,KET) VALUES ( '" + txtKode + "','" + txtKet + "')"

Sample Insert statement
http://www.w3schools.com/sql/sql_insert.asp

I would also check the lengths of your fields in your table to make sure the variables you are inserting do not exceed those field lengths. If you are trying to insert a value greater than the field length your insert statement will fail.

Rick Lipkin

[Mulyadi]

Oke mr, my struc table is so simple, : )

KODE VARCHAR2 (20)
KET VARCHAR2 (50)

thanks for answer my question, to next time i try again..
if found any idea for solution or other trick, can your give me a simple code to the case,
and send to my email at: [url]mrmulyadi32@gmail.com[/url]


for UPDATE and DELETE statemen is okey no problem, but for INSERT is not success.

Thanks.
Mulyadi from Indonesia..

[Rick Lipkin]

Mulyadi

Try your insert code with this statement:

Code: Select all  Expand view

cSql := "INSERT INTO mulyadi.MyTable (KODE,KET) VALUES ( '" + txtKode + "','" + txtKet + "')"
 

I made some syntactical changes per my last post... Let me know how it works.

Rick Lipkin

[Mulyadi]

Mr Rick Lipkin.

its works fine...
to now, I understand with the case statement at:

Original me : ...VALUES ('" // result --> No action
your solution : ...VALUES ( '" // result --> working fine

Thanks for your attention..
see you againt in other case...


Mulyadi.

[Rick Lipkin]

Glad it is working ..

Rick