Jump to content
Sign in to follow this  
JLogan3o13

Looking to get more error checking from SQL

Recommended Posts

JLogan3o13

I am currently working with a large script that connects to and pulls data from several databases, both Oracle and SQL Server. In both cases, I am connecting to the db using the locally installed client and then passing in a query. Everything works as expected, I am just looking for some improvement.

One section of the code takes input from the end user (policy number, for example) and then modifies the sql query appropriately. What I am having a hard time with is the error checking. I have a COM error handler in there, and can catch the error, but mainly what I get is the standard "requested action with this object failed" verbiage. I am just curious if any SQL experts know how I can retrieve better errors through the local client, something akin to RapidSQL or SQL Management Studio that would tell me "no records returned" or "invalid identifier" if a value is missing. These queries are quite large in some cases, and I am sure they'll change or be updated from time to time. I am just looking for a method to better troubleshoot a failure (see below for example).

; User prompted for Policy Number. Policy Number added to query

;Query
$sCallersSQLStatement = StringTrimLeft(_ArrayToString($aQuery, " "), 3)

;connect to Oracle db
$sCallersConnectionString = "Provider=MSDAORA.1;User ID=svcaccount;Password=dev;Data Source=ORADEV;Persist Security Info=False"

$ado = ObjCreate("ADODB.Connection")
    If Not IsObj($ado) Then MsgBox(64, "", "Error creating Connection Object")
$adors = ObjCreate("ADODB.RecordSet")
    If Not IsObj($adors) Then MsgBox(64, "", "Error creating RecordSet Object")
$ado.Open($sCallersConnectionString)
    If @error Then
       MsgBox(64, "", "Error opening Connection")
    Else
       _FileWriteLog($log, "Successfully opened connection to Database.")
    EndIf
$adors.Open($sCallersSQLStatement, $ado)
    If @error Then
       MsgBox(64, "", "Error calling Statement")
    Else
       _FileWriteLog($log, "Successfully passed query to database and returned record set.")
    EndIf

;typically the issue is with trying to access the record set - $adors.Open($sCallersSQLStatement, $ado). This is caught with the standard error handling, but I am not 

;provided with a ton of information regarding the cause of the failure (could be one of several things wrong). Just curious if there is a way to pull specific error messages from the Oracle client.

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
trancexx

I'm not SQL expert you ask for but I know this:

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf
That helps?

♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites
JLogan3o13

Thanks for the suggestion. Unfortunately, it doesn't write anything to the console - I get the same error.

post-54985-0-35394000-1380221030_thumb.p

Edited by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
trancexx

You are running 3.3.8.1 AutoIt. For that one you need COM error handler if you want to continue execution on COM error.

This was incredibly lame reaction so I changed that eventually.

Error function can be simple dummy or full version. Latter will give you full error report just like ADO error object would.


♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites
trancexx

BTW, on that pic you have hidden path to your script, not.


♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites
JLogan3o13

I wondered as much. And my fault as well, I apologize for not putting in my original post that, due to some other project constraints, I am not free to move up from 3.3.8.1 at the moment. But, at least knowing that it will be available when I am able to update is good news.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
JLogan3o13

BTW, on that pic you have hidden path to your script, not.

 

Yeah, knee-jerk reaction. After posting I wondered why I bothered :)


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
trancexx

You wonder too much :P.

What I wonder myself is if you have forgotten how to use COM error handler. This is wrong:

ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

This is right:

$oCOMErrorObject = ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

Do you see the difference?

Edited by trancexx
  • Like 1

♡♡♡

.

eMyvnE

Share this post


Link to post
Share on other sites
mLipok

This is great.

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop
! [Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego

+>09:57:38 AutoIt3.exe ended.rc:0
>Exit code: 0    Time: 0.518

TRANSLATION: ! [Microsoft] [ODBC Driver Manager] Can not find the data source name, and no default driver specified.

 

very useful

Thanks @trancexx

EDIT: typo

Edited by mlipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-03-17

Share this post


Link to post
Share on other sites
JLogan3o13

You wonder too much :P.

What I wonder myself is if you have forgotten how to use COM error handler. This is wrong:

ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

This is right:

$oCOMErrorObject = ObjEvent("AutoIt.Error", "_ErrFunc")
Func _ErrFunc($oError)
    ; Nothing
EndFunc

$sCallersConnectionString = "WTF"
$oADO = ObjCreate("ADODB.Connection")
If Not IsObj($oADO) Then Exit MsgBox(64, "", "Error creating Connection Object")

$oADO.Open($sCallersConnectionString)
If @error Then
    For $oError In $oADO.Errors
        ConsoleWrite("! " & $oError.Description & @CRLF)
    Next
Else
    ;...
EndIf

Do you see the difference?

 

I do see the difference; and see why I was being thick. When copying over your example I didn't include my error handler. I see that done properly (imagine that), it gives me the information I need, even in 3.3.8.1. Thanks for the nudge.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×