Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
I use Visual MaxFrame Professional so the details are hidden from me and I don't really care what technology is used behind the scenes. < smile >
I actually think that Drew coded it so that it works through remote views in the background.
If I were to start doing my own code/framework now in VFP I would probably use CursorAdapters but I have never actually used them for more than playing with to learn about them. I believe they give you enough flexibility while hiding some of the technical details. If you want absolute control, then go with SPT.
Frank.
Frank Cazabon
On 20/08/2019 05:39 AM, Peter Cushing wrote:
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
Considering they are flipping over SQL Server I would learn to USE SQL SERVER correctly on the front end from VFP. You create the sprocs that do all of your CRUD operations. Populate them from the fox world and as VFP is replaced, the newer interfaces will use the same sprocs you laid down today whenever they are activated.
On Tue, Aug 20, 2019 at 7:24 AM Frank Cazabon frank.cazabon@gmail.com wrote:
I use Visual MaxFrame Professional so the details are hidden from me and I don't really care what technology is used behind the scenes. < smile >
I actually think that Drew coded it so that it works through remote views in the background.
If I were to start doing my own code/framework now in VFP I would probably use CursorAdapters but I have never actually used them for more than playing with to learn about them. I believe they give you enough flexibility while hiding some of the technical details. If you want absolute control, then go with SPT.
Frank.
Frank Cazabon
On 20/08/2019 05:39 AM, Peter Cushing wrote:
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
[excessive quoting removed by server]
On 8/20/2019 9:42 AM, Stephen Russell wrote:
Considering they are flipping over SQL Server I would learn to USE SQL SERVER correctly on the front end from VFP. You create the sprocs that do all of your CRUD operations. Populate them from the fox world and as VFP is replaced, the newer interfaces will use the same sprocs you laid down today whenever they are activated.
Or use CursorAdapters and make the app a bit more able for change in case they choose to NOT use SQL Server at some point in the future.
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Yes, agreed using stored procedures is a good way, however I believe you still need SPT or CursorAdapters to access those stored procedures, so they are not necessarily mutually exclusive.
Frank.
Frank Cazabon
On 20/08/2019 09:42 AM, Stephen Russell wrote:
Considering they are flipping over SQL Server I would learn to USE SQL SERVER correctly on the front end from VFP. You create the sprocs that do all of your CRUD operations. Populate them from the fox world and as VFP is replaced, the newer interfaces will use the same sprocs you laid down today whenever they are activated.
On Tue, Aug 20, 2019 at 7:24 AM Frank Cazabon frank.cazabon@gmail.com wrote:
I use Visual MaxFrame Professional so the details are hidden from me and I don't really care what technology is used behind the scenes. < smile >
I actually think that Drew coded it so that it works through remote views in the background.
If I were to start doing my own code/framework now in VFP I would probably use CursorAdapters but I have never actually used them for more than playing with to learn about them. I believe they give you enough flexibility while hiding some of the technical details. If you want absolute control, then go with SPT.
Frank.
Frank Cazabon
On 20/08/2019 05:39 AM, Peter Cushing wrote:
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
[excessive quoting removed by server]
Peter,
Personally, I like SPT because I can build valid and safe pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
Cursoradapter eases the transition from DBC to SQL: you can tailor the SQL command according to the backend, recycle your code, test against either backend seamlessly.
Thierry Nivelet http://foxincloud.com/ Give your VFP app a second life in your cloud
Le 20 août 2019 à 16:11, Paul H. Tarver paul@tpcqpc.com a écrit :
Peter,
Personally, I like SPT because I can build valid and safe pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
Peter Cushing IT Department WHISPERING SMITH
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
I'm with you mostly it would seem, Paul. I wrote my own SPT framework years ago after watching Bob Lee demonstrate his VFP app in Wisconsin accessing a MySQL database over the internet in New York back at WhilFest in 2002 or 2003. CursorAdapters sounded great and I'm told they work great today, but initially, I steered clear of them after initial issues in VFP8 after reading Ed Leafe's and others testing results. I know others who use CAs and they work great for them, making it super easy to change backends (VFP, SQL Server, Firebird, etc.) without much code change at all apparently.
On 8/20/2019 10:11 AM, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
The initial request said that there was a plan already defined.
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end.
I would only work in promoting future ways of doing things in this case. Resistance to the change could lead to termination from the team in all honesty as they make progress. Finding replacement VFP work today is not as easy as it used to be.
On Tue, Aug 20, 2019 at 9:55 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
I'm with you mostly it would seem, Paul. I wrote my own SPT framework years ago after watching Bob Lee demonstrate his VFP app in Wisconsin accessing a MySQL database over the internet in New York back at WhilFest in 2002 or 2003. CursorAdapters sounded great and I'm told they work great today, but initially, I steered clear of them after initial issues in VFP8 after reading Ed Leafe's and others testing results. I know others who use CAs and they work great for them, making it super easy to change backends (VFP, SQL Server, Firebird, etc.) without much code change at all apparently.
On 8/20/2019 10:11 AM, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe pass-through
commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor
adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files
just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL,
Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with
the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Peter Cushing
Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On 8/20/2019 11:15 AM, Stephen Russell wrote:
The initial request said that there was a plan already defined.
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end.
I would only work in promoting future ways of doing things in this case. Resistance to the change could lead to termination from the team in all honesty as they make progress. Finding replacement VFP work today is not as easy as it used to be.
Well certainly do whatever to not get fired, of course! But my point was that the CAs appear to be much more flexible if you're going to be doing any VFP work with an eye on the future with respect to using non-VFP databases in the future. That's all.
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
I use SQLSTRINGCONNECT usually because those apps build the connection string manually, but I do have another app that uses SQLCONNECT using a defined DSN.
On 8/20/2019 12:26 PM, Peter Cushing wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for many years, we are upgrading it (in stages) to a visual studio app with SQL server back end. I have read many people here talking about using SQL as a back end. At the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through the archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
Make sure you are using ODBC Data Sources (32-bit) and in the Drivers tab you'll see which drivers you have for SQL. I have ODBC Driver 17 for SQL Server, SQL Server and SQL Server Native Client 11.0 I prefer to use a connection string rather than DSN so that I don't have to set up a DSN on each client.
m.lcDriver = "SQL Server" m.lcSQLServer = "SQL2016DEV" m.lcDatabase = "AdventureWorks"
m.lcConnStr = TextMerge("Driver={<<m.lcDriver>>};Server=<<lcSQLServer>>;Database=<<m.lcDatabase>>;Trusted_Connection=Yes;")
SQLSetProp(0, "ConnectTimeOut", 3) && seconds to timeout SQLSetProp(0, "DispLogin", 3) && 3 = do not display login
m.lhSql = SQLStringConnect(m.lcConnStr) If m.lhSql < 0 MessageBox(Message()) Return EndIf
If SQLExec(m.lhSql, "select * from Sales.Customer", "customers") < 0 MessageBox(Message()) Else Browse Normal EndIf
If m.lhSql > 0 SQLDisconnect(m.lhSql) EndIf
Make sure you always check the result of SQLExec or it will just silently fail.
Ricardo
On Tue, 20 Aug 2019 at 17:27, Peter Cushing pcushing@whisperingsmith.com wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
I use SQL native client (which is an option to install when installing SQL server):
https://www.microsoft.com/en-us/download/details.aspx?id=50402
And then I use a connection string like:
ConnectionString=DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
Or:
ConnectionString=DRIVER=SQL Server Native Client 11.0;Trusted_Connection=No;uid=myuserid;PWD=mypassword;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
But you could use a user or system dsn.
On 20 August 2019 12:26:35 GMT-04:00, Peter Cushing pcushing@whisperingsmith.com wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe
pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor
adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit
files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL,
Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you
with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Peter Cushing
Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for
many
years, we are upgrading it (in stages) to a visual studio app with
SQL
server back end. I have read many people here talking about using SQL as a back end.
At
the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through
the
archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
Now got it working with SQLSTRINGCONNECT() thanks. I will have a look at this native client. Does that give you any advantages over the ODBC connection?
Also noticed in an archive post someone using this to check the connection before doing a real query/update.
nResult=SQLEXEC(datahandle,'SELECT @@identity') If nResult is 0 then assume a problem and disconnect, throw away the old handle, and then reconnect.
How necessary is that check? Should I use it before every call?
Thanks,
Peter Cushing IT Department WHISPERING SMITH
On 21/08/2019 11:34, Frank Cazabon wrote:
I use SQL native client (which is an option to install when installing SQL server):
https://www.microsoft.com/en-us/download/details.aspx?id=50402
And then I use a connection string like:
ConnectionString=DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
Or:
ConnectionString=DRIVER=SQL Server Native Client 11.0;Trusted_Connection=No;uid=myuserid;PWD=mypassword;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
But you could use a user or system dsn.
On 20 August 2019 12:26:35 GMT-04:00, Peter Cushing pcushing@whisperingsmith.com wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe
pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor
adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit
files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL,
Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you
with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Peter Cushing
Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for
many
years, we are upgrading it (in stages) to a visual studio app with
SQL
server back end. I have read many people here talking about using SQL as a back end.
At
the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through
the
archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
An ODBC connection has to be created on every workstation, as well as installing a SQL Client as well. I'd avoid the ODBC route and go straight SQL Client that does have to be installed on each machine. Pick a higher Client version number such as 10 or 11 today.
connectionString="Data Source=SERVERNAME\INSTANCE; Initial Catalog=DATABASE YOU ARE USING; Persist Security Info=True;User ID=SQLUSER ;Password=THE PW for that USER"
I recommend making an AD user that will be used to work with SQL Server. Change it on some sort of security basis you set up.
You can download SQL Server Management Studio for SQL Express today giving you full access to all wizards in working with SQL Server.
You can also install SSIS & SSAS for pulling data out of FOX data into SQL Tables on a daily basis for your reporting and or Data Warehouse that will probably be set up.
Adding insult to injury, you can install SSRS as well and start to do reporting. It is not the Fox report writer and it will give you some heartache getting good with it but over the long haul, it will be worthwhile.
The last point to make is that you can add in Power BI and start to learn how to make dashboards that is based off of your starting DW cube(s). We use the cube concept for every business center at our company. Purchasing has it's cube as well as Sales. Accounting gets one too. Planning loves theirs to sum gross volumes of resin across all orders and break that out to either weekly, monthly or quarterly totals.
On Wed, Aug 21, 2019 at 7:08 AM Peter Cushing pcushing@whisperingsmith.com wrote:
Now got it working with SQLSTRINGCONNECT() thanks. I will have a look at this native client. Does that give you any advantages over the ODBC connection?
Also noticed in an archive post someone using this to check the connection before doing a real query/update.
nResult=SQLEXEC(datahandle,'SELECT @@identity') If nResult is 0 then assume a problem and disconnect, throw away the old handle, and then reconnect.
How necessary is that check? Should I use it before every call?
Thanks,
Peter Cushing IT Department WHISPERING SMITH
On 21/08/2019 11:34, Frank Cazabon wrote:
I use SQL native client (which is an option to install when installing
SQL server):
https://www.microsoft.com/en-us/download/details.aspx?id=50402
And then I use a connection string like:
ConnectionString=DRIVER=SQL Server Native Client
11.0;Trusted_Connection=Yes;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
Or:
ConnectionString=DRIVER=SQL Server Native Client
11.0;Trusted_Connection=No;uid=myuserid;PWD=mypassword;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
But you could use a user or system dsn.
On 20 August 2019 12:26:35 GMT-04:00, Peter Cushing <
pcushing@whisperingsmith.com> wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe
pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor
adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit
files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL,
Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you
with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Peter Cushing
Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for
many
years, we are upgrading it (in stages) to a visual studio app with
SQL
server back end. I have read many people here talking about using SQL as a back end.
At
the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through
the
archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
Wow. Lots of stuff to check out there. Will definitely be checking out the SQL server stuff even though I'm not writing the new software. I could well be writing some reports though.
Thanks,
Peter Cushing IT Department WHISPERING SMITH
On 21/08/2019 14:40, Stephen Russell wrote:
An ODBC connection has to be created on every workstation, as well as installing a SQL Client as well. I'd avoid the ODBC route and go straight SQL Client that does have to be installed on each machine. Pick a higher Client version number such as 10 or 11 today.
connectionString="Data Source=SERVERNAME\INSTANCE; Initial Catalog=DATABASE YOU ARE USING; Persist Security Info=True;User ID=SQLUSER ;Password=THE PW for that USER"
I recommend making an AD user that will be used to work with SQL Server. Change it on some sort of security basis you set up.
You can download SQL Server Management Studio for SQL Express today giving you full access to all wizards in working with SQL Server.
You can also install SSIS & SSAS for pulling data out of FOX data into SQL Tables on a daily basis for your reporting and or Data Warehouse that will probably be set up.
Adding insult to injury, you can install SSRS as well and start to do reporting. It is not the Fox report writer and it will give you some heartache getting good with it but over the long haul, it will be worthwhile.
The last point to make is that you can add in Power BI and start to learn how to make dashboards that is based off of your starting DW cube(s). We use the cube concept for every business center at our company. Purchasing has it's cube as well as Sales. Accounting gets one too. Planning loves theirs to sum gross volumes of resin across all orders and break that out to either weekly, monthly or quarterly totals.
On Wed, Aug 21, 2019 at 7:08 AM Peter Cushing pcushing@whisperingsmith.com wrote:
Now got it working with SQLSTRINGCONNECT() thanks. I will have a look at this native client. Does that give you any advantages over the ODBC connection?
Also noticed in an archive post someone using this to check the connection before doing a real query/update.
nResult=SQLEXEC(datahandle,'SELECT @@identity') If nResult is 0 then assume a problem and disconnect, throw away the old handle, and then reconnect.
How necessary is that check? Should I use it before every call?
Thanks,
Peter Cushing IT Department WHISPERING SMITH
On 21/08/2019 11:34, Frank Cazabon wrote:
I use SQL native client (which is an option to install when installing
SQL server):
https://www.microsoft.com/en-us/download/details.aspx?id=50402
And then I use a connection string like:
ConnectionString=DRIVER=SQL Server Native Client
11.0;Trusted_Connection=Yes;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
Or:
ConnectionString=DRIVER=SQL Server Native Client
11.0;Trusted_Connection=No;uid=myuserid;PWD=mypassword;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp;
But you could use a user or system dsn.
On 20 August 2019 12:26:35 GMT-04:00, Peter Cushing <
pcushing@whisperingsmith.com> wrote:
Hi,
Thanks to everyone that replied. I don't have to worry about getting fired, so all good there. SPT seems the easiest to begin with, but not got it working yet. I have created an ODBC link called X2live. I set it up as a system DSN as I tried a user DSN first and that didn't work. Just wondering what version of the ODBC driver are you using. Mine is: Microsoft SQL Server ODBC Driver Version 06.01.7601 Did a few quick searches and can't seem to establish if this is a 32 or 64 bit driver.
When I click test data source it comes back with Tests completed successfully but if I try ?sqlconnect('x2live') && returns -1
I have tried putting in the username/password and didn't make any difference.
What actual commands do you use to connect?
TIA
Peter Cushing IT Department WHISPERING SMITH
On 20/08/2019 15:11, Paul H. Tarver wrote:
Peter,
Personally, I like SPT because I can build valid and safe
pass-through commands in my code based on user selections, generate the string, hand it off to the server with an ASYNCH connection and easily provide users with progress feedback while data is returned.
For some reason, I've never been able to wrap my head around cursor
adapters completely. Maybe it’s a control issue! :) I think SPT just made more sense to me upfront. Thankfully, Foxpro gives you plenty of different ways to accomplish the same things and you can pick the one that works the way you do! I use it to query data for reporting purposes and it gives me a lot of control over how I construct the query string since I can use all of the string functions within Foxpro to build exactly the query I want based upon users' selections.
Another advantage is I able to dump the query string to my audit
files just before the SQLEXEC() command fires, so if something goes wrong, I can just copy the query to my SQL management tool and see if I can manually figure out what is going on.
So far, I've used SPT successfully to backend Foxpro with SQL, MySQL,
Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to customize for the database and the rest of my code remains the same.
It works for me, but I'm sure, someone else here will provide you
with the benefits of cursor adapters.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Peter Cushing
Sent: Tuesday, August 20, 2019 4:40 AM To: profoxtech@leafe.com Subject: SQL server connection
Hi,
After running my app (VFP 9) on our local network (with a DBC) for
many
years, we are upgrading it (in stages) to a visual studio app with
SQL
server back end. I have read many people here talking about using SQL as a back end.
At
the beginning I will just be accessing the SQL data for reporting purposes but may need to write to it at some point. Looking through
the
archives some people mentioned SPT and others cursor adapters. Just wondering what people recommend to connect, so I can avoid the usual gotchas.
Thanks,
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
[excessive quoting removed by server]
-
Frank Cazabon -
MB Software Solutions, LLC -
Paul H. Tarver -
Peter Cushing -
Ricardo Molina -
Stephen Russell -
Thierry Nivelet