PDOX11 over MSSQL (ODBC) - Schema in Table Names

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

sorry, no clue

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

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

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

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

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

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

... 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

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

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

> 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