Update sql command in foxpro

Meantime I noticed that the reason for the long wait for the update task to complete was the delayed response from the foxpro database. I was trying to update nearly a million record. However when I issue the same command within foxpro it updates in a few seconds. I am thinking of handling in SSIS the update via a loop updating around records at a time. Can you share your thoughts as well on this as to how this loop can be achived or suggest any other better alternatives.

Sign in. United States English. Ask a question. Quick access. Mike, is this what you are talking about? 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. 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. 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. 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.

The main number of commands don't take many time, cursorsetprops are instantly done. And so it's not as you think mainly done for editing existing data. Oh, and regarding your configurations. The mode of the driver is either database or directory of free tables, it's never a single DBF. 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 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. Quote Olaf I disagree slightly about Temp folder usage. Mike and Olaf, great explanations and very useful information!

Userid,; soheader. Descrip As Routedes,; immaster. Item; Into Cursor c1 Select c1. Descrip As Descrip,; immaster. Item; Into Cursor c3 Select c3. You can only have characters between the string delimiters. 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. 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. 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! 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 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.

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.

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

Therefore it also puzzled me how you only got one record per itemno, that wouldn't matter in the same way. Try it and see. You can create an updatable view if this is a standing job. There are plenty of variation on this. It can work in either direction; select the source table and send updates to the target, or select the target table and pull data from the source.

Why don't you use the SQL command instead? VFP supports it, unless you are using a much older version. Thanks, Brock. I have discovered that I cannot seem to add fields to the table either, which suggests some kind of lock.

But I can't see any related setting. It may be that the table structure is only one character for the gender as one can only be either biologically Male or Female and the short reference of "M" or "F" respectively. VFP is just not nagging you that you are trying to write a larger string value than the column in the table can handle and thus only keeping the "F" out of "FEMALE" attempt.

If you want the output in some report to show the full gender reference, you could do something like this in your sql query to get the data. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 7 years, 5 months ago. Active 7 years, 5 months ago.

Viewed 1k times.