Hi guys,
i have a problem with External Tables using OpenEdge (11.3). When I try to consume two diferentes tables with a PK i get a error in SQL Statement.
ERROR: Error in query SELECT NAME, ADDRESS, ADDRESS2, BALANCE, CITY, COMMENTS, CONTACT, COUNTRY, CREDITLIMIT, CUSTNUM, DISCOUNT, EMAILADDRESS, FAX, PHONE, POSTALCODE, SALESREP, STATE, TERMS FROM CUSTOMER WHERE null=?: RB_36: Error preparing statement for query SELECT NAME, ADDRESS, ADDRESS2, BALANCE, CITY, COMMENTS, CONTACT, COUNTRY, CREDITLIMIT, CUSTNUM, DISCOUNT, EMAILADDRESS, FAX, PHONE, POSTALCODE, SALESREP, STATE, TERMS FROM CUSTOMER WHERE null=? [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "null=?" (10713)
As you can see i using the sports demo tables. Does anybody have the same problem? Any solution? Thanks!
PS.: I use the Rollbase book and some others resources and do everything right (i guess). Thanks!
Will do :)
Hi victor,
As per the Exception stack trace which you have provided there is a problem with the SQL query. Please adjust the query by modifying SQL queries in
Click on object--> Select Object Definition--> More Actions--> Adjust SQL Queries.
If not could you provide the steps to reproduce it, so that I can verify and get back to you.
Thanks & Regards,
Kiran Avs.
Hi Victor, just wondering if Kiran's suggestion helped?
Hi Jean,
I am having the same problem and I noticed there is no response to this.
I am not sure the answer is correct as I can see no way to change the SQL that creates the join in (Click on object--> Select Object Definition--> More Actions--> Adjust SQL Queries).
I could be wrong as I am new to rollbase.
I created a reproducible case with the sports2000 database in an effort to get an answer.
Here are the steps.
1. Create a sports2000 db and incorporate the rollbase tables into the public schema.
2. Create a new app sports2000.
3. Create an object based on the external table salesrep (this will be the parent table).
4. Remove the salesrep index from the customer table.
5. Create an object based on the external table customer.
Select the customer.salesrep field as a foreign field, relate it to the salesrep.salesrep.
6. Change the default list view for the customer to display cust.custnum cust.name & cust.salesrep
7. Bring up the application view for the customer and you get the error under the customer.salesep column.
I can supply a backup of the sports2000 that illustrates this.
Thanks,
Diego.
diego, why do you remove the index?
Hi Gus,
I removed the index because I was reproducing the problem. The database I was using when I encountered this did not have an index on the foreign field as the table was small only about a dozen records.
When interfacing rollbase to a customer's database it may not be possible to introduce schema changes to conform with rollbase (if this is in fact is a requirement by rollbase, i.e. to have an index on a foreign field). Having said this for performance reasons you would probably alway want the foreign keys to be indexed.
Hope this makes sense.
Thanks,
Diego.
Does the problem occur if the SalesRep field is indeed indexed?
Looks like the absence of an index is a red herring, retested and it still happens if the index is there.
This topic has gone along a while here on Progress Community .... Have you reported it to Customer Support (esp now that you have a reproducible test case)?=
Will do :)