Catching SQL errors 1

[Bryan - Gendev]

On my Query form I have some boilerplate SQL statements available which are pretested and won't 'crash'. They are run by being selected in a list box into a text box then a command button with the code shown below.

I also have a text box in which the user can type an SQL statement.

I use this code on a command button to run it

<code>

LOCAL SQL

SQL = ALLTRIM(thisform.text2.Value)

&SQL

</code>

Is there a way to catch errors in the typed statement when the command is run via the &SQL command?

I would like to put up a message to inform the user.

Thanks

GenDev

 

[Mike Lewis]

Are you running the SQL commands within Visual FoxPro? If so, then the easiest way to trap errors is to use a TRY/CATCH/ENDTRY construct:

LOCAL SQL, llError
SQL = ALLTRIM(thisform.text2.Value)
llError = .F.
TRY
  &SQL
CATCH
  llError = .T.
ENDTRY
IF llError
  * Error in SQL statement
ELSE
  * OK
ENDIF

Or, are you running the SQL against a remote database? If so, then your SQLEXEC() function will return a negative number if there is an error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Bryan - Gendev]

>Are you running the SQL commands within Visual FoxPro?

Yes and your solution works perfectly - thanks. I've never used that construct before.

GevDEv

 

[Olaf Doschke]

Mike,

as gendev does mention his code literally and it's &SQL and not SQLEXEC(h,SQL), it's surely Foxpro, also this is already clear from the outset of another recent thread.

So your answer is fine and I second it, I would also have suggested using TRY..CATCH. You could also put the handling directly in the CATCH block instead of just setting a llError variable .t. there, but this is also good in a way, because eventual errors occurring in a lengthy catch block code could give some extra trouble.

So take Mikes answer very literally.

Bye, Olaf.

 

[Mike Lewis]

as gendev does mention his code literally and it's &SQL and not SQLEXEC(h,SQL), it's surely Foxpro

Yes, of course. But it was early on a Sunday morning, and my mind was on other things.

GenDev,

I'm glad that it works. Just to add that - depending on the type of users who will be using this feature - you might need to watch out for security issues. It would be simple for a knowledgeable user to enter a DELETE command, for example, which your application would than cheerfully execute. (Just something to think about.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy