Unable to get CursorSetProp & TableUpdate to work. 1

[Goofus828]

Hi all, i've based the following code on this previous thread:
thread184-1716941

PUBLIC gnConnect
gnConnect = SQLCONNECT("AccountOperations")

SET MULTILOCKS ON

**setup empty cursor
?SQLEXEC( gnConnect, "select * from JML_dbCamsAssets where 1=0", "csr_dbCamsAssets" )

** get the data for importing
SELECT RECID  AS FAS_RECID_NV,;
	CLTNO     AS CLIENTNO_NV,;
	COUPRATE  AS COUPRATE_MN,;
	MKTCODE   AS MKTCODE,;
	MKTVALUE  AS MKTVALUE_MN,;
	DMATURE   AS DMATURE_DT,;
	DSELL     AS DSELL_DT,;
	DBUY      AS DBUY_DT,;
	PORTFOLIO AS PORTFOLIO_NV,;
	CATEGORY  AS CATEGORY_NV,;
	MAJTYPE   AS MAJTPE_NV,;
	TYPE      AS TYPE_NV,;
	NOUNITS   AS NOUNITS_NM,;
	ACCOUNT   AS ACCOUNT_NV,;
	OWNCAT    AS OWNCAT_NV,;
	OWNCODE   AS OWNCODE_NV,;
	OWNNAME   AS OWNNAME_NV,;
	NAME      AS NAME_NV,;
	GROUP     AS GROUPBY_NV,;
	CLASS     AS CLASS_NV,;
	dlastChg  AS DLASTCHG_DT,;
	BROKDLR   AS BROKERDEALER_NV;
	FROM C:\DBVDATA2\FAS;
	ORDER BY CLTNO;
	INTO CURSOR CSR_FAS

xFields = "FAS_RECID_NV,CLIENTNO_NV,"
xFields = xFields + "COUPRATE_MN,MKTCODE_NV,MKTVALUE_MN,DMATURE_DT,DSELL_DT,"
xFields = xFields + "DBUY_DT,PORTFOLIO_NV,CATEGORY_NV,MAJTYPE_NV,TYPE_NV,"
xFields = xFields + "NOUNITS_NM,ACCOUNT_NV,OWNCAT_NV,OWNCODE_NV,OWNNAME_NV,"
xFields = xFields + "NAME_NV,GROUPBY_NV,CLASS_NV,DLASTCHG_DT,BROKERDEALER_NV"


?CURSORSETPROP("Buffering", 5,"csr_dbcamsassets")

?CURSORSETPROP("sendupdates", .T.,"csr_dbcamsassets")

?CURSORSETPROP("tables", "JML_dbCamsAssets","csr_dbcamsassets")

?CURSORSETPROP("KeyFieldList", "fas_recid_NV", "csr_dbcamsassets")

?CURSORSETPROP("UpdatableFieldList", xFields, "csr_dbcamsassets" )

?CURSORSETPROP("UpdateNameList",     xFields, "csr_dbcamsassets" )

SELECT csr_dbCamsAssets
APPEND FROM DBF( "CSR_FAS" )

? "TableUpdate "
xjunk = TABLEUPDATE(2,.T.,"csr_dbcamsassets", AErrorArray )

?? xjunk

All of the CursorSetProps return True.
The TableUpdate returns False

AErrorArray has all negative numbers in.

Sometimes I get error 1491 "no update tables are specified. Use the Tables Property of the cursor"

What am I missing?
Is there something in the Tool->Options->Remote Data that I have to change?

TIA!

 

[JRB-Bldr]

You most certainly create a Remote View into a SQL Server data table and then do updates against it.
But I have found that even when the Remote View Cursor is built properly, it can be a sometimes 'ify' thing - especially if there are a LOT of records involved.

Instead I'd recommend doing a SQL-Pass Through approach.
If you search this forum for: sql pass-thru and/or sql pass-through you will find a number of previous postings on the subject.

If, for some reason, you REQUIRE a Remote View approach, come back and let us know.

Good Luck,
JRB-Bldr

 

[Goofus828]

I've done the scan/endscan with the sqlexec( insert ) before, but now I have 26,000,000+ records to insert so I was hoping this method would be faster.

 

[Dan Freeman]

Um, no. With that volume, you should actually be looking into Bulk Copy.

You *might* get away with it if you SCAN/ENDSCAN and tableupdate() each individual row, but still you should be looking into bulk copy (or bulk insert). See

http://technet.microsoft.com/en-us/library/aa196743(v=sql.80).aspx

and related links.

 

[Olaf Doschke]

You can do mass inserts with APPEND and TABLEUPDATE, too, but you move data twice, once from the source into an updatable cursor and then into the server. If you do that at the server it means you load data into memory and then into the server, which a bulk copy does too, in principle. Nevertheless if the data already is in the form you need and you have no data transformations to do in VFP, a bulk insert is better, but if you have to create CSV files first, that's additional steps and so TABLEUPDATE can still be a good candidate. I often see people just measuring the bulk insert time, but the "door to door" time is essential, nothing else!

Steps to make the insert faster without bulk copy is turning the database into the simple recovery model, work in chunks of a few thousand records and of course use transactions for that.

You CURSORSETPROPS miss to prepare the Updatenamelist with pairs of field names:

Each pair of names consists of a local field name followed by the full remote field name. The full remote field name appears as <remote table name>.<remote field name> where <remote table name> matches the name from the Tables property.[/help]

So that would need to be "JML_dbCamsAssets.FAS_RECID_NV FAS_RECID_NV,JML_dbCamsAssets.CLIENTNO_NV CLIENTNO_NV,..."
Looks a bit redundant but that's the way it's needed. You can create that list easier by looping all with FIELD(N) names.

Bye, Olaf.

 

[Mike Lewis]

Have you really got 26 million records to insert?

If so, any solution involving VFP code in a loop is going to take a long time - not to mention the possibility of breaking the 2 GB limit in an intermediate file or cursor. Much better to use the import tools available in your back-end database. If the database is Microsoft SQL Server, look at Integration Services or (as Dan suggests) bulk copy. Other databases have similar tools.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>...So that would need to be "JML_dbCamsAssets.FAS_RECID_NV FAS_RECID_NV,JML_dbCamsAssets.CLIENTNO_NV CLIENTNO_NV,..."
Inverse of course. First local then remote name for each pair.

Bye, Olaf.

 

[Goofus828]

Olaf, I made that change and it worked perfectly. Thank you for clarifying the documentation for me.

Mike, it is actually 22 million records. It is a transaction table that started back in 1997. And yes it is very close to the 2 gig limit.

Thank you for all your help!

Josh