View full Query String when Debugging

[Former Member]

I am trying to debug a database error my application throws randomly.

I have a window with two shared DataWindows on it. Most of the time when I modify and save everything works fine. But on some records the application throws a "row changed between retrieve and update" error.

I read here that it could be to do with the database/triggers. I have been through debugging but cannot seem to find the actual query that gets executed.

I don't think this is an error with the application itself as I have been able to modify the same column of other records using the same process. so I think there has to be something wrong with the query for this particular record.

The closest I could find to the query being executed was:

string sqlsyntax = "UPDATE "TABLENAME" SET "CHANGED_ROW" = :1 WHERE "TABLE_ROW_1" = :2 AND "TABLE_ROW_2" = :3 AND .. etc".

But cannot see what the values of :1, :2 etc are to get a full idea of what the query is, and hopefully why it is happening for this particular record.

Is there a way to view the exact query that is being executed against the database?

It is an Oracle database, application is PB 12.5 classic

Thanks

[Former Member · Accepted solution]

Hi J;

  FWIW: I would activate PB's SQL tracing feature and then run the application to the where the issue occurs ...

ie:

SQLCA.DBMS = "TRACE ASE"     or

SQLCA.DBMS = "TRACE ORA"    or

SQLCA.DBMS = "TRACE ODBC"    // for example

HTH

Regards ... Chris

[Former Member]

An argument to the DBError event is row. You could consider populating local variables with the values of that row to see what the primary and original buffer holds at the time. You can then view them in the debugger.

"row changed between retrieve and update" means that the where clause of the update statement fails to find the row in the database. If you are sure that no other user has in fact changed the row since it was retrieved or you don't care, as in last save wins, you could consider using "Key columns" for updates rather than all or modified columns. If it only happens occasionally, I would hasten a guess that you have "Key and Modified" columns set for update.

If you only ever encounter the problem on a second save, then there is a chance you haven't done a ResetUpdate() after the Update(). More likely, the database has defaulted something or a timestamp has been set by the database, in which case, even a ResetUpdate() will not set the timestamp in your datawindow to the value now in the database. You would cure this with a retrieve.

[Former Member]

Thanks for the info Lars. I believe it is something wrong with this particular update statement, It would be good to just be able to view the full sql query as it would be much easier to determine which part of the where clause is failing me.

I will try the key columns only (do I do that for both DWs? or just parent from the sharedata?), as at the moment the query string from the original post includes about 20 columns in the where clause. This is why it would be good to look at it, rather then have to go through and stick each one in a variable and output it somewhere..

*edit*

Both DWs are set to "Key and Updatable Columns" should I change both to just have Key columns for the update properties?

thanks

[Former Member]

The sqlpreview event occurs before each statement is sent to the database by the datawindow. Add any code there so you can add a breakpoint. A Beep(1) would be work. One of the arguments is the sql statement.