using alias to oracle, datamodel gives error

Hi, new to this forum, I joined 'cause whatever I google about paradox I end up here. I am also new to paradox. I am an oracle consultant. I am currently helping a customer to convert their data to oracle. They have paradox 8 on an XP, and using oracle 10g. They still want to use their paradox forms. Here's my problem: I have converted their data, and I have linked to the oracle database through an alias and odbc. I changed the alias used in the form that pointed to local tables to point to oracle. This works, I get the tables correctly. Going into the form that uses this alias, I can see that it has also converted the datamodel to point to the tables in oracle, this seem to work as well. The problem is when I try to run the form, then I get errors. It says "Index used to join tables is no longer valid" there's a sub page that reads: "Table is not indexed". Not so informative, I wish it would tell me which index it is looking at and which table.

Any ideas what to do here?

Also, going into design allows me to see the datamodel, if I click a linked table and click link, then it gives me these errors:

:TABLES is my alias, and :customer is the oracle table. But what key is it it can't find?

I have both primary keys and foreign keys in place in the oracle database. Any ideas what I can do to identify the problem?

Hi Lars,

First off, welcome!

You mentioned that you have both primary keys and foreign keys setup. In Paradox, it is a common technique to add a specifically named secondary index to a table and then use switchIndex in ObjectPAL to force or guarantee it's usage. This might be the problem. Either way, here's my recommendation...

Rather than sifting through code, I would suggest you view the secondary indexes on each table and make sure they exist in the Oracle database with the exact same name. I realize this may not feel right if you're implementing a naming convention on your Oracle DB, but it would be the quickest way to get this up and running and you can always circle back later.

...hmmm. After another quick read of your post, I have to ask. Are you sure you setup primary keys in Oracle? Sometimes I see developers create indexes but forget to create the primary key.

Also, make sure these primary keys match exactly with the Paradox database. For example, if you're tempted to switch a composite key to an autoinc key with a composite secondary index, don't. Instead use the exact keys as they are in Paradox at least until it's working. Then you can gradually change things to fit your development style (or modern development styles).

You will need to use the infostructure or restructure menu option in Paradox.  This will open a dialog that will allow you to see the primary and secondary indexes.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Yes, very often the indexes are referenced in paradox code. 

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Thank you very much for the replies. Could you guide me to where I can see the index information on the tables? Especially the secondary ones? Is it what I see in the "old", by old I mean using the alias that points local .db files, datamodeling in the form, with the index on the right side?

Regarding naming convention, where do I see the naming of these indexes?

Thanks,

Lars

Let me rephrase my previous post. How do you find the name of the secondary index? I can see if a table has a secondary index, but how do i see the name of the index that paradox uses? That is, paradox is obviously using a name to compare with and since it can't find it in oracle it complains, so how do I find out what to call my indexes in oracle??

Hi, Dan!

Thanks for the reply. Now when looking in the info structure, i see an a secondary index. So i'll have to create this in oracle, do i have to create the index like the name it has in info structure.

i.e. if name in paradox is offnum, should it be named offnum in oracle as well?

lars

Just stumbled upon another thing, table lookups. What are they comparable to in oracle? Or how do I set that up in Oracle, does that work as referential integrity, so I have to create a foreign key??? Could this actually be the cause of index not valid??

Why is it that when looking in info structure no table lookups is available for a table, but looking in the datamodel right clicking on the table, choosing fields and then some of the fields have an arrow that points to a table lookup??? How can you

Is there a way to see if the form is looking for these things??? is there a way to search the form for this??

Lars

I‘m encountering precisely the problem referenced in the original post when connecting from Paradox 11 via ODBC to either a SQLExpress or MySQL back end. In brief, I‘ve tried creating primary key fields on the back end database using various naming schemes and although when I examine any given SQL table schema from Paradox I can see primary and secondary keys listed under the secondary keys tab, the "key" glyph on the field list that normally highlights primary key fields is always blank. I suspect this is either an issue with Paradox not properly leveraging the SQLPrimaryKeys() function that is part of the ODBC standard (which would be very bad) or Paradox‘s requiring very specific naming of the primary key field index (I‘ve tried creating a unique key with the table name as the key name to no avail).

Has anyone reading this ever seen Paradox highlight a field as a primary key under Info Structure when pulling up the structure of a table across an ODBC connection to a SQL (e.g., SQLExpress or MySQL) back end? Hopefully the functionality isn't fundamentally broken.

Cheers,

John

... a brief update for anyone else encountering this issue: with ODBC against MySQL, I was able to address the error by creating the primary keys on the MySQL tables used in the data model of the form using the following DDL:

ALTER TABLE `tablename`

ADD CONSTRAINT `tablename` PRIMARY KEY (`Primary_Key_Field_Name`)

... where tablename is the name of the paradox table that was migrated to the new back end with the name preserved. YMMV (particularly against other back-end DB’s).

Best,

John