Using SqlExec To Update Sql Table

[JICGreg]

Historically, I have used tableupdate() to update foxpro data to sql. However, I am running up against the 2gb limit and from a prior post it was suggested to use sqlexec() instead of table update. Unfortunately, I have not found anything right on point and being new to this I was hoping for some input

The structure of the sql table and foxpro table are the same. I simply want to put the foxpro data into the sql table as easily as possible. The update command seems to go column by column and since there are a lot of columns, I would rather not do that unless I have to.

I came up with this but not sure if it would work.

Use foxprotablename
SCATTER MEMVAR
sqlexec(connectionstring, 'insert into sqltablename from memvar')

Thanks for any thoughts.

 

[bborissov]

Check CusrorAdapter Class in HELP.
That is the easiest way when you have to use two or more different DataBases.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Mike Lewis]

You can't use MEMVAR as keyword in this context.

What SQLEXEC() does is it sends a command to a back-end database, typically SQL Server, Oracle, MySQL or something similar. The command is the second parameter (the INSERT command in your example).

However, the command in question has to be one that is recognised by the back-end database. Since MEMVAR is a Visual FoxPro keyword, there's no way that any other back-end will recognise it.

You have to construct the command using the actual field names in the table, something like this"

lcCmd = "INSERT INTO MyTable (ID, Name, Phone) ;
  VALUES (100, 'Mike', '123-4567')"
SQLEXEC(lnConn, lcCmd)

Also, the first parameter to SQLEXEC() isn't a connection string, as per your example. It is a connection handle, which you obtain by calling SLQCONNECT().

I suggest you read up on both of the above functions in the Help before you go much further.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You're mixing systems here. SQL syntax of foxpro and SQL-Server differs, SQL Servers SQL Syntax/Lnaguage is called T-SQL. They both have many things in common, but INSERT FROM memvar is not one of these common commands.

Also you have a false understanding of UPDATE SQL, both in VFP and SQL Server this is there to update existing data and is not limited to single columns.

But besides this one thing is for sure: The TableUpdate() command of VFP is very comfortable, and the good news is: it's also usable with remote data, even if that remote data is more than 2GB, all that matters is how large the read in data is.

As I understand the problem you have is the SQL Server Tables have grown to more than 2GB, but that only has an impact on VFP, in that you can't load all data at once. But you don't need to.

If you have your data in SQL-Server then you typically don't retrieve the full data of a table into DBFs or remote views or cursors, just the part of the data you need to process, display, update etc.

In regard to an VFP frontend application with SQL data in the backend, your app just displays portions of data, doesn't it? Do you load ALL customers ALL orders and ALL orderitems to display or print a certain order? No, you don't do that.

In VFP you might have been used to opening all these tables and navigate to the specific data of one order, but that doesn'
t apply to an SQL Server database. You can still use cursors instead of tables and reuse all the code you have to navigate to data, but you also only retrieve the portion of the data needed.

The very general approach to use VFP with an SQL Server backend is to load data as needed into updatable remote cursors, whatever this is - remote view cursors, sql passthrough generated cursors or cursoradapter generated cursors. As long as those remote cursors are set updatable and a mapping has been done of keyfields and updatable fields, then TABLEUPDATE() does update the remote SQL Database.

You need to switch to parameterized remote views, you can stay with your VFP data manipulation processes.

Bye, Olaf.

 

[Olaf Doschke]

I may have misunderstood you on this:

"Historically, I have used tableupdate() to update foxpro data to sql."

I read this as you are already using an SQL Server backend and you are using TABLEUPDATE() to update to(wards) the SQL Server.

Or does "to sql" rather mean "instead of doing this with the SQL language?

Bye, Olaf.

 

[JICGreg]

Olaf:

Your first interpretation was correct. I have data in foxpro, sql server is my backend database, and I'm trying to update sql server with the new data stored in foxpro.

Thank you all for your response. Since I really did not want to follow Mike's suggestion (due to the number of columns), I put a where clause in my remote data view that kept me under the 2gb limit and I can now update with the tried and true tableupdate() command. It seemed to work.

Thanks again for your input.

 

[Mike Lewis]

I put a where clause in my remote data view that kept me under the 2gb limit and I can now update with the tried and true tableupdate() command.

I'm glad that you've got it working. But, you know, I wish you had mentioned at the outset that you were using a remote view, instead of talking about SQLEXEC(). It would have avoided the misunderstanding, and helped us give you a better response.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Hello Greg,

good you got it working. What I don't understand then is, that you have both foxpro data and sql server backend. Is that for other applications not ported to remote database access? Or do you think of the remote views as foxpro data? Views are queries, not data.

Bye, Olaf.