Am i doing the right this here for conversion

[FoxWing]

I'm doing conversion from vfp to MSSQL database. When i get my hand dirty yesterday changing a screen, i'm really worry whether am i doing it a right way although it's functionally works.

Because this scrren i was doing has nagivation buttons (Top,Prev,Next,Last), I decided to select the whole table (2000 records max) using SPT into a cursor, CurCustomer.

In the nagivation buttons, i point it to this Cuscustomer
as i was pointing it to a VFP table.

Example:

The way i did my existing system consists the following steps :

From (Fox Table)
SELECT Customer
IF NOT EOF()
skip
ENDIF

What i doing now is for MSSQL :
(In the Form Init())
nConnection = SQLCONNECT()
SQLEXEC(nConnection, "Select * from Customer", "CurCustomer")
SELECT CurCustomer
SQLDISCONNECT(nConnection)

(In the Form NEXT button Click())
SELECT CurCustomer
IF NOT EOF()
skip
ENDIF

This reason i do this is it requires less rewrite of codes therefore, the core logic will not be heavily touched which might result more chances for bugs as the current system is quite stable.

Am i doing a right thing here ? Any advice will be much appreciated.

Thanks.

 

[Mike Gagnon]

It depends what you need to do. The way you are accessing the data would indicate you only want to view the records, and not modify or add, otherwise you would use a Remote view. I may also poit out you are not using any error trapping in this code, if you don't get a connection to the database, the error VFP will throw moght look a little ugly.
for Example:

GetConnHandle = SQLConnect(lcServer,lcUser,lcPassWord)
If GetConnHandle > 0  && Connection established
	= SQLPrepare(GetConnHandle, 'Select * from unreliable')
	GetQuery  = SQLExec(GetConnHandle)
	If GetQuery > 0  && We have a cursor!
		Select * From Sqlresult Into Cursor unreliable Readwrite
		Index On countrycode Tag country
	Else
		Messagebox("No records")
	Endif
	= SQLPrepare(GetConnHandle, 'Select * from activitylog where ldelete= "0"')
	GetQuery  = SQLExec(GetConnHandle)
	If GetQuery> 0  && We have another cursor
		etc......

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

FoxWing,

This reason i do this is it requires less rewrite of codes

That is the root of the issue.

If your main concern is to do the conversion as quickly and cheaply as possible, you are taking the right approach. In fact, you can make it even easier by using remote views instead of SQLEXEC(). If you make the views updatable, that will also deal with the updating issue that Mike Gagnon mentioned.

However, if you take this approach, performance will suffer horribly. As far as performance is concerned, there is no point in going to a back end unless you redesign your application to take advantage of the client-server architecture.

Essentially, that means that you throw away your VCR-style navigation buttons, and provide an interface that lets the user search for a specific record each time. The aim is to let the server do the record selections (plus any sorting or grouping that might be relevant) and to return the smallest possible result set to the client.

It's really a trade-off between performance on one hand and ease of conversion on the other.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[FoxWing]

Mike,
Thanks for your swift response.

Yeap, I missed out the Error checking. Thanks for reminding me.

I have 1 questions after reading ur reply, if u don't mind.

Is there a cursor created when there is no record selected in the SQLEXEC() ? I tried in my office (command window), i created an empty cursor. But, with my laptop in my program, it created nothing.

thanks.

 

[Mike Gagnon]

FoxWing.

Sorry, the code is wrong. Yes it does create a cursor everytime empty ot not. The code should read.

If GetQuery > 0  && We have a cursor!
        Select * From Sqlresult Into Cursor unreliable Readwrite
        Index On countrycode Tag country
    if !eof()
      ** Do something here.
    Else
        Messagebox("No records")
    Endif
endif

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.