>Hi all,
>
>I have inherited a process that is responsible for updating an Oracle database with values that are store in a VFP database.
>
>The developer of this process has set this up as follows:
>
>- prepare separate tables for Changes, Adds and Deletions
>- process each of these via SQLConnect, SQLPrepare and SQLExec statements
>
>I am having great difficulty in getting the Changes process to run due to C000005 errors.
>
>The Oracle table consists of 53 fields and the VFP tables mirror them. The program scans the VFP tables, stores the values from each of its fields into memvars and then attempts SQLExec.
>
>The context of the SQL is stored in a variable as such...
>
>cSqlChanges=[update ora.providers set Field1=?cField1, Field2=?cField2,
> Field3=?nField3,...,Field53=?cField53 where id=cId]
>
>From within a scan, these memvars are loaded with data from the VFP table.
>
>Then, the SQLConnect (good return), the SQLPrepare (good return) and SQLExec (good return) are issued.
>
>
>nConn = sqlconnect(DSN,Userid,Password)
>if nConn = -1
> do error_rtn
>endif
>
>if SQLPrepare(nConn, cSqlChanges) = -1
> do error_rtn
>endif
>
>if sqlexec(nConn, cSQLChanges) = -1
> do error_rtn
>endif
>
>
>
>I understand that the SqlExec only really needs the connection handle, but this is the way it was handed to me.
>
>The C5 error occurs during the Update (SQLExec) , sometimes right at the onset, sometimes 2% into the scan, sometimes further.
>
>I haven't had problems with the Add or Delete phases, as yet. My thought is that there is just too much (i.e. too many fields) data for VFP to handle, possibly due to available memory. The tables can contain close to 200,000 rows. Each having 53 fields to be updated.
>
>I have no prior experience with VFP to Oracle, but it seems to me that there has got to be a better technique to achieving the end result, that is, updating Oracle with Fox data, then the one currently in place. My gut reaction is that this really belongs on the Oracle box for processing, not on my PC.
>
>Any guidance towards improving this process is greatly appreciated.
Vincent,
It'd take a long time. Probably C..5 is due to a connection error. Instead you could use a table buffered updatable SPT cursor and do a single tableupdate().
Cetin