How do I check for errors after PROP:SQL?

questions faq
1

This is quite a common question, hence the #questions:faq status!

Consider the following:

MyTableOrView{PROP:SQL} = 'SELECT some, bunch, of, fun, columns FROM TheTable WHERE ???'
LOOP
  Next(MyTableOrView)
  IF ErrorCode()
    BREAK
  END
END

Does the Next() fail because there are no results or because the PROP:SQL had a spelling mistake?!

MyTableOrView{PROP:SQL} = 'SELECT some, bunch, of fun, columns FROM TheTable WHERE ???'
IF ErrorCode()
  Stop('There was a PROP:SQL Error... The error is: ' & Choose(Error()=90, FileError(), Error()))
END
LOOP
  Next(MyTableOrView)
  IF ErrorCode()
    BREAK
  END
END

Friends don’t let friends PROP:SQL without error checking folks! :slight_smile:

Also… what does your favourite common error checking method look like… no one does IF ErrorCode() everywhere do they?

e.g.

MyTableOrView{PROP:SQL} = 'SELECT some, bunch, of fun, columns FROM TheTable WHERE ???'
IF MyFancyCheckError('Helpful error message to show if there is an error!') = EQUATE:Abort
  RETURN ! or EXIT or some kind of shortstop here?
END
3 Likes
2

Hi I use

if fileerror() 
  stop(filerror())
end

after the prop:sql it only returns the error if there is a formatting issue and say exactly what it is.
error will return if there are no record

2 Likes
3

For query statements, I think the error only occur after NEXT statement. To get more info about file driver error (error code = 90), we can use fileerrorcode and fileerror instead.

4

If you have syntax errors in your PROP:SQL then you can definitely get errors directly after that regardless of what kind of query it is.

One common one would be if your column labels have changed in the sql schema but your PROP:SQL is using a string constant which does not reflect the schema change.

If you were not checking for errors after the PROP:SQL then you would miss that and instead your LOOP/NEXT would suddenly start giving you no results.

1 Like
5

Yep, always check errorcode and as with other IO, check fileerror() and fileerrorcode() as errorcode indicates. Tracing helps as well if you cant figure it out from there. The help shows how to programmatically turn tracing on and off for a particular table.