Search

PowerBuilder ‘Gotcha’ – Malformed Imbedded SQL

Posted on Thursday, October 7th, 2010 at 8:20 pm in

I came across a statement like this while tracking down a separate issue and at first didn’t think much of it since it wasn’t related to the problem at hand. However, when running my process through the debugger I noticed a SQL error which caught my attention.

<code>UPDATE dbo.shipperDetail
SET contractId = (SELECT max(docId) 
			FROM dbo.orderHeader
			WHERE dbo.shipperDetail.catalogNo = dbo.orderHeader.catalogNo
			AND dbo.shipperDetail.partNo = dbo.orderHeader.partNo
			AND dbo.shipperDetail.customerNo = dbo.orderHeader.customerNo
			and dbo.orderHeader.status = 'A')
FROM dbo.shipper
WHERE<strong> dbo.shipperDetail =</strong> :al_ship_id -- what column?
AND dbo.shipper.shipperNo = dbo.shipperDetail.shipperNo
AND dbo.shipper.shipFlag = 'N'
USING SQLCA;
COMMIT;</code>

Notice the WHERE clause.

Since SQLCA.SQLCode was not checked after the initial UPDATE statement, the error was not caught. This was in a production application for several years. Remember that only imbedded SELECT statements are validated when you ‘compile’ or save a script.

Updated March 2021

Tags:

Top