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

 

[Olaf Doschke]

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.

 

[Mike Lewis]

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

 

[campagnolo1]

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

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

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:

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:

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

 

[campagnolo1]

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

 

[Olaf Doschke]

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.

 

[campagnolo1]

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.

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

 

[Olaf Doschke]

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.

 

[campagnolo1]

Olaf,

great suggestions! Tried the following:

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:

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

 

[Olaf Doschke]

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:

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.

 

[Olaf Doschke]

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

Bye, Olaf.