Select from Remote SQL that runs a VFP process and Return it back in result cursor?

[stanlyn]

Hi,

I need to do a select statement into a remote sql server with ??. The sql server would use the ?? and run a VFP process that returns a result. The result is then returned back to the original caller.

A vfp app make the call to sql server,
sql server will make a call to its local vfp (on same box) and get result
sql server will return result back to original vfp app.

The ?? above is (fill in the gaps as I do not know). I suspect this can be done in a ss stored proc. Any suggestions on how or what to look for would be great.

Thanks,
Stanley

 

[Mike Lewis]

Stanley,

Are you asking how to call the stored procedure from within a VFP application? Or are you asking how the stored procedure should make the call to VFP?

If the former, then the easiest way is to use SQL pass-through? If you don't know how to do that, check the Help for SQCONNECT(), SQLSTRINGCONNET() and SQLEXEC().

Also, when you say "sql server", do you in fact mean "SQL Server"? It's an important distinction. The former could mean any remote server, regardless of make or model. The latter is a specific product, made by Microsoft. This is important because each back-end has its own SQL dialect and its own conventions.

Assuming you are asking about the Microsoft product, your code might look very roughly like this:

lnConnect = SQLSTRINGCONNECT("<your connection string here>")
IF lnConnect <= 0
  * Your error processing here
ENDIF

lcCommand = "EXEC <name of your stored procedure plus parameters here>")
lnReply = SQLEXEC(lnConn, lcCommand, "csrResult")
IF lnReply < 0
  * Your error processing here
ENDIF

SELECT csrResult
BROWSE    && view the result returned by the stored procedure

This is only meant as a rough guide, to give you the general idea.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

A vfp app make the call to sql server,
sql server will make a call to its local vfp (on same box) and get result
sql server will return result back to original vfp app.

If this is being run from a VFP app, why do you want to run in such an un-necessarily complex manner?

Just run the secondary VFP utility directly from the original VFP app and send the result to the SQL Server so that it can utilize it in its own Query and/or Stored Procedure using SQL pass-through - returning its result to the original VFP app as a Cursor.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Why do you need a server side VFP process, if you want data access you typically would use SQL Server only. Are you talking about linked server? To dbfs? Linked server is unideal, as you can't simply [tt]Select * from linkedserver.Table[/tt], you rather need a syntax [tt]Select * from openquery('linkedserver','Select * from table')[/tt], and that's unfortunate, as it's obviously changes again, when data is migrated to MSSQL, so you have no easy and seamless transition from DBF based to MSSQL Server based application. A syntax like a normal query as in [tt]Select * from linkedserver.database.schema.table[/tt] without the need of openquery works, but AFAIK only when the linked server is a MSSQL database. One reason is, VFP databases lack the concept of schemas (dbo for example as database owner schema). I'm open to be teached otherwise.

If you want Azure, then the Azure File Storage system would be giving you a file share in the Azure cloud. That could work without any change as a LAN file share does and could combine a desktop application with Azure data storage. I never used Azure, though. It's what MS advertises, a SMB protocol based file share in the cloud.

Bye, Olaf.

 

[stanlyn]

Hi Mike and JRB,

Are you asking how to call the stored procedure from within a VFP application? Or are you asking how the stored procedure should make the call to VFP?

Yes, both. You answered the 1st part.

If this is being run from a VFP app, why do you want to run in such an un-necessarily complex manner?

Just run the secondary VFP utility directly from the original VFP app and send the result to the SQL Server so that it can utilize it in its own Query and/or Stored Procedure using SQL pass-through - returning its result to the original VFP app as a Cursor.

Because the secondary VFP is on the same box that is hosting Microsoft SQL Server in a different geographical location from the primary VFP app.

Please bear with me here as I've never treaded these waters before. I use SQL pass-thru CRUD commands from VFP a lot. I have never used a sql stored procedure before other that simple stuff like count.

So, Mike... Yes, Microsoft SQL Server remotely from primary vfp perspective. You are on the right track. Next step, what would the SQL stored procedure look like (that is being run by the remote SQL Server that connects locally to VFP to resolve a value. Then that value needs to be returned back to the original primary vfp app in a cursor.

The secondary VFP engine is on the same box as SQL Server which are remote to the primary calling VFP app.

JR, if there is an easier less-complex way, than great, I'll try whatever you suggest.

Thanks,
Stanley

 

[Olaf Doschke]

You have a primary location with a VFP application using MSSQL Server as backend? You want to use that same application on a second site?

Then one easy way is terminal server. You need a decent server able to host the needed number of user sessions (dimension RAM and CPU cores with number of users) and cals, but then don't need any secondary SQL Server and the vfp app also is not installed in the second site at all, the users there only get a rdp file.

The other way would be setting up both sites similarly and let databases sync each other using MSSQL replication.

In both scenarios there is no complicated path through a remote MSSQL using a local VFP app, this is not how you add sites to use the same vfp application.

Replication has a big advantage in my eyes, as you can keep syncing at minimum, not every site needs all data. You have stem data needed everywhere, but local store inventory is local only, for example.

Most companies rather choose the terminal server way, though its operation is more expensive, it works right away, the already built up infrastructure is continued to be used. Also setting up replication is not done with a few clicks. The operation costs also depend on the MSSQL server license a bit and of course number of users plpay a role in both MSSQL and terminal server cals, the latter are needed anyway also if the additional number of users use the same server.

One unfortunate thing is how users don't end remote sessions correctly and keep connections open, there is some administrtation needed about that, also updating an app eg with the loader strategy doesn't work on a terminal server always in use, many users share the same exe and thus just one active user hinders an update to replace the running exe, again administration is needed to handle this.

Two two solutions work on making a cut in the tiers of an application, the terminal server makes the cut even outside of the whole application using secondary sites clients as thin clients, as terminals only, keyboard, mouse and monitor are used, they are connected to the terminal server, which runs the app, reacts to input and serves the screen output. Hard things are USB sticks used for data exports, but a rdp connection can (just like remote desktop) share a local USB port as server side device. There is a time lag and especially saving to USB stick needs much more time than locally, another disadvantage of terminal.

The other cut again is outside of the whole application at the end of the database, each client works as on the original site, the only additional costs and line between sites is replicating data, there obviously is no lag in useage, you can use any local devices, eg also serail port devices, etc. but data entered in site 1 needs a lag caused by the data replication/syncing.

In terms of scalability, just think how probable it sounds 10000 users might work on terminals connected to a headquarter site runnning the app on a single server. You say "that's how web applications work", why not? Well, a web application makes much more use of the client side resources, the browser runs the gui, only some user actions (submitting form data for example) make a sewrver side action/request needing a response, you don't run 10000 user session each running a VFP runtime etc. needing exorbitantly more RAM,CPU, etc. So again a point for using replication as means of reusing the same app with globally shared data.

What you seem to want to do is making the cut in the middle of an application, in its data access, that's the hardest way to do this. It's quite simple to make a remote SQL Server connection, but as you want to use a VFP module via SQL Server, your app does not seem to use SQL Server as a backend, you just use it as means of making a connection (?) If that would be the case, if you rather not want to connect to data but to functionality, you better make that connection through IIS (webserver) or more direct via Winsock, not through an SQL Server.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

You have a primary location with a VFP application using MSSQL Server as backend?

Yes

You want to use that same application on a second site?

No

but as you want to use a VFP module via SQL Server,

YES, SQL needs to run a VFP script local to the sql server and then sql server will pass the results back to the original vfp app which is remote.

your app does not seem to use SQL Server as a backend,

IT DOES

you just use it as means of making a connection,

Yes and No, it is used for the connection and then running the proc.

if you rather not want to connect to data but to functionality, you better make that connection through IIS (webserver) or more direct via Winsock, not through an SQL Server.

I'll need both functionality and data (dbf) access for this to be a viable option.

Not sure about any of this, hence the discussion.

Look at it this way,
VFP1 makes a call to a remote Sql server stored proc by passing some parms that SQL Server will need to pass thru to VFP2. VFP2 is local to SQL Server. VFP2 will need to do its thing and return a result set back to SQL Server, and SQL Server will need to pass it on back to VFP1.

Also Olaf, Terminal Services is not a preferred way. I do have a fair bit of experience using it and making apps that run on it. I'm actually removing all our RDP code from those apps.

Thanks,
Stanley

 

[atlopes]

Disclaimer: I'm passing this just as a reference, I don't have any experience with this, do not know of any obstacles you may encounter, and currently with too many things in hand to give it myself a try. But will remain curious on any of your findings.

MSSQL has an interface to access COM servers:

https://msdn.microsoft.com/en-us/library/ms190501.aspx.

If you create a VFP COM server, you can run VFP code from MSSQL through an exposed OLE object.

 

[stanlyn]

Hi atlopes,

Thanks for the article, will dive deeper...

Thanks,
Stanley

 

[Olaf Doschke]

No, there is DCOM, remote (distributed) COM server usage for your needs. It's needing a configuration allowing remote access, RPC (remote procedure call) but no SQL Server.

So you do just use MSSQL as means of making a connection, and that's wrong. The MSSQL stored procedure you want to use only calls into VFP, doesn't it? You don't use any MSSQL database/table in this aspect, do you? Then why go through SQL Server at all, simply because you know how to make that connection?

The usage of DCOM is explained in the VFP help, onme methodology is using it as ISAPI dll via IIS, rather than working through MSSQL Server.

The foxpro example you find when searching the help for DCOM is FowWeb and FoxIS, a simple ISAPI component for IIS, also google IID DCOM and you find a relation of these two technologies, too. Overall there is no need to go through MSSQL Server, you either go through IIS Webserver or even lower level go through a general DCOM Service to instanciate a DCOM Server instance remotely.

Bye, Olaf.

 

[Olaf Doschke]

You might orientate on the last part of

http://www.dfpug.de/loseblattsammlung/migration/whitepapers/advauto.htm,

starting with Remote Automation (about last 20% of the whitepaper)

Bye, Olaf.

 

[JRB-Bldr]

SQL needs to run a VFP script local to the sql server and then sql server will pass the results back to the original vfp app which is remote.

Why does the Secondary VFP application (a.k.a. VFP2 "script") need to reside on the SQL Server's server?

As long as the Primary ("Original" VFP1) VFP application can access the SQL Server for 'results', then a Secondary VFP Application (VFP2) can do so as well.
And if, for some unclear reason, there should be VFP Data Tables on the SQL Server's server, those can also be readily accessed.

So I am still confused as to WHY the VFP applications need to be resident in 2 separate places.
And does it even need to be in 2 separate applications or can it be a single one.

Personally I'd have VFP1 run a process within remote SQL Server to get anything it might initially need and have it passed back to it as a Cursor.
Use the initial cursor results to run the VFP2 - most likely as a Procedure or Function within VFP1 (not as a separate application)
Finally run another process within the SQL Server using the results from the VFP2 code and get the results back as a Cursor.

If VFP2 was some 'legacy' code that you felt you needed to maintain, just duplicate its code into VFP1 and then 'retire' VFP2.

Unless there is a GOOD reason for it, this seems like an notably unnecessarily complex configuration.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

The nature of the stored procedure is stil unclear, I assume it's used to call into the VFP2 module only, so the SQL detour is unneccessary.

I can totally understand needing some component, be it MSSQL or VFP or anything as a central server module. Simnple reason is connecting to it from many places, sites, not necessarily globally, but already two clients are a normal configuration for client/server, so I don't see this as complex configuartion because of the server involved. I just don't see why you need two server side components. When you finally need data back, the sql server can do it alone, even if data is in dbf files, I meantioned linked server as possibility of MSSQL to query DBF data. The reasons for the data being split in DBF and SQL Server are manifold, eg legacy application needs dbfs on that same server, even if not directly involved with the client/server situation.

But indeed, much would be clearer, if you'd talk about the real world problem and not make it such an abstract unusual situation. If I take VFP1 (client), VFP2 (server) and MSSQL (same server) as granted modules, my initial thought would be VFP1 connecting to VFP2 via interprocess communication (eg RPC, Winsock, ...) and if MSSQL data or stored proc is needed (eg third party product working on MSSQL only) , then act on this from VFP2. Using MSSQL to run VFP2 is turning things upside down. VFP easily uses MSSQL, but MSSQL only easily uses data and linked servers (data sources via ODBC or OLEDB provider) or flat files. Less obvious is the use of MSSQL to automate COM components. Yes, as atlopes shows there are sp_OA (OA as in OLE Automation) stored procs, but it's not the norm an SQL Server uses COM components, it's an exception, an interoperability only implemented for seldom case, not the core intention and use of MSSQL.

Bye, Olaf.

 

[stanlyn]

Hi JRB-bldr,

So I am still confused as to WHY the VFP applications need to be resident in 2 separate places.

Because VFP1 has the question and VFP2 has the answer. VFP1 is at the client location under their control. VFP2 is under our control and contains answers based on questions asked from VFP1. VFP2 ideally can be on the same SQL box or on Sql's lan, it wouldn't matter. Now if sql could run a vfp script natively, then there would be no need for vfp2. This answer has to be formulated or a record found (based on the parm passed from VFP1) that returns the answer back to VFP1 so VFP1 can use it.

I know that I can create a connection to VFP2 and query it first before going to sql. My question is about how to do it the more complicated way, via sql with a single connection.

Hope that helps,
Stanley

 

[stanlyn]

Hi JRB-bldr,

Personally I'd have VFP1 run a process within remote SQL Server to get anything it might initially need and have it passed back to it as a Cursor.
Use the initial cursor results to run the VFP2 - most likely as a Procedure or Function within VFP1 (not as a separate application)
Finally run another process within the SQL Server using the results from the VFP2 code and get the results back as a Cursor.

Your suggestion got me thinking about creating the answer table as a sql table on the same server, and query that first, return it to vfp1, process it at vfp1, and use its results to do its final sql query. That will work.

And if any remote queries to vfp is needed, just make a specific connect to it.

Thanks, Stanley

 

[JRB-Bldr]

got me thinking about creating the answer table as a sql table on the same server, and query that first, return it to vfp1, process it at vfp1, and use its results to do its final sql query. That will work.

That would be a MUCH better approach.
Far less complicated and more direct.

Good Luck,
JRB-Bldr