VFP data to SQL Server

[jrajesh]

Hi all,
Due to data storage and security issues, we are planning to convert an existing VFP app. to VFP front end and SQL server backend.
I would like to know the issues that are to be taken into consideration for this upsizing.
I tried upsizing the existing data to the MS Sql server that ships with XP. I get error messages on records with empty date fields. I tried populating the empty date fields with ctot('31/12/1899 00:00:00') as per a suggestion in some web site. Still no joy.

Should I buy the SQL server or can I use the existing version for trial?

Thanks in advance for your advise,
Rajesh

 

[ramani]

HI

Try this..

CTOT("1899-12-31T00:00:00&quot



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

Rajesh,

There is a lot more to converting converting a VFP app to SQL Server than merely upsizing the data. There are many database design and user interface issues to consider, plus all kinds of small changes that will be necessary to your code.

In general, you should make sure you have a solid understanding of SQL Server -- and of the various ways of accessing it from within VFP -- before you start to write any code. I don't mean to discourage you -- it is not all that difficult. But it's not merely a matter of running an upsizing routine either.

To deal with your specific question .... As you have found, SQL Server does not support blank dates. Sending a "base" date like 31/12/1899 should work OK. In your case, the error message might have come up because of differences in date formats. SQL Server supports both DMY and MDY formats, but you need to tell it which one to use, and the setting is specific to the connection. (On my system, MDY is the default, but yours might be different.) VFP, on the other hand, supports BRITISH, AMERICAN and several others. So, the first step would be to ensure the setting is the same on both systems.

Mike


Mike Lewis
Edinburgh, Scotland

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

 

[jrajesh]

Ramani and Mike: Thank you very much for your advise.

Ramani: Shall try out your CTOT("1899-12-31T00:00:00&quot suggestion.

Mike:
You are right. I am, though, vaguely aware of the depth of changes the 'Upsizing' warrants (primarily from 'Creating Client/Server Solutions' section of MSDN.)

My concerns are:
* Whether to use SPT or remote views or must it be a combination of both (what are the cases when I must choose SPT over remote views?). At present our VFP app. does not use any views. It creates cursors from sql statements generate on the run from various trigger points. Am I right in assuming that these are to be converted to views with a 'Requery' at the appropriate place or would a SPT query be ok? (Confused....)
* To what extent is the Upsizing wizard reliable or should I use the Data Transformation Services (DTS) utility of SQL Server?
* Any other technical issues that I must be aware of.

BTW, DTS etc., are all big words for me (gleaned from the web). I've not as yet used SQL Server. Scheduled to attend a course in SQL server soon.

Would greatly appreciate any further pointers.
Thanks again for your help and advise.
Warm regards,
Rajesh

 

[Mike Lewis]

Rajesh,

Re using remote views vs. SQL pass-through ... You'll probably end up using both. Each has its advantages and disadvantages.

I'm not a big fan of the upsizing wizard. It doesn't properly handle all data types, and it is difficult to use on a re-usable basis. DTS tends to be more reliable.

However, my preferred approach is to write my own upsizing code, which is not all that difficult. You can use SQL pass-thought to send a series of INSERT commands to the server, based on the data in a DBF.

The chances are that you will want to take the opportunity of the upsizing process to make changes to your table structures and to generally clean up your data. If so, writing your own code gives you the chance to do that.

There's a lot of information on VFP + SQL Server on the web that should help you get started. I suggest you start by taking a look at some of the articles on my own web site (see below for URL).

Mike


Mike Lewis
Edinburgh, Scotland

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

 

[ramani]

HI

If you are going with VFP8, then good time to think of CursorAdapter class and such concepts.

You can read from the following link..

http://www.code-magazine.com/Article.aspx?quickid=0301062

ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[jrajesh]

Ramani:
1/ Thanks for your link on Cursoradapter. Am presently using VFP 7. But we do have a copy of 8. Will surely try the CursorAdapter.
2/ Tried your CTOT("1899-12-31T00:00:00&quot idea. Still no joy. I think I'll go the Mike way and programattically populate the SQL server tables.

Mike:
Your website is very informative. Thank you very much.
Shall try your suggestion for updating the SQL server programatically.


Thanks again Ramani and Mike.
You've helped me a lot.
Warm regards,
Rajesh

 

[badukist]

Shall try your suggestion for updating the SQL server programatically.

Maybe this procedure can give you a start. I've used it to transfer data from our tables to an FirebirdSQL database some time ago.

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. and !(laFlds[i,2] = 'D' OR laFlds[i,2]='T')
		\\ 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.
lcMemoConversion = ''
IF tlAddData=.t.
	lnRecordsProcessed=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 
		lnRecordsProcessed = lnRecordsProcessed + 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]))
				&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 date, need conversion
				&lcVarName = ' '
			ENDCASE
		NEXT
		IF SQLEXEC(tnConnHnd) < 1
			CLEAR
			AERROR(xx)
			DISPLAY MEMORY LIKE xx
			?'insert error'
			SUSPEND
		ENDIF
		IF MOD(lnRecordsProcessed,100) = 0
			SET MESSAGE to STR(lnRecordsProcessed)
		endif
	ENDSCAN
ENDIF
CLEAR
?
?'Data transferred in :', SECONDS()-_t1 , 'seconds'

*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

 

[jrajesh]

Badukist,
You code certainly is most useful. Thank you.
Shouldn't you perhaps put it in the FAQ section?

Thanks again and Best Regards,
Rajesh