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

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

Oracle OLEDB provider

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

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

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



Rao's code should work also



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

Rick Lipkin

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

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

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: mrmulyadi32@gmail.com


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

Thanks.
Mulyadi from Indonesia..

Mulyadi

Try your insert code with this statement:


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

Rick Lipkin

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.

Glad it is working ..

Rick