Does anyone know where to find a si

[John Stephen]

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/2...mysql-odbc-connector-with-foxpro-9-and-mysql/

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

 

[Olaf Doschke]

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/sq...r-management-studio-ssms?view=sql-server-2017

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/sq...bc-driver-for-sql-server?view=sql-server-2017

And then see

https://www.connectionstrings.com/sql-server/

It starts out as simple as

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

 

[John Stephen]

Thanks Olaf, some serious study to do I think

 

[Olaf Doschke]

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

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

 

[John Stephen]

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

 

[Olaf Doschke]

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

 

[John Stephen]

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