×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how to update SQL table from FoxPro cursor
2

how to update SQL table from FoxPro cursor

how to update SQL table from FoxPro cursor

(OP)
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:

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

CODE -->

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

RE: how to update SQL table from FoxPro cursor

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:

CODE -->

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

RE: how to update SQL table from FoxPro cursor

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.

CODE -->

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

RE: how to update SQL table from FoxPro cursor

(OP)
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:

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

RE: how to update SQL table from FoxPro cursor

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

RE: how to update SQL table from FoxPro cursor

(OP)
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.

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

RE: how to update SQL table from FoxPro cursor

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:

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
        lcExecCmd = "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 


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

RE: how to update SQL table from FoxPro cursor

(OP)
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

RE: how to update SQL table from FoxPro cursor

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.

RE: how to update SQL table from FoxPro cursor

(OP)
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

RE: how to update SQL table from FoxPro cursor

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

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

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.

RE: how to update SQL table from FoxPro cursor

(OP)
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

RE: how to update SQL table from FoxPro cursor

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.

RE: how to update SQL table from FoxPro cursor

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/02/06/sql-server...
Another one:
http://www.sqlteam.com/article/using-bulk-insert-t...

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


RE: how to update SQL table from FoxPro cursor

Chris,

You said:

Quote:

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

Quote:

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

RE: how to update SQL table from FoxPro cursor

(OP)
Mike,

you mentioned

Quote (Mike)

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

RE: how to update SQL table from FoxPro cursor

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.

RE: how to update SQL table from FoxPro cursor

(OP)
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 smile) 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.

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

RE: how to update SQL table from FoxPro cursor

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:

CODE

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.

RE: how to update SQL table from FoxPro cursor

I mentioned Bulk Insert much earlier in this thread, but I didn't follow it up because it is specific to SQL Server, and Chris has not yet told us which back-end he is using. Chris, if you could clarify that point, it would be helpful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
Good morning gentlemen,

Quote (Mike)

I mentioned Bulk Insert much earlier in this thread, but I didn't follow it up because it is specific to SQL Server, and Chris has not yet told us which back-end he is using. Chris, if you could clarify that point, it would be helpful.
Mike, currently everything (accounting and manufacturing software) is using FoxPro dbf free table directories. We are creating custom tables for a few of our Crystal reports that would otherwise either not be able to run or would take forever to run. I'm now in the process of creating a web portal for all of our reports using Visual Studio. The reason for that is that the reports with custom tables need to use an xBase connector so that the custom tables can be refreshed by any user at any time (which doesn't work with ODBC). That in return requires that we have two seperate versions of report viewer (DataLink Viewer 9 and 2011). We probably could make do with only DataLink Viewer 9 (the older version) but like some of the funtionality of DLV 2011. And since the software company now has just released the accounting and manufacturing software with a SQL backend. So that's why I'm trying to use SQL (which is SQL Server 2008 R2).
Clear as mud? shadeshappy

Chris

RE: how to update SQL table from FoxPro cursor

Chris,

Quote:

Clear as mud?

Actually, no - and not particularly relevant. But never mind. I might have a better solution for you.

You say that the xBase connector in CR let's you access a table without opening it exclusively, but that you had an error when you tried to use it with a VFP table. I've just been experimenting with the xBase connector. I can get it to open VFP tables correctly, provided these are saved in the "old" (pre-visual) format.

With that in mind, you should be able to report on your original data, without any need to upload it to another database, as follows:

First, create the cursor as before, and then copy it to a Fox2x file:

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
COPY TO C6_X TYPE FOX2X 

Then create a report in CR, using the xBase connector to open C6_X.DBF.

This will give you all the required data, in the form that you need it for the report, without the shared-access issue.

There are a couple of caveats:

1. If the original tables contains long field names, these will be truncated in the report. That won't matter, provided the truncated versions are still meaningful.

2. Although mulitple CR users will be able to share the table, you won't be able to overwrite it with a new version (with the same name) while any of the reports is open. If you try, the COPY command will give a "file in use" error. One workaround might be not to overwrite it with new data, but to delete all the existing records, and then append the new records to it, with an occasional PACK when convenient.

I think you will find this method considerably faster than any of the other methods we have discussed.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
Mike,

Quote (Mike)

Then create a report in CR, using the xBase connector to open C6_X.DBF.

We would like to move away from the xBase connector since we are trying to use DataLink Viewer 2008 and Crystal Reports Viewer 2008 which gives you a Database Connection Error when using xBase. The way you have described it in your post is actually the way we've had it for a few years now, but that required using the older viewer software.

Quote (Mike)

You say that the xBase connector in CR let's you access a table without opening it exclusively, but that you had an error when you tried to use it with a VFP table. I've just been experimenting with the xBase connector. I can get it to open VFP tables correctly, provided these are saved in the "old" (pre-visual) format.

The error occurs when I use the newer viewer software (DLV 2008) and not when I try to oopen a VFP table.

So to make a long story short, I would like to move away from DataLink Viewer and use a browser based portal that uses Crystal Reports Viewer 2008 for viewing the reports. That requires either OLE DB or ODBC connectors because the CRV 2008 does not work with xBase. But using OLE DB or ODBC will put a lock on the table and I'm not able to refresh the table at-will if somebody else is viewing the report. So I thought that putting the data into a SQL table might be the best solution.

I'm sorry if I'm repeating myself, I know it can be hard trying to convey something in writing, especially if english is your second language (which is the case for me). I really appreciate your guys' help and so far I've already learned a lot!

Now to Olaf's code from earlier on:
1. There is no ID field, I set the Item field as the primary key.

CODE -->

SQLEXEC(connstr,"SELECT * FROM itemprojimstock WHERE 1=0","curAppend")	 
SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('Sendupdates',.T.,'curAppend')
CURSORSETPROP('Tables','itemprojimstock','curAppend')
CURSORSETPROP('KeyFieldList', 'Item','curAppend') && important: is ID really the name of a primary key field of the itemprojimstock table? Is such a field missing?
CURSORSETPROP('UpdatableFieldList','Item, Sono, Sorev, Sqty, Needdate','curAppend') && what about Sorev? Further fields? What about the ID field? I said you have to specify the key field here, too
CURSORSETPROP('UpdateNameList','Item Item,Sono Sono, Sorev Sorev, Sqty Sqty, Needdate Needdate','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.
SELECT curAppend
APPEND FROM DBF("C6")
TABLEUPDATE(2,.T.,"curAppend")
CLOSE ALL 


2. The above code works great, but I think there is a caveat that I think I forgot about:
a. the table holds work order information, and each time the data gets refreshed there is (mostly) all new information. If I understand the
code correctly, it keeps the primary key field and updates all the other fields, correct?
If that is the case, then tableupdate() may not work since I basically need the table to be cleared and the new data entered each time the code
runs.

Thanks,

Chris

RE: how to update SQL table from FoxPro cursor

Chris,

1. Are you sure the xBase problem is related to Data Link Viewe? As far as I know, Data Link Viewer just uses Crystal Reports as a COM object; it doesn't have any functionality of its own.

2. You say you are "not able to refresh the table at-will if somebody else is viewing the report". I still think that's because of an incorrect setting in your OLE DB setup. I'm sorry I can't give you more details, because I don't have access to that system at the moment.

3. If by "refresh the table", you mean overwrite it with entirely new contents, you will never be able to do that. You would in effect be deleting it and recreating it, and you can't delete a file (any file) while another application has it open. That's why I suggested you deleted the individual records instead, and then append the new ones.

Re point 2, if someone else here can tell Chris how to configure the Foxpro OLE DB driver so that it does not take exclusive use of the table, that might solve his problem. I know that you can do that with the ODBC driver (it's in the same dialogue as collating sequence, null support, etc.). But I'm not sure about OLE DB.

Olaf? JRB-Bldr? Anyone?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
So,

I've been looking at the tutorial for configuring the OLE DB datasource, unfortunately without success.
I set up two datasources, one OLE DB and one ODBC, and configured the report to use them (one at a time of course).
Both times I ran the report and then tried to run the VFP exe, both times getting the "File Access is denied" error.

Here is the OLE DB data connector:

Quote:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=VFPOLEDB.1;Data Source=C:\Temp\itemprojimstock.dbf;Mode=Share Deny None;Password="";Collating Sequence=MACHINE;DELETED=True;MVCOUNT=24000;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;REPROCESS=5
and the ODBC connector (yes, it is OLE DB but uses an ODBC DSN)

Quote:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Data Source=itemprojimstock;Mode=Share Deny None

I have tried changing the value for the Mode option, but it doesn't seem to make a difference.

Chris

RE: how to update SQL table from FoxPro cursor

Unfortunately, ODBC doesn't work with tables created with the latest versions of VFP. The VFP ODBC driver hasn't been updated since ver 6.0, and so will only work if you created your tables with an earlier version (or if you saved them as TYPE FOX2X, as I described earlier). That's why you need to use OLE DB instead.

Regarding the "File access denied" error, is it your VFP EXE or Crystal Reports that is giving that message? Earlier, I assumed it was CR, which is why I suggested it was an OLE DB configuration issue. But if the message is coming from the EXE, then it's likely that the EXE is trying to open the table exclusively, which it cannot do because it is already opened within Crystal.

If that's the case, the solution would be to modify the VFP EXE so that it does not open the table exclusively - assuming that's possible. Just to be clear, the table in question is the one that you are linking to within CR. It's got nothing to do with the C6 cursor that you created in your SQL uploading code. In fact, there are probably several tables involved; they must all be opened non-exclusively.

It could be that the VFP app needs exclusive use so it can perform maintenance, such as a pack or a zap. If so, you might need to change the app's logic a little. But I suggest you at least try opening the tables non-exclusively to see if it solves the "file access denied" problem. If it does, you can worry about how to deal with packing or whatever later.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

>The above code works great, but I think there is a caveat that I think I forgot about:
>a. the table holds work order information, and each time the data gets refreshed there is (mostly) all new information. If I understand the
>code correctly, it keeps the primary key field and updates all the other fields, correct?
Only halfways.

The code itself inserts new records, doesn't it? So indeed it answers your question with NO.

TableUPDATE() is the name of the function, but it also inserts and deletes, it does commit all changes in the buffer of an alias. Think of it as BufferCommit()

You are right the reason you have to specify a keyfield is mainly for updates and deletes of records. For inserts it's not always necessary to know a primary key field, nevertheless Tableupdate() will not work without that cursor property. In updates and deletes VFP generates it uses a WHERE id=? clause and sends these queries to whatever remote backend or DBFs, for which this also works. But also in case the primary field of a table is autogenerated and therefore a readonly field of a table, it's important for foxpro to know it has to skip this field in inserts it sends to the remote database.

It may seem weird to first select no record into a cursor, to then insert records and send them to the database, but the main advantage is, you only need the one tableupdate() to push in all data. The query on the remote backend is the equivalent of USE someview with NODATA. You get a cursor representing the remote table to work on, as if it was a DBF.

The number of commands of my code may be larger, perhaps than with the prepared statement, and you don't get around specifying fieldname lists, etc., but you don't scan each single record you want to insert. The main number of commands don't take many time, cursorsetprops are instantly done. You can generalize much of the code using AFIELDS() on the empty cursor you get from the SQLExec of the WHERE 1=0 query. And so it's not as you think mainly done for editing existing data. No. TABLEUPDATE() is the core VFP code for persisting data via such SQLEXec cursors, via updatable local or remote views, via cursoradapter and you can even use it with DBFs, so it's the thing function working for everything.

Bye, Olaf.


RE: how to update SQL table from FoxPro cursor

>since I basically need the table to be cleared and the new data entered each time the code
And to accomplish that simply send a SQLEXEC(connstr,"TRUNCATE itemprojimstock") before using the code and you start on an emtied SQL Server table. You need this with the SQLPreprare() and SQLExec() of single Inserts too, as this code also only adds to the table and doesn't clear it before.

And another way to do it would be using CREATE TABLE and finish with DROP TABLE. Or you work with Temp Tables in SQL Server.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Oh, and regarding your configurations. Both in OLEDB and ODBC you never specify a single DBF as Source, either you specify a DBC, but as you only have free fox2x tables you specify the directory only. The mode of the driver is either database or directory of free tables, it's never a single DBF.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Quote:

CODE -->

[oledb]
; Everything after this line is an OLE DB initstring
Provider=VFPOLEDB.1;Data Source=C:\Temp\itemprojimstock.dbf;
Mode=Share Deny None;Password="";Collating Sequence=MACHINE;DELETED=True;
MVCOUNT=24000;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;REPROCESS=5 

Also, it's not a good idea to use C:\Temp as the data source. You have no control over what other files might be present there, or how long your own files are likely to stay there.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

Well, I disagree slightly about Temp folder usage. VFP uses it for cursors and disposes them automatically, but that doesn't has to be that way, you can store something there temporarily, you should expect the temp folder to be cleared by a system reboot, but otherwise you can even use it to share data with several processes on the local machine. It's intended for that usage, isn't it? So mostly in regard of the likeliness of files to stay there, that's up to you, unless you need it more than on the currentsession, the current reprt output. The OS let's any process and any user write there and it's not an automatic trash folder. To get no overlap in usage of the same name you can simply check file existence, you can also use random names, as given by SYS(2015). And as the folder is local, you would only have a problem, if a user does two similar reports at the same time.

What's true: A separate folder for your temporary data would give you a clean overview as developer or admin in case you are asked to debug something, for example, the OS temp folder is shared with anything wanting to write a temp file.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Quote (Olaf)

I disagree slightly about Temp folder usage

Fair comment. I don't feel strongly about it, and it's not central to the original problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
Mike and Olaf,

great explanations and very useful information!

Quote (Mike)

Regarding the "File access denied" error, is it your VFP EXE or Crystal Reports that is giving that message? Earlier, I assumed it was CR, which is why I suggested it was an OLE DB configuration issue. But if the message is coming from the EXE, then it's likely that the EXE is trying to open the table exclusively, which it cannot do because it is already opened within Crystal.
I never posted the full code, otherwise you would have seen that I'm opening all needed tables non-exclusive:

CODE -->

Close All
Set Exclusive Off
Use F:\TIW\KOKAC\immaster In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKAC\imstock In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Somater In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\soheader In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Bmrev In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\bmsl In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Soroute In 0
Set TABLEVALIDATE To 0

**If File("C:\Temp\itemprojimstock.DBF" )
**Delete File ("C:\Temp\itemprojimstock.DBF")
**ENDIF

connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKATAT-507BB64\SQLExpress; Database=WebPortal;Trusted Connection=Yes")

Select soheader.partno As Item,;
	soheader.sono As sono,;
	soheader.rev As sorev,;
	soheader.sqty As sqty,;
	soheader.need_date As needdate,;
	soheader.priority,;
	soheader.salesno,;
	soheader.crea_date,;
	soheader.start_date,;
	soheader.remark1,;
	soheader.remark2,;
	soheader.instr1,;
	soheader.instr2,;
	soheader.plandate,;
	soheader.rev,;
	soheader.fgloc,;
	soheader.mtlloc,;
	soheader.solineno,;
	soheader.Userid,;
	soheader.part_desc,;
	Soroute.opno As routeopno,;
	Soroute.loadcenter,;
	Soroute.Descrip As Routedes,;
	immaster.misc04 As lottrack,;
	immaster.upccode As upccode;
	FROM soheader Inner Join Soroute On soheader.sono = Soroute.sono;
	LEFT Join immaster On soheader.partno = immaster.Item;
	Into Cursor c1

Select c1.*,;
	Somater.partno As partno,;
	STR(Asc(Somater.Phanref),3)As Phanref,;
	STR(Asc(Somater.Phanid),3)As Phanid,;
	VAL(Somater.qty_assy) As qty_assy,;
	VAL(Somater.qty_aloc) As qty_aloc,;
	Somater.Delmark As Delmark;
	FROM c1 Left Join Somater On c1.sono = Somater.sono And c1.routeopno = Somater.opno;
	INTO Cursor c2


Select c2.*,;
	immaster.Descrip As Descrip,;
	immaster.stockum As stockum,;
	immaster.misc04 As misc04,;
	immaster.lotstat As lotstat;
	FROM c2 Left Join immaster On c2.partno = immaster.Item;
	Into Cursor c3

Select c3.*,;
	imstock.lonhand As lotqty,;
	imstock.locid As lotloc,;
	imstock.lot As lotnum;
	FROM c3 Left Join imstock On imstock.Item = c3.partno AND c3.mtlloc = imstock.locid;
	Into Cursor c4 

Select c4.*, Iif(Empty(Bmrev.fgparent), Bmrev.itemparent, Bmrev.fgparent ) As fg;
	FROM c4 Left Join Bmrev On c4.Item + c4.sorev = Bmrev.itemparent + Bmrev.rev;
	into Cursor C5

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 

And from there on I have now adopted Olaf's code:

CODE -->

SELECT C6
lcExeCmd= "INSERT INTO itemprojimstock (Item, Sono, Sorev, Sqty, Needdate, Priority, Salesno, Crea_date, Start_date, Plandate, Rev, Fgloc, Mtlloc, solineno, userid, part_desc, routeopno, loadcenter, routedes, lottrack, upccode, partno, phanref, phanid, qty_assy, qty_aloc, delmark, descrip, stockum, misc04, lotstat, lotqty, lotloc, lotnum, fg, findno, scrpad) VALUES (?lcItem, ?lcSono, ?Sorev, ?lcSqty, ?lcNeeddate, ?lcPriority, ?lcSalesno, ?lcCrea_date, ?lcStart_date, ?lcPlandate, ?lcRev, ?lcFgloc, ?lcMtlloc, ?lcsolineno, ?lcuserid, ?lcpart_desc, ?lcrouteopno, ?lcloadcenter, ?lcroutedes, ?lclottrack, ?lcupccode, ?lcpartno, ?lcphanref, ?lcphanid, ?lcqty_assy, ?lcqty_aloc, ?lcdelmark, ?lcdescrip, ?lcstockum, ?lcmisc04, ?lclotstat, ?lclotqty, ?lclotloc, ?lclotnum, ?lcfg, ?lcfindno, ?lcscrpad)"
SQLPREPARE(connstr, lcExeCmd)
SCAN
lcItem = C6.Item
lcSono = C6.Sono
lcSorev = C6.Sorev
lcSqty = C6.Sqty
lcNeeddate = C6.Needdate
lcPriority = C6.Priority
lcSalesno = C6.Salesno
lcCrea_date = C6.Crea_date	
lcStart_date = C6.Start_date
lcRemark1 = C6.Remark1
lcRemark2 = C6.Remark2
lcInstr1 = C6.Instr1
lcInstr2 = C6.Instr2
lcPlandate = C6.Plandate
lcRev = C6.Rev
lcFgloc = C6.Fgloc
lcMtlloc = C6.Mtlloc
lcSolineno = C6.Solineno
lcUserid = C6.Userid
lcPart_desc = C6.Part_desc
lcRouteopno = C6.Routeopno
lcLoadcenter = C6.Loadcenter
lcRoutedes = C6.Routedes
lcLottrack = C6.Lottrack
lcUpccode = C6.Upccode
lcPartno = C6.Partno
lcPhanref = C6.Phanref
lcPhanid = C6.Phanid
lcQty_assy = C6.Qty_assy
lcQty_aloc = C6.Qty_aloc
lcDelmark = C6.Delmark
lcDescrip = C6.Descrip
lcStockum = C6.Stockum
lcMisc04 = C6.Misc04
lcLotstat = C6.Lotstat
lcLotqty = C6.Lotqty
lcLotloc = C6.Lotloc
lcLotnum = C6.Lotnum
lcFg = C6.Fg
lcFindno = C6.Findno
lcScrpad = C6.Scrpad
SQLEXEC(connstr)
ENDSCAN
SQLDISCONNECT(connstr)
CLOSE ALL 

Mike, I'm not sure what it is, but whichever way I turn with my OLE DB connector, it just won't work. The scenario I'm looking at is that somebody opens the report in DataLink Viewer 2011 (I think earlier I mistakenly wrote 2008) and while the report is open, another user can run the VFP exe, which will delete and recreate the table, and then view the report with the new data. But as we know now, with OLE DB I can't delete the table.

Olaf, so far the code works and I will try your modification suggestion of using TRUNCATE. One strange thing I have encountered though is that I'm getting a "Command contains unrecognized phrase/keyword" error on the lcExeCmd="INSERT..." line which seems to be tied to certain fields. I'm still trying to figure them all out, but so far I know that REMARK1 and REMARK2 are causing problems. Both are character fields in the VFP table and SQL table. I will have to work more on that.

Cheers,

Chris

RE: how to update SQL table from FoxPro cursor

In your line cExeCmd="INSERT..." the string literal is too long. You can only have 254 characters between the string delimiters. So you better put that in

CODE

http://www.tek-tips.com/viewthread.cfm?qid=1716941
TEXT TO cExeCmd NOSHOW
INSERT...
ENDTEXT 
Then you may also format the insert to a readable query, not an endless single line. SQL Server will acccept line breaks within a command, don't concatenate lines with semicolons, then! T-SQL only optionally expects semicolons at the end of commands.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

And indeed you could do more than one join per query, but I won't dive into that. If the data amount is limited for a report, it shouldn't matter much. It works and you may find it easier to maintain than a huge single query.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Quote:

... while the report is open, another user can run the VFP exe, which will delete and recreate the table ...

Chris, that's exactly what I've been trying to say. If the table is open in the report, no other process can delete it. That's got nothing to do with Crystal Reports, or DataLink Viewer, or OLE DB, or even FoxPro. You simply can't delete a file - any file - while it's open.

That's why I suggested that, instead of deleting the physical table, you delete all the old records and append the new ones. If you could do that, the "file access denied" error will disappear. That's true regardless of what program or access method you are using to access the file for reporting.

That said, I see you are making some progress with Olaf's suggestion, so by all means stay with that instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
Mike,

how would I go about just deleting the records and appending? I appreciate your help and I'm trying to understand both ways, yours and Olaf's. I think ultimately I'm hoping to go with the SQL solution, but I also would like to get the VFP solution just so I have options.

And I can't say thank you enough to both of you for this awesome thread and you sharing your knowledge! I hope that other people will be able to benefit from this thread!

Chris

RE: how to update SQL table from FoxPro cursor

I may answer in parallel to Mike: To delete records you can USE table Sahred and DELETE ALL. The problem then is not explusive access, but if the tables are still in use for a report, deleting data would effect the running report, wouldn't it?

Here generating random DBF names with SYS(2015) comes in handy, I just don't know how you could setup a crytal report to work on varying table names.

A solution might be to add a field into your dbf(s) with eg a reportnumber, Make it one parameter for Crystal to only read all records with a certain record number. Then you can run reports in parallel and would only need to delete data and pack th shrink the table. On e easy way would be to try to delete the files at the start of your exe, then this would happen about once a day when the first user enters. If you fail with deleting, that doesn't matter, just determine the next higher reportnumber to put into that seq number field and you seperate the report data for each user.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Since you put the data into LOCAL temp, I jsut wonder what the problem would be? Two users using the same computer in seperate windows sessions? If you have the typical multi user scenario with a workstation per user, this wouldn't matter. Also, if you could use the users tmep folder, which is in each users profile and also won't overlap, but then each user needs it's own Source Data for ODBC or OLEDB, which also isn't a problem, though, you can dynamcally determine that folder per user just by GetEnv("Temp"). Take a look at ? GetEnv("Temp"), it should contain your windows account name.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Quote (Olaf)

To delete records you can USE table Sahred and DELETE ALL. The problem then is not explusive access, but if the tables are still in use for a report, deleting data would effect the running report, wouldn't it?

That wouldn't be a problem. In Crystal, the user sees, in effect a snapshot of the data. If the data changes while the user is looking at it, the report doesn't change. The user must explicitly refresh the report to see the up-to-date version.

Your idea of storing a report number in the table, and filtering on that within the report, sounds like a good approach.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: how to update SQL table from FoxPro cursor

(OP)
Okay guys,

I think I'm going to have to let the VFP approach settle in a little over the weekend, my head is starting to spin!

Now the SQL code works nicely and is quite fast, but I discovered an issue that I wanted to bring to your attention to see if you have an idea what could be done about it:
I set the primary key to the Item field, which works just fine. But what I didn't realize is the fact that there are usually multiple rows per item.
Let me try to explain:
The workorder (Sono) shows all the parts that are needed to build an item (Item). So if an item has 10 components you will have 10 rows with the same Sono value, Item value, and so on. But what I'm getting right now is only one line per Item, since it is the primary key.
And as I'm typing this of course I realize what I need to do. I set the primary key to the partno field and now it shows all rows per Sono.

So at this point I would like to declare the SQL import a success! I still would like to work on the VFP code some more, but that will have to wait until Monday.

Thank you Mike and Olaf, if I could I would buy you guys a drink or two for all your help and efforts! Enjoy the weekend and I'll probably have more on Monday.

Cheers,

Chris

RE: how to update SQL table from FoxPro cursor

Cheers,

The solution to the primary key problem would also be to specify more fields, you're not limited to one. It's called KeyFieldList.
I wonder though, as you use the SQLPrepare approach you don't need to do cursorsetprops.

Have a nice weekend let it all settle and then see, if you can answer the questions about temp dir, users per computer etc. yourself. You might have an easier solution than you think, if there is no sharing of the local report data anyway. The same way cursors are also never shared between users. If your report data should be available longer to more users, the approach to export it to sql server (or import, depending on your point of view), will give a central report data repository for the weekly, monthly, yearly report data, if that's what it is.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

I had another idea about the primary key field: You don't need a primary key in your dbfs or the report data cursors on the VFP side, you can still have an automatic id field in the SQL Server table, eg an int IDENTITY(1,1), which compares to VFP Integer AutoInc.

And even though this IDENTITY field then will be part of the curAppend Cursor, you don't need to have it in your C6 cursor. The way VFPs APPEND FROM DBF works, it only fills fields by same name, so the id field not existing in C6 is not filled and all records you create have id=0. And that doesn't hurt for the TABLEUPDATE, as all those records have a buffer field state of 3, being new records. TABLEUPDATE determines what action to do in SQL Server from that field state, therefor it disregards the curAppend.id value, as TABLEUPDATE knows it will do INSERTS and the id is filled by SQL Server. Therefore it also puzzled me how you only got one record per itemno, that wouldn't matter in the same way. Simply see for yourself:

First Of all I created a simple table in SQL Server:

CODE --> T-SQL

CREATE TABLE [dbo].[test](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[data] [char](20) NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
You might run that in a query window in sql server management studio to create that test table in your database.

Then in VFP I did:

CODE --> VFP

* your connection string varies of course.
nHandle = SQLStringConnect("Driver={SQL Server Native Client 11.0};Server=....;Database=...;Trusted_Connection=Yes;")

SQLEXEC(nHandle,"SELECT * FROM test WHERE 1=0","curAppend")

SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('SendUpdates',.T.,'curAppend')
CURSORSETPROP('Tables','test','curAppend')
CURSORSETPROP('KeyFieldList','id','curAppend') 
CursorSetProp('UpdatableFieldList','data','curAppend')
CursorSetProp('UpdateNameList','id test.id,data test.data','curAppend')

Insert into curAppend (data) values ("hello")
Insert into curAppend (data) values ("world")
If NOT TABLEUPDATE(2,.T.,"curAppend")
   AError(laError)
   Set Step on
EndIf 
Close Tables All
SQLDisconnect(nHandle) 


While following my own instructions, I recognized one important detail I missed to explain correctly:

You actually DO specify the table name in the UpdateNameList, instead what I said earlier
CursorSetProp('UpdateNameList','id test.id,data test.data','curAppend')
'test' is the table name also specified in the 'Tables' setting.

But the good news is, you don't need to pick a field existing in your report cursor, which would perhaps be best as a primary key, nor do you need to specify a list of fields. Simply let the SQL Server table have an id field as specified above, and you specify it in the UpdateNameList, but not in the UpdatableFieldList, as the id is automatically generated, it isn't updatable, only SQL Server itself writes it at record creation.

Set a breakpoint, before TABLEUPDATE and see for yourself:
SELECT curAppend
Browse
=> Both records have id=0
? gefldstate(-1)
=> 334, that means it's a new record, as the states of the deletion flag and both fields are >=3. See GetFldState.

After you ran the code multiple times, the records in sql server will have sequential ID numbers. And even if you specify an existing id, you get new record, as said this is because of the field state.

Don't wonder why I did use INSERT INTO curAppend instead of APPEND, it doesn't matter how you act on curAppend, it only matters what's in the curAppend cursor in the end, and what field state the curAppend records and fields have. To update an existing record, it would need to come from SQL, and then have a field state of 1 instead of 3 and fields changed will get field state 2.

Oh, and don't be worried, I am having a nice weekend.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

(OP)
Olaf,

sorry it has taken me a while to get back to you. I had some work that needed to get done urgently and didn't get a chance to look at your post until today.
So I set up my script the way you suggested and added the ID field to the SQL table. But once I ran the script and then the report I would get every record multiple times in the report. It seems to me that the ID field is somehow messing up the reocrds.
That being said, I think the way we left the code on Friday really works. But what I have thought about over the weekend is that I might want to try and put the FoxPro code directly into my VisualStudio project, which is written in C#. Is that something we should continue to discuss here or should I open a new thread for that?

Chris

RE: how to update SQL table from FoxPro cursor

You get your report rows multiple times in a single run? Or more rows each time you repeat rerporting? You forget the code doesn't yet include emptying the tables or adding a report number for filtering report data for a single report, as suggested earlier. Run my code and you get 2 records more with each run, it only adds, it never updates or merges data.

In regard of adding this to C#, the only thing you can do is work with an OleDBCommand with the VFPOLEDB Provider. Yes, that would be a topic change and good reason to start a new thread.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

(OP)
Olaf,

I actually made a mistake yesterday by forgetting to recompile the VFP exe. So as I was testing your code manually it worked, but when I ran the web project it was still running the old code when calling the exe.

CODE -->

Close All
Set Exclusive Off
Use F:\TIW\KOKAC\immaster In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKAC\imstock In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Somater In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\soheader In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Bmrev In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\bmsl In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Soroute In 0
Set TABLEVALIDATE To 0

**If File("C:\Temp\itemprojimstock.DBF" )
**Delete File ("C:\Temp\itemprojimstock.DBF")
**ENDIF

connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKTS-VM\SQLExpress; Database=WebPortal;Trusted Connection=Yes")

Select soheader.partno As Item,;
	soheader.sono As sono,;
	soheader.rev As sorev,;
	soheader.sqty As sqty,;
	soheader.need_date As needdate,;
	soheader.priority,;
	soheader.salesno,;
	soheader.crea_date,;
	soheader.start_date,;
	soheader.remark1,;
	soheader.remark2,;
	soheader.instr1,;
	soheader.instr2,;
	soheader.plandate,;
	soheader.rev,;
	soheader.fgloc,;
	soheader.mtlloc,;
	soheader.solineno,;
	soheader.Userid,;
	soheader.part_desc,;
	Soroute.opno As routeopno,;
	Soroute.loadcenter,;
	Soroute.Descrip As Routedes,;
	immaster.misc04 As lottrack,;
	immaster.upccode As upccode;
	FROM soheader Inner Join Soroute On soheader.sono = Soroute.sono;
	LEFT Join immaster On soheader.partno = immaster.Item;
	Into Cursor c1

Select c1.*,;
	Somater.partno As partno,;
	STR(Asc(Somater.Phanref),3)As Phanref,;
	STR(Asc(Somater.Phanid),3)As Phanid,;
	VAL(Somater.qty_assy) As qty_assy,;
	VAL(Somater.qty_aloc) As qty_aloc,;
	Somater.Delmark As Delmark;
	FROM c1 Left Join Somater On c1.sono = Somater.sono And c1.routeopno = Somater.opno;
	INTO Cursor c2


Select c2.*,;
	immaster.Descrip As Descrip,;
	immaster.stockum As stockum,;
	immaster.misc04 As misc04,;
	immaster.lotstat As lotstat;
	FROM c2 Left Join immaster On c2.partno = immaster.Item;
	Into Cursor c3

Select c3.*,;
	imstock.lonhand As lotqty,;
	imstock.locid As lotloc,;
	imstock.lot As lotnum;
	FROM c3 Left Join imstock On imstock.Item = c3.partno AND c3.mtlloc = imstock.locid;
	Into Cursor c4 

Select c4.*, Iif(Empty(Bmrev.fgparent), Bmrev.itemparent, Bmrev.fgparent ) As fg;
	FROM c4 Left Join Bmrev On c4.Item + c4.sorev = Bmrev.itemparent + Bmrev.rev;
	into Cursor C5

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
	
SQLEXEC(connstr,"SELECT * FROM itemprojimstock WHERE 1=0","curAppend")	 
SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('Sendupdates',.T.,'curAppend')
CURSORSETPROP('Tables','itemprojimstock','curAppend')
CURSORSETPROP('KeyFieldList', 'ID','curAppend')
TEXT TO lcFieldList NOSHOW
Item, Sono, Sorev, Sqty, Needdate, Priority, Salesno, Crea_date, Start_date, Plandate, Rev, Fgloc, Mtlloc, solineno, userid, part_desc, routeopno, loadcenter, routedes, lottrack, upccode, partno, phanref, phanid, qty_assy, 
qty_aloc, delmark, descrip, stockum, misc04, lotstat, lotqty, lotloc, lotnum, fg, findno, scrpad
ENDTEXT
CURSORSETPROP('UpdatableFieldList', lcFieldList,'curAppend')
TEXT TO lcNameList NOSHOW 
Item itemprojimstock.Item, Sono itemprojimstock.Sono, Sorev itemprojimstock.Sorev, Sqty itemprojimstock.Sqty, Needdate itemprojimstock.Needdate, Priority itemprojimstock.Priority, Salesno itemprojimstock.Salesno, 
Crea_date itemprojimstock.Crea_date, Start_date itemprojimstock.Start_date, Plandate itemprojimstock.Plandate, Rev itemprojimstock.Rev, Fgloc itemprojimstock.Fgloc, Mtlloc itemprojimstock.Mtlloc, solineno itemprojimstock.solineno, 
userid itemprojimstock.userid, part_desc itemprojimstock.part_desc, routeopno itemprojimstock.routeopno, loadcenter itemprojimstock.loadcenter, routedes itemprojimstock.routedesc, lottrack itemprojimstock.lottrack, upccode itemprojimstock.upccode, 
partno itemprojimstock.partno, phanref itemprojimstock.phanref, phanid itemprojimstock.phanid, qty_assy itemprojimstock.qty_assy, qty_aloc itemprojimstock.qty_aloc, delmark itemprojimstock.delmark, descrip itemprojimstock.descrip, 
stockum itemprojimstock.stockum, misc04 itemprojimstock.misc04, lotstat itemprojimstock.lotstat, lotqty itemprojimstock.lotqty, lotloc itemprojimstock.lotloc, lotnum itemprojimstock.lotnum, fg itemprojimstock.fg, 
findno itemprojimstock.findno, scrpad itemprojimstock.scrpad
ENDTEXT
CURSORSETPROP('UpdateNameList', lcNameList,'curAppend') 
SELECT curAppend
APPEND FROM DBF("C6")
TABLEUPDATE(2,.T.,"curAppend") 
SQLDISCONNECT(connstr)

CLOSE ALL 

This is the code I'm using now and it works nicely. I have the ID field in my table and it is set to be the primary key.

Thanks again for all your help! Where do you think I should post the thread for converting all this to C#?

Cheers,

Chris

RE: how to update SQL table from FoxPro cursor

Well, if that's your goal (converting all this to C#), then this is not the way to go in C#.

What you could try is use the code (almost) as is, wrapped in an EXECSCRIPT done by the VFPOLEDB Provider. The problem is, VFPOleDB Provider does not support 100% of all VFP language and I'm not sure if this can be exceuted. That's why I put almost in paranthesis. It could work as is, but it would allbe done by VFPOleDB and C# would merely call that.

Another solution in the same direction would be putting all this into a OLEPUBLIC Class, compile as COM Server DLL and use that in C#.

In C# I would do this totally different, as you need to read this into datasets and datatables and have a totally differrent data access technology, this doesn't convert this way.

But give it a try, you won't find many doing C# here. You won't find many in a C# forum being able to read VFP.

Try both and see where it goes.

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

(OP)
Olaf,

great suggestions! Tried the following:

CODE -->

protected void Page_Init(object sender, EventArgs e)
        {
            OleDbConnection oConn = new OleDbConnection("provider=VFPOLEDB.1; data source='F:\\TIW\\KOKAC'");
            oConn.Open();
            OleDbCommand oCom = new OleDbCommand("EXECSCRIPT('DO F:\\Crystal\\Projects\\combine\\refreshSQL.exe')", oConn);
            oCom.ExecuteNonQuery();
        } 
but got the following error:

Quote:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Function is not implemented.

Source Error:


Line 22: oConn.Open();
Line 23: OleDbCommand oCom = new OleDbCommand("EXECSCRIPT('DO F:\\Crystal\\Projects\\combine\\refreshSQL.exe')", oConn);
Line 24: oCom.ExecuteNonQuery();
Line 25: }
Line 26:


Source File: C:\Documents and Settings\Chris\my documents\visual studio 2010\Projects\WebPortal\WebPortal\Production\Findings\WOTraveller.aspx.cs Line: 24

Stack Trace:


[OleDbException (0x80004005): Function is not implemented.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1084332
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
WebPortal.Production.Findings.WOTralveller.Page_Init(Object sender, EventArgs e) in C:\Documents and Settings\Chris\my documents\visual studio 2010\Projects\WebPortal\WebPortal\Production\Findings\WOTraveller.aspx.cs:24
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnInit(EventArgs e) +91
System.Web.UI.Page.OnInit(EventArgs e) +12
System.Web.UI.Control.InitRecursive(Control namingContainer) +140
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +480


I'm guessing the "Function is not implemented" has something to do with what you pointed out in your post that not all of the VFP language may be supported.
I will try your other suggestion next.

Chris

RE: how to update SQL table from FoxPro cursor

Well, yes, DO is not supported I guess. But if you have an EXE you can start it from C#, no I was talking about the VFP source code, not a compiled EXE.

If you want to start the EXE from C#, that's easy enough:

CODE --> c#

int exitCode;
ProcessStartInfo start = new ProcessStartInfo();
start.FileName = "refreshSQL.exe";
start.WindowStyle = ProcessWindowStyle.Hidden;
start.CreateNoWindow = true;
start.WorkingDirectory = "F:\\Crystal\\Projects\\combine";
using (Process proc = Process.Start(start))
{
   if (proc.WaitForExit(400)) exitCode = proc.ExitCode; else exitCode = -1;
} 

And the EXE can be anything, it's a seperate process, that has nothing to do with converting to C#, has it?

Bye, Olaf.

RE: how to update SQL table from FoxPro cursor

Also: Your code does not yet empty the itemprojimstock sql server table.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close