×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Does anyone know where to find a si

Does anyone know where to find a si

Does anyone know where to find a si

(OP)
Does anyone know where to find a simple example of how to connect to SQL server in VFP9 (assuming I have been given the database details. The remote database is Microsoft SQL Server. I found this excellent example of coding for mySQL with a mySQL ODBC driver
https://efransiscus.wordpress.com/2009/03/15/tutor...
Something like this looks perfect, but I wonder how I could adapt it to SQL Server. Maybe SQL Server has a native ODBC driver ? Does anyone agree with this example ?
TIA John

RE: Does anyone know where to find a si

Hi John,

of course there are ODBC drivers to MSSQL. You can create a DSN to connect to MS SQL Server, too.

I'd rather not use a DSN though, simply use a connection string. That'll be usable for all three types of remote access, too:
1. Remote Views - where the sql connection string is applied to a connection object in a DBC
2. SQL passthrough (what that tutorial shows) - where you have SQLStringConnect
and
3. Cursoradapters, where also SQLStringConnect is used and the result handle is stored into the cursoradapter DataSource, while DataSourceType is ODBC.

You get the ODBC driver installed together with SQL Server Management Studio: https://docs.microsoft.com/en-US/sql/ssms/download...
Not 1000% sure, but the management studio doesn't work without being able to connect and it does so via ODBC.

You also find specific ODBC drivers standalone installations:
https://docs.microsoft.com/en-us/sql/connect/odbc/...

And then see https://www.connectionstrings.com/sql-server/

It starts out as simple as

CODE -->

h=SQLStringConnect(connectionstring)
SQLExec(h,"SELECT * FROM table","crsVFPResult")
SQLDisconnect(h) 

But it pays to look into cursoradapters in the VFP help. Help chapter "Data Access Management Using CursorAdapters" is a starting point.

And there's a lot to learn before you deep dive into writing your own SQL only based SQL life cycle of inserting, reading, updating and deleting data, cursoradapters and remote views easily allow you to get an updatable cursor, which means you act on the result cursor, you eventuially do a TABLEUPDATE() and the necessary SQL insert/update/delete statements are generated and executed by TABLEUPDATE().

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Does anyone know where to find a si

(OP)
Thanks Olaf, some serious study to do I think

RE: Does anyone know where to find a si

Yes, but worth it, as in the end after having a good set of cursoradapter classes you only write or visually design your queries and the rest then is just usage of the cursoradapter cursor including REQUERY() to refresh TABLEUPDATE() to commit changes and TABLEREVERT to revert changes.

I sketched what you need to know in a series of answers in thread184-1793207: how to edit and insert data records in SQL table using VFP data entry form

You are easily tempted to go for the three lines solution, but in the end, it costs developing your own complete set of things the buffering and tableupdate() mechanisms of VFP provide even for native DBF data access. So if you never used that you have to learn a lot, maybe even OOP class design. Then it's a steep learning curve. But that's even steeper in .NET with Entity Framework, albeit also much more detailed and complex in possibilities than VFP.

If you mainly only read data SQLEXEC of queries might be fine, but when you get to the point your user should be able to edit and add data and save it back to SQL Server you better have one of the updatable cursor types in your developer knowledge toolbelt.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Does anyone know where to find a si

(OP)
Fair comment but in this case it is purely an interface to send data up the backend and bring data down on a timer with no user intervention. My app is a vertical market accounting package and the client is getting an ecommerce website built. So my system only has to send minimal fields of stock records that have changed (stocklevel and price) up to the server and it only has to query the server to get the latest orders. I should have mentioned this.
My application is very old and I am working towards learning a new RAD called Livecode for the new version. It handles client sever very simply.
Cheers John

RE: Does anyone know where to find a si

In that case simple SELECTs, INSERTs and UPDATES might suffice, still a cursoradapter to a stock table can handle all three things easily. The usage of OOP is not depending on the amount of tables. Sure inheritance of things only applies, if you manage multiple tables.

The best advantage of Cursoradapter is being adapter, i.e. you work on a cursor, just like working on a DBF, so you might recycle code acting on a stock.dbf by just modifying it to not USE stock.dbf but let the cursoradapter get the data to display and perhaps add to or update.

Going for SQLEXEC means you need to turn code doing REPLACEs or APPENDs into corresponding SQL.
Again, you better know how your current code handles this, but as said the idea of adapters is to adapt the usual workarea/cursor principle to remote data, which actually helps with very legacy code, too.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Does anyone know where to find a si

(OP)
I will certainly have a good look at cursor adapters
Thank you
John

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close