2

I have a DataWindow in a PB10.5 application which works perfectly until I add a WITH clause to the top of the SQL query that sits behind. The query painter accepts it as valid syntax, and if I manually retrieve the data in the DataWindow painter, it works perfectly there too. The problem comes when running the application where the retrieve consistently returns -1. Can anyone tell me why this might be happening and what I can do to prevent it?

ninesided
  • 23,085
  • 14
  • 83
  • 107
  • That's interesting. I have the opposite problem with CTEs using PowerBuilder and DB2. In the DB Painter, the SQL won't return data (no errors, just no data). But if I put the SQL in a datawindow and run it from code it works fine. – Slapout Jan 23 '13 at 14:59

1 Answers1

3

You could look what returns sqlca.sqlerrtext (providing that the current object transaction is the default sqlca, you may need to adapt that) just after the -1 return by retrieve() it should help.

You could also put a breakpoint into the DW dberror() event to look if there is something useful here in case of error.

Seki
  • 11,135
  • 7
  • 46
  • 70
  • There's nothing in `sqlerrtext` :-( – ninesided Jan 23 '13 at 11:46
  • @ninesided: Obviously, -1 for `retrieve()` means there is a problem. Did you checked that the `retrieve()` is not overloaded or wrapped in something that is performing a `commit` or `rollback` that could reset the error text from the DBMS? Also just added a point about `dberror` in my answer. – Seki Jan 23 '13 at 11:52
  • `dberror()` was a good shout, there's a sneaky hidden ORA-00942 getting returned from the DB that was being gobbled. Not exactly sure why it is being raised, yet but I've at least got a direction to look in. Thank you! – ninesided Jan 23 '13 at 12:13