CODEWITHSUNDEEP
validationsyntaxpowerbuilder
user7022367
user7022367

Reputation:

How to validate a query without executing in PowerBuilder

wondering if there is way to validate a query before executing

Is there way to check/validate Query without executing it?

Upvotes: 1

Views: 988

Answers (3)

Marc Vanhoomissen
Marc Vanhoomissen

Reputation: 395

Put the select statement in any script and compile it. Part of the work will be to check the SQL syntax against the database you are connected to. Watch out: you need at least one bound variable in the column list of your SQL statement. This is not the case for other DML statements.

Example: in my case:

select noms into :ls_ttt  from contacts;

results in a message Unknown columns 'noms' in 'field list'.

However,

select nom into :ls_ttt  from contacts;

does not show any error.

Hope this helps.

Upvotes: 0

Eric Glenn
Eric Glenn

Reputation: 399

One way that we validate SQL is to add a condition to the SQL that could never be true.

Example:

long ll_rc
long ll_result
string ls_sql, ls_test
string ls_message

//Arbitrary SQL
ls_sql = "SELECT * FROM DUAL"

//This SQL when executed will always return 0 if successful.
ls_test = "select count(*) from ( " + ls_sql + " WHERE 1 = 2 )" 

DECLARE l_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :ls_test;
OPEN DYNAMIC l_cursor;

ll_rc = SQLCA.SQLCODE
choose case ll_rc
    case 0
        //Success
        ls_message = "SQL is properly formed"
    case 100
        //Fetched row not found. This should not be the case since we only opened the cursor
        ls_message = SQLCA.SQLERRTEXT
    case -1
        //Error; the statement failed. Use SQLErrText or SQLDBCode to obtain the detail.
        ls_message = SQLCA.SQLERRTEXT
end choose

CLOSE l_cursor ; //This will fail if open cursor failed. 

messagebox( "Result", ls_message )

Note: If your SQL is VERY complicated, which I suspect it isn't, the database optimizer may take several seconds to prepare your SQL. It will be significantly less time than if you run the entire query.

Upvotes: 2

Matt Balent
Matt Balent

Reputation: 2397

Since the database is the final arbitrator for what is "valid" (table and column names and such) the general answer is no. Now you could come up with a class in PB which checks statement syntax, object names, etc. so you wouldn't have to touch the db but it would be obsolete as soon as any changes were made to the db.

Upvotes: 0

Related Questions