If a foundset for tableb is based (in part) on criteria of related table a (e.g. in the find: tableb_to_tablea.foo=‘>0’) and then we attempt to convert the foundset to tablea like this:
var cvs = convertFoundSet(foundset, taqbleb_to_tablea)
Then two problems result:
The generated SQL is invalid, error is:
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Invalid argument value message from server: "Not unique table/alias: 'tablea'"
The problem appears to be that tablea is mentioned twice in the “FROM” clause.
2) This error is not reported (the only hint is that the resultant foundset is empty)
Navigate to ccpurchases form; 2) press “Search Purch” (foundset now in part depends on related ccard field); 3) Press “To Cards” which invokes the failing convertFoundSet().
This failure to resolve the foundset of related record(s) seems to be limited to where you have performed your search on related data !
So, in your purchase table, part of your find looks for data in the related cr card table.
The search runs correctly and the results returned are fine but the follow up foundset conversion fails !
The debugger shows that following a search on related data it cannot resolve the related foundset and returns null which it then cannot convert into a found set !
If you run a manual search the same problem occurs IF you use related data in the search (I just added a portal to the purchase form !)
If you search on local purchase data then it works fine !
This failure to resolve the foundset of related record(s) seems to be limited to where you have performed your search on related data !
So, in your purchase table, part of your find looks for data in the related cr card table.
The search runs correctly and the results returned are fine but the follow up foundset conversion fails !
Yes, that’s pretty much my understanding of this problem too.
Harry Catharell:
The debugger shows that following a search on related data it cannot resolve the related foundset and returns null which it then cannot convert into a found set !
Hmmm… I wonder if that’s a problem in its own right, or the symptom of an underlying problem. Specifically this mal-formed SQL query:
select ccards.ccards_id from ccards,ccards,ccpurchases where (ccpurchases.purchamt > 10.0 and ccards.cards_exp > '200506' ) and ccpurchases.ccard_id = ccards.ccards_id order by ccpurchases.ccpurchasesid
Note the “… from ccards,ccards,…”
Harry Catharell:
If you run a manual search the same problem occurs IF you use related data in the search (I just added a portal to the purchase form !)
If you search on local purchase data then it works fine !
I’m not sure of the significance of this bit, but hopefully someone who knows the internals can make something of it.
2005-07-22 09:32 : select ccards.ccards_id from ccards,ccards,ccpurchases where (ccpurchases.purchamt > ? and ccards.cards_exp > ? ) and ccpurchases.ccard_id = ccards.ccards_id order by ccpurchases.ccpurchasesid
2005-07-22 09:32 : java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Tables or functions ‘ccards’ and ‘ccards’ have the same exposed names. Use correlation names to distinguish them.
This seems to bear out what you are saying about the malformed SQL statement having multiplereferences to ccards table !
Did someone say, ‘bug’ !!!???
Have you tried taking that SQL statement, removing the extra ‘ccards’ reference and running it as a getdatasetbyquery function ?
Will be fixed in Servoy 3.0 and if urgent merged to 2.2.x branch (please let us know)
BTW The issue seems only to happen when you move back and forth with convert