Finally, I have something new and maybe interesting to throw at y'all!
Got a potential client I'm doing some prelim work for to determine if we can establish an ODBC connection to a Progress Database. I'm just now trying to install all the ODBC drivers and extras the Progress client requires. However, I wondered if anyone on this group had ever connected Foxpro to Progress and if so, what pitfalls can you help me avoid? I'm told by the client that they use SQL Management Studio and then connect to the Progress database via Linked Databases which forces the use of an ODBC DSN. My goal would be to use this to browse the tables and build my test queries and then use SQL Pass-Through via a DSN-Less ODBC connection to query data in production.
I've accomplished similar processes by connecting Foxpro to SQL, MySQL, PostgreSQL, FirebirdSQL, Oracle, Sybase, SQLite and Access. I've ODBC'd my way into a bunch of databases, but I've never worked with Progress.
Any tips, suggestions or recommendations?
Thanks in advance!
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
UPDATE!
Since I didn't get any responses to my original post, I thought I'd at least provide an update on this project.
At the end of last week, I was able to establish an ODBC DNS-less connection to a self-hosted Progress Database across a VPN from within Foxpro, issue queries and obtain and process the results. Woohoo!
Turns out the biggest difference on Progress will be the syntax of the SQL which seems to be pretty rigid T-SQL. And, the only type of connection I can made to the database is via ODBC. Because the Progress ODBC drivers are 32bit, I was unable to use Microsoft SQL Studio 64bit software to create a linked database connection to query the database, so I downloaded an free-ware tool called ODBC Query Tool (https://sourceforge.net/projects/odbc-query-tool/) which has a 32bit and a 64bit bit version software package designed to connect to any database via ODBC. It provides a browse table feature and a simple pass-through query feature to allow users to build queries. Once I got a VPN connection and could ping the database from an ODBC System DSN, I then was able to issue a simple query through the ODBC Query Tool. Next, I set about putting together the connection string for the DSN-less connection from VFP. The Progress DSN-less connection string is constructed like this:
DRIVER=<ODBC Driver Name>;HOST=<Hostname>;PORT=<Port>;DB=<Database Name>;UID=<Username>;PWD=<Password>;DIL=<DEFAULT ISOLATION LEVEL>
All of the settings are self-explanatory except DIL which supports the has following values: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
However if you want more information you can find it here: https://knowledgebase.progress.com/articles/Article/P97292
If you want to learn more about the architecture of the Progress Database system, you should check out this manual: https://waterfund.go.ke/watersource/Downloads/Progress%20Database%20Administ ration.pdf
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul H. Tarver Sent: Monday, June 10, 2019 4:12 PM To: profoxtech@leafe.com Subject: Using FoxPro & ODBC to connect to Progress Database
Finally, I have something new and maybe interesting to throw at y'all!
Got a potential client I'm doing some prelim work for to determine if we can establish an ODBC connection to a Progress Database. I'm just now trying to install all the ODBC drivers and extras the Progress client requires. However, I wondered if anyone on this group had ever connected Foxpro to Progress and if so, what pitfalls can you help me avoid? I'm told by the client that they use SQL Management Studio and then connect to the Progress database via Linked Databases which forces the use of an ODBC DSN. My goal would be to use this to browse the tables and build my test queries and then use SQL Pass-Through via a DSN-Less ODBC connection to query data in production.
I've accomplished similar processes by connecting Foxpro to SQL, MySQL, PostgreSQL, FirebirdSQL, Oracle, Sybase, SQLite and Access. I've ODBC'd my way into a bunch of databases, but I've never worked with Progress.
Any tips, suggestions or recommendations?
Thanks in advance!
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
I apologize for not thinking of this before, but there is a website called https://www.connectionstrings.com/ that helps assemble the right syntax for ODBC connection strings to different back end databases.
Eric
On Tue, Jun 25, 2019 at 10:18 AM Paul H. Tarver paul@tpcqpc.com wrote:
UPDATE!
Since I didn't get any responses to my original post, I thought I'd at least provide an update on this project.
At the end of last week, I was able to establish an ODBC DNS-less connection to a self-hosted Progress Database across a VPN from within Foxpro, issue queries and obtain and process the results. Woohoo!
Turns out the biggest difference on Progress will be the syntax of the SQL which seems to be pretty rigid T-SQL. And, the only type of connection I can made to the database is via ODBC. Because the Progress ODBC drivers are 32bit, I was unable to use Microsoft SQL Studio 64bit software to create a linked database connection to query the database, so I downloaded an free-ware tool called ODBC Query Tool (https://sourceforge.net/projects/odbc-query-tool/) which has a 32bit and a 64bit bit version software package designed to connect to any database via ODBC. It provides a browse table feature and a simple pass-through query feature to allow users to build queries. Once I got a VPN connection and could ping the database from an ODBC System DSN, I then was able to issue a simple query through the ODBC Query Tool. Next, I set about putting together the connection string for the DSN-less connection from VFP. The Progress DSN-less connection string is constructed like this:
DRIVER=<ODBC Driver Name>;HOST=<Hostname>;PORT=<Port>;DB=<Database Name>;UID=<Username>;PWD=<Password>;DIL=<DEFAULT ISOLATION LEVEL>
All of the settings are self-explanatory except DIL which supports the has following values: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
However if you want more information you can find it here: https://knowledgebase.progress.com/articles/Article/P97292
If you want to learn more about the architecture of the Progress Database system, you should check out this manual:
https://waterfund.go.ke/watersource/Downloads/Progress%20Database%20Administ ration.pdf https://waterfund.go.ke/watersource/Downloads/Progress%20Database%20Administration.pdf
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul H. Tarver Sent: Monday, June 10, 2019 4:12 PM To: profoxtech@leafe.com Subject: Using FoxPro & ODBC to connect to Progress Database
Finally, I have something new and maybe interesting to throw at y'all!
Got a potential client I'm doing some prelim work for to determine if we can establish an ODBC connection to a Progress Database. I'm just now trying to install all the ODBC drivers and extras the Progress client requires. However, I wondered if anyone on this group had ever connected Foxpro to Progress and if so, what pitfalls can you help me avoid? I'm told by the client that they use SQL Management Studio and then connect to the Progress database via Linked Databases which forces the use of an ODBC DSN. My goal would be to use this to browse the tables and build my test queries and then use SQL Pass-Through via a DSN-Less ODBC connection to query data in production.
I've accomplished similar processes by connecting Foxpro to SQL, MySQL, PostgreSQL, FirebirdSQL, Oracle, Sybase, SQLite and Access. I've ODBC'd my way into a bunch of databases, but I've never worked with Progress.
Any tips, suggestions or recommendations?
Thanks in advance!
Paul H. Tarver
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]