PowerBuilder ‘Gotcha’ – SQL Errors

Posted on Tuesday, August 10th, 2010 at 8:20 pm in

Most applications I’ve worked on make at least a limited attempt to inform the user of any database related errors especially after an insert, update, and (usually) a commit. Along with this is normally code which displays the database error from the transaction object in a messagebox. Unless you use a custom messagebox with a timer you have to account for the user who presses save while walking away to go to lunch and thereby messes up who knows what.

Generally the flow is: Execute the SQL, check for an error, show error, rollback the transaction.


update PartMaster
set stockDtm = :ldt_pro_start
where PartMaster.partNo = :ll_part_id ;

IF sqlca.sqlcode <> 0 THEN
	Rollback USING SQLCA; // lost the error text from the update...
	Messagebox(this.title, "SQL error: " + SQLCA.sqlerrtext ,StopSign!)
	// and so on

This code will only show the error if the rollback failed. To show the ‘real’ error you need to assign the sqlerrtext to a string variable prior to issuing any further SQL commands.