Sign in to follow this  
Followers 0
Wooltown

Problems With Commandtype When Reading From Sql Server

20 posts in this topic

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

Share this post


Link to post
Share on other sites



Hi Sven

I use this udf (many thanks to the person that supplied it :think: ) 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.

; ------------------------------------------------------------------------------
;
; 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by danisam

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Tahnks for your help.

How can i add a debug line ?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by danisam

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

$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 by dougie

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by danisam

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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..

:think:


Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

Thanks, im going to try.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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 by danisam

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Regards

Sven

Share this post


Link to post
Share on other sites

#20 ·  Posted

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

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  
Followers 0