Problems With Commandtype When Reading From Sql Server

[Wooltown]

expandcollapse popup

Opt("WinTitleMatchMode", 1);1=start, 2=substring, 3=exact
Opt("WinSearchChildren", 1)
Opt("MouseCoordMode",0); 0=window, 1=screen
Opt("PixelCoordMode",0)
;Opt("TrayIconDebug",1)
Opt("MouseClickDelay",50)
Opt("GUIOnEventMode", 1) ; Change to OnEvent mode 
Opt("GUICloseOnESC",1)
;Opt("TrayMenuMode",1)
;Opt("TrayOnEventMode",1)

$Codever = "0.01"

List()
; ==================================================================
Func list()
    $DBsource = "Provider=SQLOLEDB;DRIVER={SQL Server};Data Source=Server1;Initial Catalog=DB1;UID=User1;PWD=xxxxx"
    $SQLQuery = "SELECT * FROM tbl_No1"
    ExecuteQuery($DBsource, $SQLQuery)  
EndFunc
; ==================================================================
Func ExecuteQuery ($DBsource,$SQLQuery)

    Const $adLockReadOnly = 1
    Const $adLockPessimistic = 2
    Const $adLockOptimistic = 3
    Const $adLockBatchOptimistic = 4
    Const $adOpenForwardOnly = 0
    Const $adOpenKeyset = 1
    Const $adOpenDynamic = 2
    Const $adOpenStatic = 3
    Const $adUseServer = 2
    Const $adUseClient = 3
    Const $adCmdUnknown = 8
    Const $adCmdText = 1

    Const $adCmdTable = 2
    Const $adCmdStoredProc = 4
    Const $adCmdFile = 256
    Const $adCmdTableDirect = 512

    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DBsource)
    $adoRS = ObjCreate("ADODB.Recordset")
    $adoRS.CommandType = 1               ; ---------- Here I get the error
    $adoRS.LockType = $adLockReadOnly
    $adoRS.CursorType = $adOpenForwardOnly
    $adoRS.CursorLocation = $adUseClient
    $adoRS.Open ($SqlQuery, $adoCon,-1,-1)
    With $adoRS
        If .RecordCount Then
            While Not .EOF
                msgbox (0,"READ OK","")
                .MoveNext
            Wend
        EndIf
    EndWith
    $adoRS.Close
    $adoCon.Close
    
EndFunc

I can make the Connection to the database, but when I set the CommandType I just get

Error: The requested action with this object has failed.

If I remove the line, Iget the error on the $adoRS.Open instead.

Running Windows 2000 Pro SP4 , and the database is MS SQL 2000. and AutoIT 3.1.1.117 Beta

Regards

Sven

[dougie]

Hi Sven

I use this udf (many thanks to the person that supplied it ) to connect to MS-SQL 2000 - found it on this forum (added the Func _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=900) because some of the queries run for a long time and the statement times out)

It does not specifically set the 'CommandType' and it seems to be fine. I am accessing up to 6 million transactions with this udf.

expandcollapse popup

; ------------------------------------------------------------------------------
;
; AutoIt Version: 3.1.1++
; Language:    English
; Description:  Functions for ODBC/SQL databases.
;
; ------------------------------------------------------------------------------

; Retrieve registerered DSN resources, default type USER and SYSTEM
Func _SQLGetDSN($s_DSNType="")
    Local Const $s_ODBCregUser   = "HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local Const $s_ODBCregSystem = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local $s_DSNList
    
    $s_DSNList=""
    if $s_DSNType <> "SYSTEM" then  $s_DSNList &= _SQLGetDSNregister($s_ODBCregUser)
    if $s_DSNType <> "USER"   then  $s_DSNList &= _SQLGetDSNregister($s_ODBCregSystem)
    return $s_DSNList
EndFunc

; Retrieve register entries, used by _SQLGetDSN
Func _SQLGetDSNregister($s_RegEntry)
    Local $s_Entries, $s_Nm, $i
    $s_Entries=""
    $i = 1
    Do
        $s_Nm = RegEnumVal($s_RegEntry,  $i)
        If $s_Nm <> "" Then $s_Entries &= $s_Nm & "|"
        $i += 1
    Until $s_Nm = ""
    return $s_Entries
EndFunc

; Open database connection, on empty DSN a ADO will ask for specification
Func _SQLOpen($s_DSN="")
    Local $o_ADOcn 
; Create ADO connection
    $o_ADOcn = ObjCreate ("ADODB.Connection") 
; Open ADO connection, only prompting for missing params adPromptComplete=2
;$o_ADOcn.Properties ("Prompt") = 4
    $o_ADOcn.Open ($s_DSN)
    if @error then return 0
    return $o_ADOcn
EndFunc

; Close database connection
Func _SQLClose ($o_ADOcn)
    $o_ADOcn.Close 
EndFunc

; Send a schema request, optional pass a filter
Func _SQLschema($o_ADOcn, $i_SchemaID, $as_Filter=0)   ; 1="", $s_filter2="", $s_filter3="", $s_filter4="", $s_filter5="" )
    Local $o_ADOrs 
    if not isObj($o_ADOcn) Then
        SetError(1)
    else
        if IsArray($as_Filter) Then 
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID, $as_Filter)
        else
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID)
        EndIf
    EndIf
    if @error Then return 0
    return $o_ADOrs
EndFunc

; Send a query, optional pass a max number of records to retrieve
Func _SQLQuery($o_ADOcn, $s_Query, $i_MaxRecords=0 )
    Local $o_ADOrs
    if not isObj($o_ADOcn) Then
        SetError(1)
    else
        $o_ADOrs = ObjCreate ("ADODB.Recordset")
        $o_ADOrs.CursorType = 0                     ; adOpenForwardOnly = 0
        $o_ADOrs.LockType   = 3                     ; adLockOptimistic  = 3
        $o_ADOrs.MaxRecords = $i_MaxRecords; maximum records returned by query
        $o_ADOrs.Open ($s_Query, $o_ADOcn)
    Endif
    if @error Then return 0
    return $o_ADOrs
EndFunc

; --- retrieve fieldnames from the given qryid
Func _SQLGetFields($o_ADOrs, $s_Seperator="|")
    Local $i, $s_Fields
    $s_Fields=""                                ; Get information about Fields collection
    With $o_ADOrs
        For $i = 0 To .Fields.Count - 1
            $s_Fields &= .Fields($i).Name & $s_Seperator
        Next
    EndWith
    return $s_Fields
EndFunc

; --- retrieve fieldvalues and move to next row of given qryid
Func _SQLGetRow($o_ADOrs, $s_Seperator="|")
    Local $s_RowValues
    $s_RowValues = ""
    With $o_ADOrs
        If Not .EOF Then
            For $i = 0 To .Fields.Count - 1
                $s_RowValues &= .Fields($i).Value & $s_Seperator
      Next
            .MoveNext
        Else
            setError(1)
        EndIf
    EndWith
    return $s_RowValues
EndFunc

Func _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=900)
    Local $o_ADOCmd, $o_ADOrs
    $o_ADOCmd = ObjCreate("ADODB.Command")
    $o_ADOCmd.ActiveConnection = $o_ADOcn
    $o_ADOCmd.CommandText = $s_Query
    $o_ADOCmd.CommandTimeOut = $cmd_TimeOut
    $o_ADOrs = $o_ADOCmd.Execute

    if @error Then return 0
    return $o_ADOrs
EndFunc

regards

Dougie

[Wooltown]

Hi !

Thank you for the tip, but unfortunatelly I get the same problem, I tried to call the _SqlQuery and the _SqlCommand.

Error: The requested action with this object has failed.

In _SqlCommand I get it on the Execute line, in _SqlQuery on the Open line.

I run the Query in MS Access with no problem.

Regards

Sven

[danisam]

Hi ,

I have the same problem...

$s_DSN="Provider=SQLOLEDB;DRIVER={SQL Server};Data Source=Daniel-G;Initial Catalog=diirect-guev-resource;Integrated Security=SSPI;UID=sa;PWD=******"
$o_ADOcn = _SQLOpen($s_DSN)

$FechaHora = "12/04/2005 12:45:40"
$ID = "CSO"
$Temperatura = 12 
$Humedad = 34
$presion = 23

 $s_Query = "INSERT INTO diirect-resource-pronostico (" _
& "Fecha," _
& "ID," _
& "Temperatura," _
& "Humedad," _
& "presion" _
& ") VALUES (" _
& "'" & $FechaHora & "'" & "," _
& "'" & $ID & "'" & "," _
& "'" & $Temperatura & "'" & "," _
& "'" & $Humedad & "'" & "," _
& "'" & $presion & "'" _
& ")"

$cmd_TimeOut=900
$o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut)

_SQLClose ($o_ADOcn)

The error was in this part of the UDF ms-sql library Code :

Func _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=900)
    Local $o_ADOCmd, $o_ADOrs
    $o_ADOCmd = ObjCreate("ADODB.Command")
    $o_ADOCmd.ActiveConnection = $o_ADOcn
    $o_ADOCmd.CommandText = $s_Query
    $o_ADOCmd.CommandTimeOut = $cmd_TimeOut
    $o_ADOrs = $o_ADOCmd.Execute   *********** he requested action with this object has failed. ******

    if @error Then return 0
    return $o_ADOrs
EndFunc

Can you help me to fix this problem, or says me how to have a more explicit error message?

How can we catch the error message text from SQL Server ?

Running : W XP, SQL server 2000, AutoIT 3.1.1.117 Beta

Who is the author of the library, post in this thread.

Thanks a lot.

Edited April 12, 2006 by danisam

[dougie]

If you are using SiTe to run, add a debug line before the

$o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut)

Copy the $s_Query line from the output pane and execute it in the MSSQL Query Analyser to check if the statement is valid.

With regard to the $cmd_TimeOut parameter, if you leave it out of the call, it will default to 900 seconds.

Who is the author of the library, post in this thread

I can't remeber but it was difficult to find it.

Regards

Dougie

[danisam]

Tahnks for your help.

How can i add a debug line ?

[dougie]

Instead of the debug option, you can use a msgbox

MsgBox(0,"Sql - INSERT",$s_Query)

which will pop-up before the _SQL_Command function executes. You will then have to type the line from the MsgBox into the Query Analyser to execute it.

[danisam]

I perform the query in a query analyser and it work without pb.

When i put a debug line like that :

MsgBox(262144,'Debug line ~112','Selection:' & @lf & '$o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut)' & @lf & @lf & 'Return:' & @lf & $o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut) & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX

The message box doesn't appear !

I have the error message in the console :

The requested action with this object has failed.: 
$o_ADOrs = $o_ADOCmd.Execute 
$o_ADOrs = $o_ADOCmd.Execute^ ERROR

even, if i put a debug line console.

Edited April 12, 2006 by danisam

[dougie]

Did you copy the statement from the message box to the Query Analyser?

[dougie]

$s_DSN="Provider=SQLOLEDB;DRIVER={SQL Server};Data Source=Daniel-G;Initial Catalog=diirect-guev-resource;Integrated Security=SSPI;UID=sa;PWD=******"

Should the Initial Catalog=diirect-guev-resource; have two i's in it?

Check if the _SQL_Open statement return a 0 or a handle. A zero means an error occured.

Edited April 12, 2006 by dougie

[danisam]

YES, I copy the statement from the message box to the Query Analyser and it works.

_SQLOpen($s_DSN) return a handle, no problem with the _SQLOpen($s_DSN) statement.

How can i catch and print the SQL Error ?

Edited April 12, 2006 by danisam

[dougie]

Use the MsgBox command as I entered it.

MsgBox(0,"Sql - INSERT",$s_Query)

and make sure that it executes after the $s_Query has been created but before the _SQLCommand call, this will display the string $s_Query that you created. As the program stops on the _SQLCommand call, the message box cannot be after the _SQLCommand call.

I perform the query in a query analyser and it work without pb.

When i put a debug line like that :

MsgBox(262144,'Debug line ~112','Selection:' & @lf & '$o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut)' & @lf & @lf & 'Return:' & @lf & $o_ADOrs = _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut) & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX

The message box doesn't appear !

I have the error message in the console :

The requested action with this object has failed.: 
$o_ADOrs = $o_ADOCmd.Execute 
$o_ADOrs = $o_ADOCmd.Execute^ ERROR

even, if i put a debug line console.

[Jos]

Did you try added a Com object error handler to see what the Com error is ?

This is not an AutoIT Bug item, but a Com coding issue... will move it to support soon..

[danisam]

Thanks, im going to try.

[SvenP]

Hello,

I haven't been around lately, JPM pointed me kindly to this matter.

This problem is difficult to find because the methods of debugging were not very efficient.

If one is suspecting problems related to COM, first step is always to use a COM errorhandler for debugging. It will give you a source and a possible description of the error.

Secondly, when a COM function fails when it's being called from within a AutoIt statement, the statement itself will also fail.

So the method of debugging by using MsgBox(....COMFunction(..)) won't help if you suspect the COM function. If the COM function fails, it would throw a fatal error, stopping script execution; the Msgbox would never appear.

A structure of a COM script being debugged could look like this:

; Start of script. 

; Step 1: Install a COM Error handler
$objError=ObjEvent("AutoIt.Error","yourerrorfunction")

; ...your script here...

; Step 2: Call the suspected function on a separate line
$Result=SuspectedCOMFunction(arguments,..)

; Step 3: if the function fails, it would call yourerrorfunction()
; otherwise the script continues to the next line.
Msgbox(0,"result of COM function",$Result)

....

func yourerrorfunction()

  Msgbox(0,"error", $objError...etc..)  
 ; See example in helpfile
 ; It can show you the source of the error, the script line number, error description, etc.

endfunc

Regards,

-Sven

[danisam]

The Msgbox, is running now with this code :

func yourerrorfunction()

Msgbox(0,"error", "message : " & $objError.message & @LF _

& "description : " & $objError.Description & @LF _

& "number : " & $objError.Number & @LF _

& "source : " & $objError.Source & @LF _

& "SQLState : " & $objError.SQLState & @LF _

& "Native error : " & $objError.NativeError )

endfunc

Thanks a lot.

Edited April 17, 2006 by danisam

[Wooltown]

I made a test, and the result from the line

$HexNumber=hex($oMyError.number)

gave the value 80020009, all other values such as source, description, windescription gave 00000000.

Regards

Sven

[Confuzzled]

Is http://support.microsoft.com/default.aspx/kb/175239 relevant?

Check your Username and password are correct and being passed properly.

[Wooltown]

Thanks for the tip, but I have checked it out. If I specify an erratic password I get an error message earlier.

Regards

Sven

[SvenP]

Thanks for the tip, but I have checked it out. If I specify an erratic password I get an error message earlier.

Regards

Sven

A very late answer from me. I don't know if the problem has already been solved, but if you look up the error number at www.microsoft.com, it will give you a some possible causes:

Error 80020009 = "Exception occurred"

This is a generic error message that only indicates that the connection to the database failed.

http://support.microsoft.com/kb/q175239/ PRB: 80020009 Error When Retrieving Data from SQL

http://support.microsoft.com/?id=216569 PRB: Using the DE Command in a Recordset Causes an Error

When you use Google to look up this error code in combination with ADO and .Execute, you will see that it's a very common error message in SQL programming.

Regards,

-Sven