update sql server data

[kenndot]

hi all

i've read a bunch of posts on querying sql tables and returning info, but I just want to update the SQL tables and that is all. I don't need to check anything or bring anything down from SQL, I simply have a table that I create in fox and I have an identical table in SQL that I want to send that to. What is the best way?

I am reading about cursoradapters now

I've used the "UPDATE" function before to just update a few fields in a remote DB, but I'm not sure if that is silly to do in this case - one by one i mean, it's one big dump of a table, I'm positive that everything is a compatible data type and ready to go and I've issued an

INSERT INTO mkt_headcount
SELECT *
FROM myFoxTbl

but this isn't working, I get
Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mkt_headcount'."

I can query the sql table and I know it's there, I'm thinking I'm forgetting a step somewhere

I guess my main question here is what's the best way to approach this? Then, why isn't that query working?

I tried to think of all details to tell, but please tell me if you need more information to respond, thank you much

 

[kenndot]

ok i found 1/2 my problem, i was connected to the wrong db, so now fox can see the table on SQL but when I execute the query the error is that the SQL table I've selected to update is an 'invalid object'...

do i HAVE to create the cursor object to insert data from foxpro? can someone tell me the steps to take?

 

[Mike Lewis]

Kenndot,

First, I would advice against using cursoradapters in this way. I have experimented with doing something similar, and I received intermittent error messages (in some cases, the message appeared even though the update succeeded, which does not inspire confidence).

However, you should have no difficulty with this if you use SQLEXEC(). Be sure that your connection object is pointing to the correct database. Also, don't do SELECT * FROM MyFoxTbl. Instead, list the fields explicitly in the SELECT clause, and make sure the order of these fields exactly match the order in which they appear in the SQL Server table (but omit any Identity fields in the server table).

If this doesn't help, perhaps you could post more details of your code and the structures of the two tables.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mike Lewis]

Kenndot,

I am talkinb rubbish. Having had a closer look at your post, I realise why your code does not work. Sorry for not seeing this before.

The problem is that you are explicitly refering to a VFP table (MyFoxTabl) in code that you are sending to the server. The server won't have any idea what that table is, and so will report an error.

You need to take a different approach. Perhaps the easiest way is to loop through the record in the Fox table. For each record, construct an Insert statement which includes the actual field values. Send that statement to the server.

Alternatively, consider using an updatable remote view.

Let mw know if you need any clarification on either of these ideas.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

Hi Mike,

Yes, I ended up figuring out WHY it wasn't working, but I just didn't know what steps to take to make it work. I considered the looping and inserting, but I thought, gee this has to be a common transaction, perhaps foxpro can handle this without going through all that, but is that what you are saying the updatable remote view would do?

Can you clarify on that for me? I understand the other suggestion completely.

Thank you

 

[craigsboyd]

Kenndot,

An updateable Remote View will allow you to act on it just like you would a normal table/cursor in VFP. Anything you do to it will happen to the mkt_headcount table in your backend once you issue an update. Append From command along with a whole host of other commands will be at your disposal and you can let VFP do all the heavy lifting for you.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[kenndot]

okay, i just created a remote view.... the code that was created automatically apparently has a problem with itself, I get a command missing required clause error with the code created by using the view wizard....

 

[craigsboyd]

Can you post it?

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[Mike Lewis]

Kenndot,

Just to follow up the remote view suggestion ...

Create a remote view in the view designer (don't use the wizard). In the Fields tab, you should see all the fields, as they exist on the server. Select all these fields.

In the Update Criteria tab, select the primary key (this must be a field which has a unique or primary key constraint on the cursor; chances are it will already be selected by default). Select the updatable fields. This should be all the fields, including the primary key (but don't select the primary key here if it has an Identity attribute on the server). Finally, check the 'Send SQL Updates' box in the bottom left corner.

Save the view.

Now, open the view with the NODATA clause (USE MyView NODATA). If you do a browse, you should see an empty grid, with the column headers from the server, but no data.

Next, do an APPEND BLANK. Either interactively enter values for all the fields in a Browse, or do a REPLACE to insert all the values. (Or you could issue an INSERT command to add the blank record and fill in the fields in one step.) Either way, make sure you provide values for all fields, except any fields on the server which either accept nulls or have Default constraints.

Repeat the process for all your records. As you finish each record, you should see it in the table on the server.

That's just a quick overview. If you run into any difficulties, just shout.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[badukist]

Hi
I have this code, I've used it to export our DBFs to Firebird databases. If type translation string is changed with corresponding types in SQL Server, it should work. It also does a drop table - create table, these lines must be commented if the table already exists on sever (this was not our case)

PROCEDURE dbf2fb
PARAMETERS tcTable, tnConnHnd, tlAddData
*parameters:	tcTable		table name or full path
*				tnConnHnd	valid connection handle to Firebird database returned by SQLConnect()
*				tlAddData	transfer table data?
_t1=SECONDS()
LOCAL lcCreateTableSQL, lcCreateIndexSQL, lcDropTableSQL, lcInsertDataSQL, ;
		lcVFPTypes, lcFBTypes, lcOptions, lnFields, lnNDX
LOCAL ARRAY laVFPTypes[9], laFBTypes[9], laOptions[9]

USE (tcTable)

*types translation table
lcVFPTypes = 'C^D^L^M^N^F^I^B^Y^T'
lcFBTypes  = 'CHAR^DATE^SMALLINT^BLOB SUB_TYPE TEXT^NUMERIC^FLOAT^INTEGER^DOUBLE PRECISION^NUMERIC^TIMESTAMP'
lcOptions  = 'C^ ^ ^ ^N^N^ ^ ^N^ ^ '
lnFields = AFIELDS(laFlds)
ALINES(laVFPTypes,lcVFPTypes,.t.,'^')
ALINES(laFBTypes,lcFBTypes,.t.,'^')
ALINES(laOptions,lcOptions,.t.,'^')
tcTable = ALIAS()		
*build SQL statements
*lcDropTableSQL = [DROP TABLE ]+tcTable				

SET TEXTMERGE on
SET TEXTMERGE TO memvar lcCreateTableSQL NOSHOW 

\\CREATE TABLE <<tcTable>> (
FOR i=1 TO lnFields
	IF i>1
		\\, 
	ENDIF
	lnFieldTypeRange = ASCAN(laVFPTypes, laFlds[i,2])
	IF !'getkey'$LOWER(laFlds[i,9])
		\\<<laFlds[i,1]>> <<laFBTypes[lnFieldTypeRange]>>
	ELSE
		\\<<laFlds[i,1]>> BIGINT 
	ENDIF 	
	IF laOptions[lnFieldTypeRange] = 'N'
		\\(<<laFlds[i,3]>>,<<laFlds[i,4]>>)
	ENDIF
	IF laOptions[lnFieldTypeRange] = 'C'
		\\(<<laFlds[i,3]>>)
	ENDIF
	IF laFlds[i,5] = .f. 
		\\ NOT NULL 
	ENDIF
NEXT
\\)
SET TEXTMERGE to
*?SQLEXEC(tnConnHnd,lcDropTableSQL)
*IF SQLEXEC(tnConnHnd,lcCreateTableSQL) < 0
*	AERROR(laError)
*	DISPLAY MEMORY LIKE laError
*	?lcCreateTableSql
*ENDIF
SQLCOMMIT(tnConnHnd)
*build and execute SQL statements to add data if third parameter is .T.
IF tlAddData=.t.
	z=0
	SET TEXTMERGE on
	SET TEXTMERGE TO memvar lcInsertDataSQL NOSHOW 
	\\INSERT INTO <<tcTable>> (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\<<laFlds[i,1]>>
	NEXT
	\\) VALUES (
	FOR i=1 TO lnFields
		IF i>1
			\\, 
		ENDIF
		\\?m.<<laFlds[i,1]>>
	NEXT
	\\) 
	SET TEXTMERGE to
	SQLPREPARE(tnConnHnd,lcInsertDataSql)
	SCAN
		SCATTER MEMVAR 
		z=z+1
		FOR i=1 TO lnFields
			lcVarName = 'm.'+laFlds[i,1]
			DO case
			CASE TYPE(laFlds[i,1]) = 'L'	&&logical type, need conversion
				&lcVarName = IIF(EVALUATE(laFlds[i,1]) = .T.,1,0)
			CASE TYPE(laFlds[i,1]) = 'D' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty date, need conversion
				&lcVarName = '1900-01-01'
			CASE TYPE(laFlds[i,1]) = 'T' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty datetime, need conversion
				&lcVarName = '1900-01-01 00:00:00'
			CASE TYPE(laFlds[i,1]) = 'M' AND EMPTY(EVALUATE(laFlds[i,1]))	&&empty memo
				&lcVarName = ''
			ENDCASE
		NEXT
		IF SQLEXEC(tnConnHnd) < 1
			CLEAR
			AERROR(xx)
			DISPLAY MEMORY LIKE xx
			?'insert error'
			SUSPEND
		ENDIF
		IF MOD(z,1000) = 0
			SET MESSAGE to STR(z)
			SQLCOMMIT(tnConnHnd)
		endif
	ENDSCAN
ENDIF
CLEAR
?
?SECONDS()-_t1

*build and execute SQL statements to create indexes
lnNDX = ATAGINFO(laIndexes)
FOR i=1 TO lnNDX
	lcIndexName = ALIAS()+'_'+laIndexes[i,1]
	IF laIndexes[i,2] = 'PRIMARY'
		lcCreateIndexSQL = [ALTER TABLE ]+tcTable+[ ADD PRIMARY KEY (]+laIndexes[i,3]+[)]
	ELSE 
		lcCreateIndexSQL = [CREATE INDEX ]+tcTable+[_]+ALLTRIM(STR(I))+[ ON ]+tcTable+[ (]+LaIndexes[i,3]+[)]
	ENDIF
	?lcCreateIndexSQL
	
	SQLEXEC(tnConnHnd,lcCreateIndexSQL)
	SQLCOMMIT(tnConnHnd)
NEXT

RETURN

 

[kenndot]

wow, thanks for all the responses, i succeeded with the remote view and it's working perfectly for me, thank you all again, that's exactly what i needed!

 

[kenndot]

okay, one more question... more opinion, but I'm very interested in hearing it

I have spoken to some people who say they feel "nervous" about using an updateable remote view. The reason I think is because they are concerned what if the process fails midway perhaps? Foxpro has a rollback() or tablerevert() function does it not?

Will someone share their opinion and give me an example/reason, especially if you share this view of being "nervous" about using the remote view... I'm just trying to understand

thanks

 

[fluteplr]

If you are nervous about an update failing in the middle and you should be at least cautious. You can wrap your tableupdates in a begin transaction / sqlcommit or sqlrollback set of statements. Then if for some reason the update fails in the middle you can do a rollback or if your connection drops alltogether sqlserver will rollback your transactions since it will never get a commit statement.

Look inder sqlcommit() in the help file for more info.

BTW You should do this even if you are doing your updates with sqlexec statements. Especially if you are updating more than one table as part of a process and it is important that they all take or fail together.

 

[kenndot]

hi flute,

i don't know if i said this in a earlier thread, but what I'm doing is one big data dump, i'm appending app. 70K records to the end of a sql table

I am confused about a few things though, maybe you can clear it up...

I created the remote view using the wizard, so it writes it's own code there, now doing that, I never actually have to open a connection explicitly i.e. SQLCONNECT() and since I am just dumping a bunch of records, I simply update that view via a foxpro statement NOT USING sqlexec() and it's working fine. Now, I'm reading

= SQLROLLBACK(gnHandle)

to rollback or you said if I don't issue a COMMIT then it loses it anyway?

I'm confused because I'm not using that method, so I don't have a handle to use to commit or rollback, i'm doing everything through the view.

also i'm reading that you can only use begin/end transaction in foxpro db/tables?


Are the two methods completely different? Please let me know if I need to clarify anything.



thanks

 

[fluteplr]

1. If you do not do a 'begin transaction' then you don't have to worry about a sqlcommit or a sqlrollback. Since there is no transaction to begin with.

2. To get the connection handle a view is using is easy
nconnection = cursorgetprop('connectHandle','myview')

3. Don't confuse the foxpro transactions with the sqlserver transactions.

Again look up sqlcommit() and related topics in the help file.
Have to run I will try to post more later.

 

[Mike Lewis]

Kenndot,

To add to the good advice advice that Flutepir has given you, there is no easy way to wrap a remote view update in a transaction. SQLCOMMIT() and SQROLLBACK() are really intended for use with SQL pass-through. The VFP BEGIN TRANSACTION / END TRANSACTION commands won't work in this context. Nor will the SQL Server BEGIN TRANSACTION / COMMIT TRANSACTION, because there would be no opportunity for you to send them.

Nor is buffering the answer, as that manages the updates to the view itself, not to the server tables.

Keep in mind that each individual update is automatically wrapped in a transaction on SQL Server (you are using SQL Server, aren't you?; I'm not sure if we established that). However, I assume you want the entire process to be rolled back if one update fails.

One thing you could do is to increase the update batch size ("Records to batch update" in the Advanced Options dialogue in the view designer; be sure to close and re-open the view in order for a change in this setting to take effect). If you set that to the number of records you are sending, it will perform all the updates in one go, which would reduce the risk of failure (but it would still be possible for a single update to fail -- perhaps becuase of a failure of a check constraint).

One other point: Is this upload a one-off process, or are you writing an app that will do it many times? If the former, you could always manually intervene in the event of a failure. You would need to do that anyway, in order to figure out what went wrong and how to fix it. You could then simply send a DELETE command to delete the existing records from the server table and start again.

Alternatively, if this is a one-off job, you might consider using the DTS utility that comes with SQL Server to do the whole thing for you. Let us know if you need any guidance on that.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

Hi Mike,

To answer you, this is a process that will run once a month, the data is "calculated" in foxpro and then I need to append the whole foxpro table to the sql table. There are at least 70000 records that I want to dump all at once. I want to append them all at once as I have already cleaned the data up and it's ready to go. I'm not going to loop through anything, I just want to put it up there in one big swoop.

Is the remote view just not the good way to go here? I'm just trying to find some opinions on the matter, that's all.

PS, I have looked up those functions that flute mentioned and that's what prompted the questions in the first place, I didn't know if I was confusing two different methods because with the view I didn't have a handle and sqlcommit() needs one to be passed, so I figured I didn't understand something.

 

[kenndot]

I meant to add another question..

I couldn't find this answer before, but I was using sql pass through in the beginning, but I couldn't find an effective way, short of loop through my 70K records and inserting each individual one, to get the whole table up there to sql server.

Is there a way to do this? Just append a bunch of records all at once using sql pass through? I'm more than willing to try a different method.

 

[Mike Lewis]

Kenndot,

Now that I understand what you are trying to achieve, I think I can give you a better answer.

With the volumes that you are talking about, remote views will not cut it -- they will be way too slow (plus there are the problems with transactions). An alternative would be to send a series of INSERT statements via SQL pass-through, but that will also be very slow.

I think you should be looking to SQL Server itself for the solution. SQL Server has a command called Bulk Insert, which is exactly what you want. Essentially, it imports data in the form of a text file into an existing table. You have a lot of freedom in how the text file is formatted -- it could be comma-delimited, for example.

You would have to arrange for VFP to export to that format as an imtermediate step, but even so, the whole thing will be much faster and easier than the other methods. Also, the entire Bulk Insert can automatically be wrapped in a transaction.

Now, going one stage further, SQL Server has a couple of utilities which are essentially wrappers for Bulk Insert. One is called BCP, but it is awkward to use and not very well documented. A better choice is Data Transformation Services (DTS). This has a wizard-style interface. It can import from a variety of formats (not just text files). Once you have used it once, you can save the specification in a 'package' for subsequent re-use. You can even invoke the package programmatically from VFP.

If you want to experiment with this, you can invoke DTS from the SQL Server Enterprise Manager' tools menu, or from the Start menu of a PC that has SQL Client Tools installed. Either way, look for it under the heading Import / Export (or something similar).

Let me know if this is of any help.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[zhavic]

The most effective way is run SQL like this:

INSERT INTO your_table
	SELECT a.*
	FROM OPENROWSET( 'VFPOLEDB.1',
			         'C:\path_to_directory_where_the_table_is';;,   
    			     'SELECT * FROM your_foxpro_table') AS a

If you don't have VFPOLEDB, download it from Microsoft download page

http://www.microsoft.com/downloads/...58-7a94-4ae1-a59e-965869cb3bc9&displaylang=en

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.