How to know if a statement is Execute Block?

upscene · Post by **upscene** » Wed 09 Dec 2009 12:32

Hi,

Do you have something in place to know if SQL is "execute block" instead of a normal SELECT? (given that both return "sqltype select")

Thanks,

Martijn Tonies
Upscene Productions

Plash · Post by **Plash** » Thu 10 Dec 2009 08:50

After you prepare a query the SQLType property returns SQL_EXEC_PROCEDURE = 8 for EXECUTE BLOCK.
For SELECT it returns SQL_SELECT = 1.

upscene · Post by **upscene** » Thu 10 Dec 2009 10:44

Plash wrote:After you prepare a query the SQLType property returns SQL_EXEC_PROCEDURE = 8 for EXECUTE BLOCK.
For SELECT it returns SQL_SELECT = 1.

As far as I can see, for an EXECUTE BLOCK with output parameters, SQL_SELECT is returned. Can you confirm that?

Now, if this is the case, could you make some kind of property/function that allows me to check if it's really an EXECUTE BLOCK instead of a plain SELECT?

Thanks.

Plash · Post by **Plash** » Fri 11 Dec 2009 08:46

There is no special type for EXECUTE BLOCK. So the client library returns SQL_EXEC_PROCEDURE or SQL_SELECT for such statement.

You can check if the statement begins with the 'EXECUTE BLOCK' text. We are not planning to add such method to IBDAC.

upscene · Post by **upscene** » Fri 11 Dec 2009 08:53

Plash wrote:There is no special type for EXECUTE BLOCK. So the client library returns SQL_EXEC_PROCEDURE or SQL_SELECT for such statement.

I know.

Plash wrote: You can check if the statement begins with the 'EXECUTE BLOCK' text. We are not planning to add such method to IBDAC.

Given that you -have- to parse the SQL anyway, because of the (optional) input and output parameters in the block header, a simple "IsExecuteBlock" would do (after Prepare, for example).

This would help me to know when I have to signal the user that an explicit transaction has been started.

Please do add it.

If I would just be checking "execute block" at the start, I have to take in account SQL comments in front of them, return characters, extra spaces and so on. Given that you have already done all this work, I feel it would be easier for you to add a simple property than for me to duplicate this work...

Plash · Post by **Plash** » Tue 15 Dec 2009 10:07

In the next IBDAC build we will add the SQLTypeEx property. This property returns the same values as SQLType for all statements except EXECUTE BLOCK.

For EXECUTE BLOCK this property returns SQL_EX_EXECUTE_BLOCK.

upscene · Post by **upscene** » Tue 15 Dec 2009 13:04

Thank you!