I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

September Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardParadox to/from Other Data Sources Topic   Print This     

PDOX11 over MSSQL (ODBC) - Schema in Table Names

PDOX11 over MSSQL (ODBC) - Schema in Table Names in Paradox to/from Other Data Sources topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: PDOX11 over   PDOX11 over MSSQL   (ODBC) Schema   Table Names  
PS_291
-- --

Might anyone here know a way to configure ODBC/SQLEXPRESS under the BDE to return table names without a schema prefix (i.e., tablename rather than dbo.tablename)? Should I be looking at a different SQL DB? 

Ideally, I’d like to point the alias that currently references the main paradox database to one referencing tables ported to sql and look at what needs fixing from there. 

Thanks to a tool created and shared on codeproject by one of the forum members (thanks Igor!), database conversion to SQLEXPRESS went surprisingly easily, with only a few minor field type changes needed across hundreds of tables. I’m connecting to the SQL db fine from paradox via odbc. That said, I’ll consider a different back end (Firebird? PostgreSQL?) if it means the Paradox app will require fewer changes; I’m dealing with many forms and lots of code and porting the back end is a step toward a full conversion away from Paradox.

Thanks!

John

 Posted 8 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17393, 10 replies
Thread Started 1/22/2019 5:37:28 AM
View Counter=358
Last Reply Posted 3/7/2019 12:51:57 PM)
Location=-- -- 
Joined=25 months ago   MB Posts=13  
Moderator
Steven.G
Myrtle Beach, SC USA

sorry, no clue

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 8 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17394 (Level 2) and Parent is 17393
Reply Posted 1/22/2019 8:27:34 AM
Location=Myrtle Beach, SC USA 
Joined=36 months ago   MB Posts=302  
PS_291
-- --

Thank you for the feedback, Steven. If you haven't found a way around this sort of thing with as many conversions as I suspect you have under your belt, I'm guessing there's a good fighting chance there's no way around the table naming issue with MS SQL. I'll try to get tests going with a few other back ends and will update.

Cheers,

John

 Posted 8 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17395 (Level 1.1)  Reply to 17394 and Parent is 17393
Thread Started 1/22/2019 9:40:56 AM
Location=-- -- 
Joined=25 months ago   MB Posts=13  
rum
Frankfort, KY USA

John,  is this the table name you see in a query,  or when you try to connect with ODBC?

 Posted 8 months ago
Comment Quote
About rum -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Member pending approval.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17396 (Level 3) and Parent is 17393
Reply Posted 1/22/2019 9:50:21 AM
Location=Frankfort, KY USA 
Joined=27 months ago   MB Posts=88  
PS_291
-- --

Hi Jeff,

This is when connecting via ODBC. I believe that in queries MS SQL will look up the applicable schema (with a small performance penalty) if it's not prepended to the table name.

Best,

John

 Posted 8 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17397 (Level 1.1)  Reply to 17396 and Parent is 17393
Thread Started 1/22/2019 9:54:19 AM
Location=-- -- 
Joined=25 months ago   MB Posts=13  
Moderator
Steven.G
Myrtle Beach, SC USA

no, John.. it just means I don't know anything about configuring SqlExpress.. I'm not the guy who converts the old paradox apps, I'm the guy who maintains the old paradox apps that haven't been converted :-)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 8 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17398 (Level 4) and Parent is 17393
Reply Posted 1/22/2019 10:18:48 AM
Location=Myrtle Beach, SC USA 
Joined=36 months ago   MB Posts=302  
PS_291
-- --

Ah- okay! Thank you for the clarification, Steven- that’s good to know. I’ll likely be maintaining Paradox deployments for years yet moving forward (particularly with the v11 migration close to complete)- it’s been great to find that there’s still a community around.

 I’ll be sure to post an update if I get the table name issue worked out.

Best,

John

 Posted 8 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17399 (Level 1.1)  Reply to 17398 and Parent is 17393
Thread Started 1/22/2019 11:40:05 AM
Location=-- -- 
Joined=25 months ago   MB Posts=13  
PS_291
-- --

... a brief update. I‘ve been focusing more on getting things working against a PostgreSQL back end via ODBC these days, as that is looking a bit less finicky than Microsoft SQL Server 2017 from the Paradox 11 side, but I took a few minutes to revisit the MSSQL issues yesterday.

The table naming problem seems to go away if the user database login is explicitly set to use a default schema (e.g., dbo), say via SQL Server Management Studio.

Another problem I‘m seeing with MSSQL via ODBC is that index metadata is not being pulled reliably from the Paradox 11 side. The indexes are listed under View | Table Structure... if the table is opened using File | Open | Table... However, there is an error when opening a form with a linked data model or when trying to use an index via a TCursor. Interestingly, neither of the following works, but the first fails with an error indicating that the secondary index doesn‘t exist, while the second version fails silently (the index structure table is created, but contains no entries even though the SCHEDULE table in MSSQL has a primary key and a secondary index named ‘ScheduledDate‘).

In both cases, the myDB alias exists and a connection has already been established in the default session. The tc.open fails with an error related to a nonexistent secondary here:

method run(var eventInfo Event)
var
tc TCursor
endVar

tc.open(":myDB:SCHEDULE",db,"ScheduledDate")
tc.enumIndexStruct(":PRIV:MSSQLIndexStruct.DB")

endMethod

... but with a different tc.open syntax there is no error and an (empty) index struct table is created:

method run(var eventInfo Event)
var
tc TCursor
db Database
endVar

db.open("myDB")
tc.open("SCHEDULE",db,"ScheduledDate")
tc.enumIndexStruct(":PRIV:MSSQLIndexStruct.DB")

endMethod


I‘ll update if I manage to get indexes working with Paradox 11 -> ODBC -> MSSQL, but welcome any input from others who may have gotten this working. For reference, I‘m testing with:


ODBC Driver 13 for SQL Server
SQL Server Express 2017

If anyone has had better luck with earlier (but hopefully relatively recent) versions of SQL Server and ODBC drivers, I‘d love to hear about it.

Best,
John

 Posted 6 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17439 (Level 5) and Parent is 17393
Reply Posted 3/7/2019 8:42:41 AM
Location=-- -- 
Joined=25 months ago   MB Posts=13  
Moderator
Steven.G
Myrtle Beach, SC USA

that's for sharing.. but, to be clear..

you're trying to use Paradox to connect to other data sources, correct?

using other sources to connect to Paradox tables should be a no-brainer, if those sources work correctly.. Paradox tables themselves have not changed structurally since the mid-90s

however, using Paradox itself to connect to other sources, with Paradox assuming the other sources will respond exactly as they did in 2002, could very easily have bumps and quirks, as you're finding out.. and those quirks could also be specific to the exact combination of other platform / other source

and, unfortunately, usually very few other folks, if any, exploring the same combinations today

beyond this point be dragons :-)

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 6 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17440 (Level 6) and Parent is 17393
Reply Posted 3/7/2019 9:25:06 AM
Location=Myrtle Beach, SC USA 
Joined=36 months ago   MB Posts=302  
PS_291
-- --

Hi Steven,

Thank you for your feedback- it's always much-appreciated. I am indeed connecting from a biggish (400+  forms, dozens of sometimes-large libraries, and a few hundred tables) app I ported from Paradox 7 to Paradox 11 to a new back end as a step toward a total rewrite. Dragons indeed!

I've actually been seeing pretty good results so far with PostgreSQL 10 via ODBC, though there are definitely some db-specific quirks that require refactoring (e.g., table names are case-sensitive when the PostgreSQL runs on a linux platform, setGenFilter doesn't work on date fields, switchIndex() doesn't work, but switchIndex("table_pkey") works fine, some tcursor locates that should fire SQL with 'WHERE' clauses instead pull all records to the client side- even in "SERVER" mode, requiring a rewrite using SQL queries here and there for performance reasons, some sort order differences are not addressed by seemingly-reasonable sort order choices, etc.).

The real surprise has been just how much has worked without major re-factoring against PostgreSQL via ODBC, though, but that said it's been an interesting opportunity to practice automating changes to ObjectPAL code across a few hundred forms and libraries at a time- methodSet is my new best friend (right alongside our old friend enumSourceToFile)! ... still ready for the next dragon to rear its head, but for the PGSQL back end at least, it's looking like the transition from bug hunt to focusing on SQL-land performance improvements (the speed of Paradox tables can spoil coders a bit- even when dealing with pretty big tables!) is quite close.

Thanks again for reaching out! Should there still be one or two people out there facing a similar port, I'll post an update with a few lessons learned/tips once I feel good enough about progress to stick a fork in my own migration (i.e., push a beta to our users).

Best,

John

 Posted 6 months ago (Thread Starter)
Comment Quote
About PS_291 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #17441 (Level 1.1)  Reply to 17440 and Parent is 17393
Thread Started 3/7/2019 11:20:55 AM
Location=-- -- 
Joined=25 months ago   MB Posts=13  
Most Recent Post
Moderator
Steven.G
Myrtle Beach, SC USA

> The real surprise has been just how much has worked without major re-factoring <

that is VERY cool

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 6 months ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #17442 (Level 7) and Parent is 17393
Reply Posted 3/7/2019 12:51:14 PM
Location=Myrtle Beach, SC USA 
Joined=36 months ago   MB Posts=302  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1151A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #17393 Counter
358
Since 1/22/2019

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech