how to update SQL table from FoxPro cursor 2

[campagnolo1]

Hello everyone,

I've been chasing my tail here for a while and could use some help. I have a FoxPro exe that runs a bunch of queries and puts them in a cursor. In the end the exe copies the final cursor into a dbf but I want it now to put the data into a SQL table. I created the SQL table with the exact columns and data types that the cursor has. But I'm having issues on getting the data into the SQL table. I have tried this:

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	
connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKATAT-507BB64\SQLExpress; Database=WebPortal;Trusted Connection=Yes")
execmd = "INSERT INTO itemprojimstock (Item) VALUES (?'test')"

	?SQLEXEC(connstr, execmd)
	SQLDISCONNECT(connstr)

CLOSE ALL

and this works but of course puts the value "test" in the column and not the actual cursor value. If I try something like this:

execmd = "INSERT INTO itemprojimstock (Item) FROM C6 SELECT item WHERE C6.item <> ('')"

I get a -1 and nothing gets entered in the database.
What syntax do I need to use to get all values from my cursor into the sql table?

Thanks a lot in advance for the help!

Cheers,

Chris

 

[Mike Lewis]

Chris,

You're making several mistakes here.

First, you need to remove the quotes in VALUES (?'test'). The point is that test is a variable. By adding quotes, you are saying that it is a literal value, which is not what you want.

You also need to store a value in test before you run the command. Whatever value you want to place in the table, you should copy that value to the variable named test.

Next point: You shouldn't be doing the SQLSTRINGCONNECT() every time. You only need to do that once, at the start of your processing, and then store the resulting handle (which you call connstr) where it can be used by subsequent calls to SQLEXEC().

Finally, your INSERT syntax is probably wrong. I say "probably" because the syntax varies from one back-end database to another, and you didn't tell us which database you are using. But in most cases, you would just say:

INSERT INTO <tablename> (<field list>) SELECT <select clauses>

Using FROM <variable> is VFP-specific, and probably not supported by your target database.

I suggest you start by sorting out those issues, and then come back if it still doesn't work.

Mike







__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Reading your post again, I think I've got a clearer idea of what you want to achieve.

Basically, your first INSERT syntax is close to what you want. The main problem is that you've got to lose the quotes, as I already mentioned, plus you need to pass all the values individually, using multiple parameters.

The following will give you an idea of how to go about it.

SELECT <fields> <JOIN and WHERE clauses> INTO CURSOR C5
SELECT C5
SCAN
  lcFindo = C5.Findo
  lcScrPad = C5.ScrPad
  lcRev = C5.Rev
  .......
  (similarly for other fields)

  lcExecCmd = "INSERT INTO itemprojimstock (Findo, ScrPad, Rev, ....) VALUES (?lcFindo, ?lcScrPad, ?lcRev, ....)
  SQLEXEC(connstr, lcExeCmd)
ENDSCAN

Obviously, this isn't meant to be working code, but it should give you the general idea. You will need to wrap the whole thing with your SQLSTRINCONNECT() and SQLDISCONNECT().

Depending on your back-end, there might be a more efficient way of doing it (such as a Bulk Insert in SQL Server), but you should at least get the basics of it right before we approach that.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

thanks so much for your help. I guess I understand the concept now a little better. I basically scan the last VFP cursor that was created, put each cursor field in a variable and then insert the variable into the SQl table.
Your sample code helped but also confused me a bit because you used C5 but the last cursor created is C6. So I came up with this code:

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	SELECT C6
	SCAN
		lcItem = C6.Item
		lcSono = C6.Sono
		lcSorev = C6.Sorev
	
connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKATAT-507BB64\SQLExpress; Database=WebPortal;Trusted Connection=Yes")
lcExeCmd= "INSERT INTO itemprojimstock (Item, Sono, Sorev) VALUES (?lcItem, ?lcSono, ?Sorev)"
	SQLEXEC(connstr, lcExeCmd)
	SQLDISCONNECT(connstr)

CLOSE ALL

Unfortunately this results in a "Nesting Error" with the "SELECT C6" line getting highlighted.
Why would that be? Am I misunderstanding the cursor C5/C6 part?

Thanks,

Chris

 

[Mike Lewis]

Chris,

The fact that I wrote C5 and you wrote C6 is unimportant. I simply mis-read your post.

The reason for your nesting error is that you don't have an ENDSCAN to match the SCAN.

Also, as I said before, you should put your SQLSTRINGCONNECT() and SQLDISCONNECT) outside the loop. That won't make any difference to the result, but it will run quite a bit faster.

Apart from those points, your code is looking much better.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

got it to work now! Thank you so much for your guidance! Even though it works nicely, it's a bit slow filling the SQL database.

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	SELECT C6
	SCAN
		lcItem = C6.Item
		lcSono = C6.Sono
		lcSorev = C6.Sorev
		
		lcExeCmd= "INSERT INTO itemprojimstock (Item, Sono, Sorev) VALUES (?lcItem, ?lcSono, ?Sorev)"	
	SQLEXEC(connstr, lcExeCmd)
	ENDSCAN
	SQLDISCONNECT(connstr)
CLOSE ALL

Now I will have to figure out which way I should proceed from here. This exe gets run every time a Crystal Report is launched since it creates the table that the report pulls its data from. I guess I have the option to either delete the data at the beginning and then fill the table at the end, or I could use the UPDATE function. But since there really isn't an index I'm not sure if UPDATE will work. You also mentioned a more efficient way (bulk insert)?

Thanks,

Chris

 

[Mike Lewis]

Chris, are you aware that Crystal Reports can access Foxpro tables directly? You don't have to use a SQL back-end as an intermediary. You will need to install the VFP OLE DB provider on the user's system, after which you will be able to connect to your VFP tables from the Database screen in CR just as you can with any other database.

If for any reason that doesn't work, here's a possible way of speeding up the code we have been discussing. It looks something like this:

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	SELECT C6
        [b]lcExecCmd = "INSERT INTO itemprojimstock (Item, Sono, Sorev) VALUES (?lcItem, ?lcSono, ?Sorev)"
        SQLPREPARE(connstr, lcExeCmd)[/b]
	SCAN
		lcItem = C6.Item
		lcSono = C6.Sono
		lcSorev = C6.Sorev
        	[b]SQLEXEC(connstr)[/b]
	ENDSCAN
SQLDISCONNECT(connstr)
CLOSE ALL

I've highlighted the code that is different in this version.

Essentially, with SQLPREPARE(), you send the command only once. The server will prepare it for exection. Then, for each record in your cursor, you store the parameter values in the variables as before, then call SQLEXEC() without specifying the command. The server knows what command to execute; it just needs to get the new values. This is generally faster than doing the way we have discussed - but how much faster depends on which back-end you are using.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

I'm about to get off work right now so I will look at your suggestions tomorrow. Thank you again so far for all your help and guidance!

Talk to you soon,

Chris

 

[Olaf Doschke]

Mikes last advice you can use DBF data in Crystal Reports without first putting it into SQL Server, is most important.

I still want to sketch another way to push data into SQL Server:

1. SQLEXEC(handle,"SELECT * FROM dbo.servertable WHERE 1=0","curAppend")
2. Make curAppend updatable via CURSORSETPROP settings, which are:
a) 'Buffering' ( 5 )
b) 'SendUpdates' (.T.)
c) 'Tables' ("dbo.servertable")
d) 'KeyFieldlist' ("id")
e) 'UpdatableFieldList' (all fields, eg "vfpcursorfield1,vfpcursorfield2,....")
f) 'UpdateNameList' (almost the same as UpdatableFieldList, just with corresponding sqlserver fieldnames: "vfpcursorfield1 sqlserverfield1,vfpcursorfield2 sqlserverfield2,....")
Read more about each setting in the CURSORSETPROP help topic.
3. SELECT curAppend
4. APPEND FROM DBF("C6")
5. TABLEUPDATE(2,.T.,"curAppend")

Step 2 can be generalized and put into a function to make some cursor created by sql passthrough updatable.

Bye, Olaf.

 

[campagnolo1]

Gentlemen,

thanks again for your advice. The reason I'm trying to put the data into SQL is that if I run the Crystal Report on the dbf file, the report keeps its "hooks" in the database file. So if somebody else tries to run the report and the subsequent exe, the file is locked and can't be updated. The only data connector I have found that does not lock the file is xBase, but that data connector does not work with our Crystal Reports 2008, causing a database connection error. Maybe you guys have more insight on that?
I will try to put your earlier suggestions into code sometime today and will let you know how it goes.

Cheers,

Chris

 

[Olaf Doschke]

Mike is the expert in regard to Crystal Repoerts here.

From what I recall you could use a COM interface up to CR XI,R2, which means this isn't available at CR2008. You should be able to use VFPOLEDB. I don't know which connection you set up and tried, Windows itself only comes with very outdated DBF drivers, that won't work for VFP DBFs.

You'll find VFPOLEDB here:

http://www.microsoft.com/en-us/download/details.aspx?id=14839

Bye, Olaf.

 

[Olaf Doschke]

By the way: Is your Crystal Reports 2008 a 64 bit application? It's not easy to tell, but an indicator is the installation path is either in Program Files or Program Files (x86). x86 means 32bit.

If it's 64bit you won't be able to use VFP ODBC drivers or OLEDB providers. VFP is 32 bit. Then your way through SQL Server is a solution.

Bye, Olaf.

 

[campagnolo1]

Olaf,

the operating system on our terminal server where the reports are run is inded 64bit. And the fact that most likely we will change our accounting and manufacturing software to a SQL backend version influenced my decision to try getting the FoxPro data into SQL tables.
I will try your earlier suggestion here in a little while and report back.

Mike,

changed the code to your suggestion and it appears to insert the data a little quicker than it did with the original code.

Chris

 

[Olaf Doschke]

Well, it's not the OS 64bit, that would stop VFPOLEDB 32bit from working, 32bit software runs on 64bit OS, it's jsut 32bit drivers are only visible to 32bit applilcations within the 64bit OS.

From what I googled Crystal Reports 2008 is also still 32bit software. so it could work, but also in prospect of the future storing data into SQL Server is a good choice.

I'd be interested to see if the tableupdate() approach is even faster then repeated sqlexec of a prepared statement. TABLEUPDATE(2,.T.,"curAppend") is inserting all new records in batches, which you can define with CURSORSETPROP() of the 'BatchUpdateCount' setting. The help file only talks about update performance, but that includes insert operations, too. The default is 1 only, so set this up, also raise the PacketSize via SQLSetProp(), the default only is 4KB. If the IP paket loss rate is low in your LAN, try larger packets.

Bye, Olaf.

 

[JRB-Bldr]

I'd be interested to see if the tableupdate() approach is even faster then repeated sqlexec of a prepared statement.

If you have a LOT of inserts to do, you might want to try using BULK INSERT (via SQL Pass-thru) into the SQL Server table.
That's what one of my clients uses to end up with the fastest import when they need to import a LOT of data.

One reference you might want to look over might be:

http://blog.sqlauthority.com/2008/0...rt-load-comma-delimited-file-into-sql-server/

Another one:

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Remember to test your code directly in the SQL Server Management Studio for correctness of syntax, etc. before putting it into your VFP code.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Chris,

You said:

... if I run the Crystal Report on the dbf file, the report keeps its "hooks" in the database file. So if somebody else tries to run the report and the subsequent exe, the file is locked and can't be updated.

That's not normal behaviour. There must be a setting in the OLE DB config that's causing that - probably you enabled "exclusive access", which there is no need to do in a reporting application.

The only data connector I have found that does not lock the file is xBase, but that data connector does not work with our Crystal Reports 2008, causing a database connection error.

It's not that the xBase connector doesn't work with CR 2008, but rather than it can't be used to access VFP files. The VFP file format changed in ver 6.0, and any older drivers no longer work. You must either use the OLE DB driver, or make sure your DBFs are saved in FOX2X format (which would be quicker than uploading the data to a back-end database).

It would be worth your persevering with this. Remember, Crystal can work with all kinds of data sources - even plain text files - so you are not tied to SQL or VFP DBFs.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

you mentioned

There must be a setting in the OLE DB config that's causing that - probably you enabled "exclusive access", which there is no need to do in a reporting application.

I have read about this but I can't find where or how to change the properties of the OLE DB driver. Where would that be?

Olaf,

working on your suggestions right now.

Chris

 

[Olaf Doschke]

JRB-Bldr, been there, done that.

I'm not really interested in the best overall speed, but the comparison of prepared statements vs tableupdate in larger batches and larger LAN packets.

What typically is not counted for bulk insert speed is the time you need to prepare text files for bulk insert. With a LOT of data this also takes quite some time and that file also has to go through the LAN, as far as I know BULK insert needs a file locally available to the SQL Server.

We did a data migration of many GB that way, and it worked good. Still due to some conversion issues you don't have with todays available SQL Server field types, but also because of a changed data schema a previous step we took was to put data from one DBC into another staging DBC and then into txt files for bulk insert via bcp tool (not via T-SQL BULK INSERT).

Of course creating a cursor also takes it's time, in campanolos case there is no need for the creation of the C6 cursor, instead you could also do

4. Insert Into curAppend;
Select C5.*, bmsl.findno, bmsl.scrpad;
FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev

Instead of

4. APPEND FROM DBF("C6")

Which makes it one cursor less. And if campanolo is staging data through cursors C1 To C6 I bet there are possible optimizations to short cut that, too.

I would strictly limit bulk inserts into empty tables as a one time data migration, also because only then it makes sense to skip CHECK_CONSTRAINTS, which is a speed enhancement, of course.

But after you put all data into SQL Server either you should only work on that data from then on, or you introduce the need to sync DBC/DBFs with SQL Server DB. If you only want to go for a subset of data for reports, I'd not go for bulk insert. It soounds easy COPY TO some.csv TYPE CSV, then BULK INSERT, but what about Memo? Dates in a format SQL Server Bulk insert understands? There are many such detail problems to solve, which makes TABLEUPDATE much easier.

Bye, Olaf.

 

[campagnolo1]

Olaf,

thanks for the clarification on the BULK INSERT. I indeed go through multiple cursors because of different joins. If you want I can post the full code for you and maybe (most certainly ) it could be improved.
I tried your tableupdate() function but I'm getting a "Too many arguments" error. Not sure why, all arguments are valid according to the Help. I've tried different syntax but still get the error.

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	**SELECT C6
	**lcExeCmd= "INSERT INTO itemprojimstock (Item, Sono, Sorev) VALUES (?lcItem, ?lcSono, ?Sorev)"
	**SQLPREPARE(connstr, lcExeCmd)
	**SCAN
		**lcItem = C6.Item
		**lcSono = C6.Sono
		**lcSorev = C6.Sorev	
	**SQLEXEC(connstr)
	**ENDSCAN
	**SQLDISCONNECT(connstr)
	**CLOSE ALL
	SET MULTILOCKS ON
	SQLEXEC(connstr,"SELECT * FROM itemprojimstock WHERE 1=0","curAppend")
	CURSORSETPROP(BUFFERING(5),SENDUPDATES(.T.),TABLES("itemprojimstock"),KeyFieldList("id"),UpdatableFieldList("C6.item, C6.Sono"),UpdateNameList("itemprojimstock.item, itemprojimstock.sono"))			
	SELECT curAppend
	APPEND FROM DBF("C6")
	TABLEUPDATE(2,.T.,"curAppend")

Any thoughts?

Chris

 

[Olaf Doschke]

Look up Cursorsetprop, this will error, as you have it.

Also, you misunderstood several details. You don't make C6 updaable, you make curAppend updatable. So you don't specify C6.item, C6.Sono in the updatabalefield list. You actually don't specify cursor or table names in the fieldlists, because Tables is it's own setting and the cursor to update from is specified in the TABLEUPDATE.

Try it this way:

SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('Sendupdates',.T.,'curAppend')
CURSORSETPROP('Tables','itemprojimstock','curAppend')
CURSORSETPROP('KeyFieldList','ID','curAppend') && important: is ID really the name of a primary key field of the itemprojimstock table? Is such a field missing?
CURSORSETPROP('UpdatableFieldList','ID, Item, Sono','curAppend') && what about Sorev? Further fields? What about the ID field? I said you have to specify the key field here, too
CURSORSETPROP('UpdateNameList','ID ID,Item Item,Sono Sono','curAppend') && because cursor field names and sql table field names are the same, this looks odd, but needs to be this way, I said so.

Bye, Olaf.