Trying to get SQL Exception using "ADO.CreateADOCommand"

[jhonathanramos]

Hello there;

This is my first topic of doubt. I have been researching for some time without a solution and so I decided to ask, because even with some research inside and outside the Smartbear community I could not find a solution. 

I am trying to execute an SQL statement in SQLServer through the existing ADO.Command in TestComplete, expecting the return of an exception.

The database only returns if there is a syntax error. However, if the syntax is correct, but the operation can not be performed, the database is not returning the exception. Just with the return of the exception I can handle the situation in my code.

Test execution needs to stop if the database fails to execute the statement I submitted to it.

Exemplifying:

I tried to use "try-except" in my delphiscript, but the database is not returning the exception forcing it to drop directly on the except line. (Neither using a division-by-zero statement)

I've also tried to create a T-SQL with a "try-except" forcing a raiserror, but even then, I'm not able to catch the exception.

I have looked at the properties in the ADO.CreateADOCommand and there is apparently no property to flag the exception.

I am believing that the database suppresses this information and it is not returning to the application.

Or maybe I'm failing at something I have not yet observed.

Could anyone tell me what might be happening? And how could I solve this to get the exception I hope so much?
Thank you. 

[tristaanogre]

From your post, I'm assuming you're trying to get the SQL database queries to raise an exception on the SQL server side and then have your Delphi Script in TestComplete handle that exception, is that correct?

If so... I don't believe that's possible.  Anything returned by ADO is not returned as raised exception but encapsulated in the ADO objects.  You'll need to retrieve any error codes, conditions, etc., from your result set and then use that information to raise an exception within your delphi script code.

[jhonathanramos]

Hello, Robert,
Thanks for your response. 

I'm not sending a query to the database expecting a set.

Imagine that Delphiscript is sending an SQL statement as a "Restore Database TC from Database_Snapshot" or an "insert". Assuming that in 1st case the path to the backup file to be restored is not available at that time and the 2nd "insert" is prevented by integrity constraint.

The delphiscript would only wait for the completion of the execution of the SQL statement that was sent, if execution is prevented for some reason. Should this reason not be raised as an exception?
If this is not how it works, how will I know if it has failed or not? 

Thank you. 

[tristaanogre]

Well, result set was just an example.  I don't know, off hand, the specifics of every SQL command that can be executed via ADO.  But the principle that I mentioned is still the same: exceptions raised in the SQL will not re-raise as exceptions in your DelphiScript.  So, yes, on the SQL server side, it would raise as an exception if your SQL is coded to do so.  But the ADO COmmand object "handles" that exception so that it is not reraised. In order for you to determine success/failure of the SQL you'll need to either examine whatever results you DO get back (could be numeric error code, status code, etc) OR, run a subsquent query to determine the state of the database after the command to see if it completed successfully.  

 I could be wrong in this, but that's been my experience. 

[jhonathanramos]

Hi, Robert.

I understand that you said.

But, why it works different when my syntax is incorrect? In this case the exception is raised.

[tristaanogre]

Because in that case, it's not the SQL server that's raising the exception but your utilization of the ADO objects.  

[jhonathanramos]

Hi, Robert.

What alternative would you use to solve this problem?

Do we only have "ADO" to send instructions to the database with TestComplete?

Making a subsequent query does not seem to be a good practice, because each case will require a specific subsequent query, and I will not have how to predict everything.

Does TestComplete accept the use of other connection drivers like DBExpress for example?

Thank You.

[tristaanogre]

I'm not sure another database driver will work any differently.  I'm not an expert but I'm pretty sure that however you connect to your SQL database, whatever object/driver that you are using "masks" the exceptions that SQL Server returns.  Whether it's ADO or some other driver type, you are limited by what those objects actually return... there usually is no "re-raising" of exceptions in your query back to the calling application.

Someone else may have a different solution.

As to using those other drivers... so long as you can instantiate them with something like Sys.OleObject or something like that, then yes, you can use them.  They won't have the built-in wrappers like TestComplete does for ADO, but you should be able to use them on a lower level.

[jhonathanramos]

Hi! 

I'm also not an expert, but in more common development tools we often have this database return, about when we have a "integrity constraint," for example. Not even in this case do I get a response using "ADO".

For this reason I am believing that using another driver to connect with the database could raise these exceptions. 

Maybe there is another way in TestComplete itself that I do not know, so here I am trying to understand how it works.

TestComplete is a great tool for developing scripts, I can not believe I can not do this. 

[tristaanogre]

Because TestComplete is for developing scripts that execute within the specific execution engine of TestComplete.  So, there is a scoping that is happening, I believe, where that engine does not have access to the exceptions raised by the SQL database.  You will need to create something taht does that can be called within the TestComplete engine...