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

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

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardParadox Forms Topic   Print This     

using alias to oracle, datamodel gives error

using alias to oracle, datamodel gives error in Paradox Forms topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: datamodel   using alias   using alias oracle   datamodel gives  
lja
Ballerup, -- DK

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. index not valid

second page

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:

no keyno key page 2

: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?

 Posted 11 years ago (Thread Starter)
Comment Quote
About lja -Collapse +Expand
Visit Profile
Approved member.
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 #13110, 10 replies
Thread Started 11/12/2008 3:10:44 PM
View Counter=6076
Last Reply Posted 1/24/2019 2:43:22 PM)
Location=Ballerup, -- DK 
Joined=11 years ago   MB Posts=6  
Moderator
Mike Prestwood
Prestwood IT
Prestwood IT office in Citrus Heights, CA

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.

--
Mike Prestwood
Prestwood IT Solutions

 Posted 11 years ago
Comment Quote
About Mike Prestwood -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Mike Prestwood

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Web Presence
Facebook, Prestwood IT Facebook page -- fan page. (Visit Me)
Twitter, Follow Prestwood IT on Twitter. (Visit Me)
LinkedIn, Prestwood IT company page on LinkedIn. (Visit Me)
YouTube, Prestwood IT YouTube Channel (Visit My Channel)
Website, My drum website where I sell my drum books. (http://www.play-drums.com)

Post ID #13111 (Level 1.1)  Reply to 13110
Thread Started 11/12/2008 3:35:31 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=19 years ago   MB Posts=1410   KB Posts=1805   KB Comments=75   BLOG, Topics=4  
Moderator
Mike Prestwood
Prestwood IT
Prestwood IT office in Citrus Heights, CA

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

--
Mike Prestwood
Prestwood IT Solutions

 Posted 11 years ago
Comment Quote
About Mike Prestwood -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Mike Prestwood

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Web Presence
Facebook, Prestwood IT Facebook page -- fan page. (Visit Me)
Twitter, Follow Prestwood IT on Twitter. (Visit Me)
LinkedIn, Prestwood IT company page on LinkedIn. (Visit Me)
YouTube, Prestwood IT YouTube Channel (Visit My Channel)
Website, My drum website where I sell my drum books. (http://www.play-drums.com)

Post ID #13112 (Level 1.2)  Reply to 13110
Reply Posted 11/12/2008 3:41:20 PM
Location=Prestwood IT office in Citrus Heights, CA 
Joined=19 years ago   MB Posts=1410   KB Posts=1805   KB Comments=75   BLOG, Topics=4  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

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

 Posted 11 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #13117 (Level 1.3)  Reply to 13110
Reply Posted 11/13/2008 10:54:59 AM
Location=Home office in Fresno, CA. 
Joined=17 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

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

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

 Posted 11 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #13119 (Level 1.4)  Reply to 13110
Reply Posted 11/13/2008 2:04:24 PM
Location=Home office in Fresno, CA. 
Joined=17 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
lja
Ballerup, -- DK

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

 Posted 11 years ago (Thread Starter)
Comment Quote
About lja -Collapse +Expand
Visit Profile
Approved member.
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 #13115 (Level 1.5)  Reply to 13110
Reply Posted 11/12/2008 4:07:59 PM
Location=Ballerup, -- DK 
Joined=11 years ago   MB Posts=6  
lja
Ballerup, -- DK

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

 Posted 11 years ago (Thread Starter)
Comment Quote
About lja -Collapse +Expand
Visit Profile
Approved member.
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 #13116 (Level 1.6)  Reply to 13110
Reply Posted 11/13/2008 8:43:45 AM
Location=Ballerup, -- DK 
Joined=11 years ago   MB Posts=6  
lja
Ballerup, -- DK

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

 Posted 11 years ago (Thread Starter)
Comment Quote
About lja -Collapse +Expand
Visit Profile
Approved member.
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 #13118 (Level 1.7)  Reply to 13110
Reply Posted 11/13/2008 10:59:15 AM
Location=Ballerup, -- DK 
Joined=11 years ago   MB Posts=6  
lja
Ballerup, -- DK

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

table infostructure for table spec.db

field table lookup datamodel

table infostructure for table business.db

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

 Posted 11 years ago (Thread Starter)
Comment Quote
About lja -Collapse +Expand
Visit Profile
Approved member.
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 #13120 (Level 1.8)  Reply to 13110
Reply Posted 11/13/2008 2:26:24 PM
Location=Ballerup, -- DK 
Joined=11 years ago   MB Posts=6  
PS_291
-- --

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

 Posted 5 months ago
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 #17405 (Level 2) and Parent is 13110
Reply Posted 1/24/2019 1:13:32 AM
Location=-- -- 
Joined=22 months ago   MB Posts=13  
Most Recent Post
PS_291
-- --

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

 Posted 5 months ago
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 #17406 (Level 3) and Parent is 13110
Reply Posted 1/24/2019 2:33:16 PM
Location=-- -- 
Joined=22 months ago   MB Posts=13  

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 = P1161A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #13110 Counter
6076
Since 11/12/2008
Follow PrestwoodBoards on: 


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