Querying SQL Server from VFP6

[audiopro]

I have been asked to write a small reporting app which queries an existing SQL server. I am ok with the VFP side of things but my knowledge of SQL server is nil. From previous experience of interfacing the unknown, this should be quite straight forward once I a up to speed on the method of interfacing. Could you point me in the direction of some appropriate reading.
Thanks

Keith

http://www.studiosoft.co.uk

 

[bborissov]

With VFP6 you have two choices:
Use SPT (check all functions that begins with SQL in HELP:

lnSQLConn = SQLSTRINGCONNECT([Driver=SQL Server;Server=YourServerNameHere;DataBase=YourDataBaseHere;Trusted_Connection=yes;])
IF lnSQLConn < 0
   AERROR(laError)
   MessageBox([Can not connect to SQL Server ]+laError[1,2])
   RETURN
ENDIF

** Build your query in the next lines
TEXT TO lcSQL NOSHOW TEXTMERGE
     SELECT .....
            FROM ....
     WHERE ....
 
ENDTEXT

IF SQLEXEC(lnSQLConn,lcSQL,[VFPCursor]) < 0
   AERROR(laError)
   MessageBox([Can not execute query ]+laError[1,2])
   RETURN
ENDIF

SELECT VFPCursor
BROWSE NORMAL
*** User that cursor as normal VFP cursor

Use RemoteViews, Check all for them in HELP.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Keith,

Borislav has pointed you in the right direction.

I would add that you should read the Help topics on "Accessing remote data". Make sure you understand the general principles, and then come back with any specific questions.

Also, don't let yourself get drawn into the inevitable argument about the pros and cons of remote views vs. SQL pass-through (SPT). For a simple reporting job like this, choose whichever way seems the easiest to you. You'll probably find that means remote views, but you can decide that for yourself.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

Thanks for that.
I will have a look into the options and no doubt ask more questions as the fog thickens. I have some experienece of MySQL databases on web servers and I am thinking there are similarities.

I will be querying a live dB from the word go but no data will be written, only read. There may be a need to write to the main dB in the future but not until the reporting is fully tested and proved.

Keith

http://www.studiosoft.co.uk

 

[audiopro]

From what I have read SPT creates cursors which I am familiar with. The SQLConnect command appears to be similar to the connect command in MySQL which I am also familiar with. This is the route I will investigate further.
Without getting into the debate, are there any distinct disadvantages with either option?

Keith

http://www.studiosoft.co.uk

 

[opticalman]

Audiopro

I use both.

I like views because I can create and edit them visually. It also makes it easy to edit and preview the results.

I use SPT for those things that a view cannot due. Or I use SPT when I am creating a class that will have a query that will vary.

Opticalman

 

[ilyad]

http://fox.wikis.com/wc.dll?Wiki~Client/ServerDataAccessTechniques

 

[Mike Lewis]

Keith,

there any distinct disadvantages with either option?

Not really. A remote view is really little more than a wrapper for a SQL pass-through cursor. Either way, you end up with a cursor which you can use as the data source for your report.

As I said before, just use whichever one you feel most comfortable with.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

The app is very simple and the query will always be the same. Multiple users will add records to the dB through the day. At the end of day the query will pull all of that days added records and a report will be outputted for each one.

Keith

http://www.studiosoft.co.uk

 

[bborissov]

Keith,
as a start check:
Class Based Data Management as Presented by Andy Kramek at Advisor Devcon 2001 at

http://www.tightlinecomputers.com/Downloads.htm

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP