Jump to content

Trouble with ADO & MS SQL

WebRaider
 Share

Recommended Posts

WebRaider Seeker

WebRaider

Posted
Posted

I have been using AutoIt for some time now and as of late a lot of connecting to a local MSDE SQL 2000 server. I am using AutoIt Version: 3.1.1.0. I don't the problem I am having is with AutoIt, but the ADO I am tring to use. I got one of the MSsql.au3 from some where here.

What I and trying to send works. If run it using SQL Query Analyzer it works. But when I send it using the ADO connection I get no errors back, but it dose not do anything. The table trying to run this on has over 12,000 records. The crazy thing is I created and populated the table a few lines before sending this, without a problem. I have also used this a same TSQL before in other AutoIt scrips.

Anyone have any ideas?

Thanks for the help!

Here's what I am trying to send:

BEGIN TRAN DEDUPLICATE
SELECT DISTINCT * INTO SpecProAutoTest.dbo.Cleaned FROM SpecProAutoTest.dbo.SpecProAutoTest_2UB4240234_20070314_114106_Acterra_SpecPro_Options_Modules
DROP TABLE SpecProAutoTest.dbo.SpecProAutoTest_2UB4240234_20070314_114106_Acterra_SpecPro_Options_Modules
SELECT * INTO SpecProAutoTest.dbo.SpecProAutoTest_2UB4240234_20070314_114106_Acterra_SpecPro_Options_Modules FROM SpecProAutoTest.dbo.Cleaned ORDER BY Module ASC
SELECT * FROM SpecProAutoTest.dbo.SpecProAutoTest_2UB4240234_20070314_114106_Acterra_SpecPro_Options_Modules
DROP TABLE SpecProAutoTest.dbo.Cleaned
COMMIT TRAN DEDUPLICATE

The connection snippet:

; 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

My code:

$sql = ""
$sql = $sql & "BEGIN TRAN DEDUPLICATE" & @CRLF
$sql = $sql & "SELECT DISTINCT * INTO " & $db & ".dbo." & $tmpTableName & " FROM " & $db & ".dbo." & $tablename & @CRLF
$sql = $sql & "DROP TABLE " & $db & ".dbo." & $tablename  & @CRLF
$sql = $sql & "SELECT * INTO " & $db & ".dbo." & $tablename & " FROM " & $db & ".dbo." & $tmpTableName & " ORDER BY Module ASC" & @CRLF
$sql = $sql & "SELECT * FROM " & $db & ".dbo." & $tablename & @CRLF
$sql = $sql & "DROP TABLE " & $db & ".dbo." & $tmpTableName & @CRLF
$sql = $sql & "COMMIT TRAN DEDUPLICATE" & @CRLF

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 = 0                 ; maximum records returned by query
    $o_ADOrs.Open ($sql, $o_ADOcn)
Endif
Link to comment
Share on other sites
ptrex Universalist

ptrex

Posted
Posted

@

In order to debug you should start adding the COM error checking routine first.

; IN THE BEGINNING OF THE SCRIPT
; Initialize error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; YOUR SCRIPT HERE

; AT THE END OF SCRIPT
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

I hope this gives you some ideas where to start looking for errors.

regards

ptrex

Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM  - New

Link to comment
Share on other sites
WebRaider Seeker

WebRaider

Posted
  • Author
Posted

@

In order to debug you should start adding the COM error checking routine first.

; IN THE BEGINNING OF THE SCRIPT
; Initialize error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; YOUR SCRIPT HERE

; AT THE END OF SCRIPT
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

I hope this gives you some ideas where to start looking for errors.

regards

ptrex

hmm, I still get nothing. Not even any errors from what you gave me. I think I am going to create the same thing in vb script:

; ----------------------------------------------------------------------------
; AutoIt Version: 3.1.0
; Author:        A.N.Other <myemail@nowhere.com>
; Script Function:
;   Template AutoIt script.
; ----------------------------------------------------------------------------
; Script Start - Add your code below here
#include <GUIConstants.au3>
#include <Array.au3>
#include <String.au3>
#include <MSsql.au3>
#include <C:\Program Files\AutoIt3\Include\SpecPro\SpecPro_Functions.au3>

Dim $StartTime, $Label2a, $Label4a, $Label2, $Label4, $progressbar1, $progressbar2, $progressbar3, $Value3, $Label6

Global $RowRecord[100000]
Global $SaveRow[100000]
Global $DataCodeRecordSet[100000]
Global $ReportDataCodes[100000][10]
Global $RowRecordCount
Dim $Path
Dim $FileName
Dim $FileList[1000]
Dim $Excluded[1000]
Dim $loadingCount

; Post File Ext.
Dim $PostFileName
$PostFileName = "_-999"

Dim $refs[100]

; IN THE BEGINNING OF THE SCRIPT
; Initialize error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$today = @YEAR & @MON & @MDAY
$now = $today & "_" & @HOUR & @MIN &@SEC
$StartTime = @MON & "/" & @MDAY & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC

$Dirs = StringSplit(@ScriptDir,"\")
$ScriptDir = $Dirs[UBound($Dirs) - 1]
$ScriptDirLen = StringLen($ScriptDir)
$pos = StringInStr(@ScriptDir,$ScriptDir)
$RootDir = StringMid(@ScriptDir,1,$pos - 2)

HotKeySet("{ESC}", "CtrlAltx")
Func CtrlAltx()
    Exit
EndFunc

;SQL SpecPro Test Database
    Dim $o_ADOcn
;Dim $dsn = "SpecPro"
;Dim $db = "SpecProAutoTest"

    GetRegInfo()
;MsgBox(0,"$GetRegInfo",$GetRegInfo); ***********************
    $GetRegInfo = RegRead("HKLM\SOFTWARE\Freightliner\SpecProDataAutoTest","dsn") & "|"
    $GetRegInfo = $GetRegInfo & RegRead("HKLM\SOFTWARE\Freightliner\SpecProDataAutoTest","database")
    $split = StringSplit($GetRegInfo,"|")
    Dim $dsn = $split[1]
    Dim $db = $split[2]
    
    If Check4DSN($dsn,$db) Then End()
    If Check4DB_Only($dsn,$db) Then
        End()
    Else
    ;MsgBox(0,"","SpecProAutoTest already exists!")
    EndIf

ExcludeList()

SetupINI($RootDir);$refs[1] = MultiFileDir : $refs[2] = refs

; ******* Core Proccess
; Check for Multi File
    $search = "%_SpecPro_Options"
    $TablesTestList = GetTableList($dsn,$db,$search)
    
;_ArrayDisplay($TablesTestList,"$TablesTestList")
    
    StatusWindow(); $StartTime, $Label2a, $Label4a, $Label2, $Label4, $Label5, $Label6, $progressbar1, $progressbar2, $progressbar3 Get Updated
    GUICtrlSetData ($Label4a,UBound($TablesTestList))
    
; Set Progress Multiplier 
    $progressMultiplier = 100/UBound($TablesTestList)

    For $f = 1 to UBound($TablesTestList) - 1
        GUICtrlSetData ($progressbar1,$f * $progressMultiplier)
        GUICtrlSetData ($Label2a,$f)
        GUICtrlSetData ($Label6,$TablesTestList[$f])
        
        $TableName = StringMid($TablesTestList[$f],1,StringInStr($TablesTestList[$f],$search)-1)
        
        $TableName =  $TableName & "_Modules"
        $fieldnames = "Module varchar(3)"
        CreateTable($dsn,$db,$TableName,$fieldnames)
        InsertModuleCodes($dsn,$db,$TableName,$TablesTestList[$f])
        RemoveDups($dsn,$db,$TableName)

        GUICtrlSetData($progressbar1,$f * Int($progressMultiplier/2))
        
    ;GetDataFromFile($Path & $TablesTestList[$f]); ************************************
    ;ProcessData(); ************************************
        
        $TableName = StringMid($TablesTestList[$f],1,StringLen($TablesTestList[$f]) - 4) & $PostFileName & ".log"
    ;SaveFile($TableName)
    Next
    GUICtrlSetData($progressbar1,100)
    GUIDelete()
; ******* Core Proccess
; ***** Exit
Exit
; *********************
; ***** Functions *****

; Save File
Func SaveFile($FileName)
    $file = FileOpen(@ScriptDir & "\" & $FileName, 2)
; Check if file opened for writing OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
        For $row = 2 to $SaveRow[0]
            If $SaveRow[$row] <> "" Then
                FileWriteLine($file,$SaveRow[$row])
            EndIf
        Next
    FileClose($file)
EndFunc

; Get Table List
Func GetTableList($dsn,$db,$search)
    Local $TableList[10000]
    $sql = "select name from " & $db & ".dbo.sysobjects WHERE name LIKE " & Chr(39) & $search & Chr(39)
    ClipPut($sql)
    $o_ADOrs = _SQLQuery($o_ADOcn,$sql)
    If $o_ADOrs = 0 Then
        msgbox(0,"Error","")
        _SQLClose($o_ADOcn)
        Return 1
    EndIf
    $x_1 = 1
    While 1
        $row = _SQLGetRow($o_ADOrs)
        If @error Then ExitLoop
        If StringInStr($row,"|") > 0 Then $row = StringMid($row,1,StringInStr($row,"|")-1)
        $TableList[$x_1] = $row
        $x_1 = $x_1 + 1 
    WEnd
    $TableList[0] = $x_1
    ReDim $TableList[$TableList[0]]
    Return $TableList
EndFunc

; Insert Data in to Module Table
Func InsertModuleCodes($dsn,$db,$OutTableName,$InTableName)
    $sql = "SELECT [Data Code] FROM " & $db & ".dbo." & $InTableName
    ClipPut($sql)
    $o_ADOrs = _SQLQuery($o_ADOcn,$sql)
    If $o_ADOrs = 0 Then
        msgbox(0,"Error","")
        _SQLClose($o_ADOcn)
        Return 1
    EndIf
    
    $text = RecordCount($o_ADOrs,"1")
    $ProgressBar_Multiplier = 100/$text
    GUICtrlSetData($progressbar3,0)
    GUICtrlSetData($Label4,$text)
    
    $x_1 = 1
    While 1
        $row = _SQLGetRow($o_ADOrs)
        If @error Then ExitLoop
        $row = StringMid($row,1,3)
        $sql = "INSERT INTO " & $db & ".dbo." & $OutTableName & " (Module) VALUES('" & $row & "')"
        ClipPut($sql)
        $o_ADOrs2 = _SQLQuery($o_ADOcn,$sql)
        If $o_ADOrs2 = 0 Then
            msgbox(0,"Error","")
            _SQLClose($o_ADOcn)
            Return 1
        EndIf
        $x_1 = $x_1 + 1
        GUICtrlSetData($Label2,$x_1)
        GUICtrlSetData($progressbar3,$ProgressBar_Multiplier*$x_1)
    WEnd
EndFunc

; Creates a table in a database
Func CreateTable($dsn,$db,$TableName,$fieldnames)
    $sql = "select name from " & $db & ".dbo.sysobjects WHERE name=" & Chr(39) & $tablename & Chr(39)
    ClipPut($sql)
;MsgBox(0,"ERROR",$sql)
    $o_ADOrs = _SQLQuery($o_ADOcn,$sql)
    If $o_ADOrs = 0 Then
        msgbox(0,"Error","")
        _SQLClose($o_ADOcn)
        Return 1
    EndIf
    $fields = StringSplit(_SQLGetRow($o_ADOrs),"|")
    If $fields[1] <> $tablename Then
        $sql = "CREATE TABLE " & $db & ".dbo." & $tablename & " (" & $fieldnames & ")"
        LoadingAnim()
        ClipPut($sql)
        $o_ADOrs2 = _SQLQuery($o_ADOcn,$sql)
        If $o_ADOrs2 = 0 Then
            msgbox(0,"Error","")
            _SQLClose($o_ADOcn)
            Return 1
        Else
            Return 0
        EndIf
    Else
        $sql = "DROP TABLE " & $db & ".dbo." & $tablename
        LoadingAnim()
        ClipPut($sql)
        $o_ADOrs2 = _SQLQuery($o_ADOcn,$sql)
        If $o_ADOrs2 = 0 Then
            msgbox(0,"Error","")
            _SQLClose($o_ADOcn)
            Return 1
        EndIf
        CreateTable($dsn,$db,$TableName,$fieldnames)
    EndIf
EndFunc

Func RemoveDups($dsn,$db,$tablename)
    $tmpTableName = "Cleaned"
;$sql = ""
;$sql = "BEGIN TRAN DEDUPLICATE"
;Query($sql)
;$sql = "SELECT DISTINCT * INTO " & $db & ".dbo." & $tmpTableName & " FROM " & $db & ".dbo." & $tablename
;Query($sql)
;$sql = "DROP TABLE " & $db & ".dbo." & $tablename 
;Query($sql)
;$sql = "SELECT * INTO " & $db & ".dbo." & $tablename & " FROM " & $db & ".dbo." & $tmpTableName & " ORDER BY Module ASC"
;Query($sql)
;$sql = "SELECT * FROM " & $db & ".dbo." & $tablename
;Query($sql)
;$sql = "DROP TABLE " & $db & ".dbo." & $tmpTableName
;Query($sql)
;$sql = "COMMIT TRAN DEDUPLICATE"
    
    $sql = ""
    $sql = $sql & "BEGIN TRAN DEDUPLICATE" & @CRLF
    $sql = $sql & "SELECT DISTINCT * INTO " & $db & ".dbo." & $tmpTableName & " FROM " & $db & ".dbo." & $tablename & @CRLF
    $sql = $sql & "DROP TABLE " & $db & ".dbo." & $tablename  & @CRLF
    $sql = $sql & "SELECT * INTO " & $db & ".dbo." & $tablename & " FROM " & $db & ".dbo." & $tmpTableName & " ORDER BY Module ASC" & @CRLF
    $sql = $sql & "SELECT * FROM " & $db & ".dbo." & $tablename & @CRLF
    $sql = $sql & "DROP TABLE " & $db & ".dbo." & $tmpTableName & @CRLF
    $sql = $sql & "COMMIT TRAN DEDUPLICATE" & @CRLF
    Query($sql)
EndFunc
Func Query($sql)
;ClipPut($sql);************************************************************************************
;MsgBox(0,"$sql",$sql)
;$o_ADOrs = _SQLQuery($o_ADOcn,$sql)
;If $o_ADOrs = 0 Then
;   msgbox(0,"Error","")
;   _SQLClose($o_ADOcn)
;   Return 1
;EndIf
    
    ClipPut($sql);************************************************************************************
    MsgBox(0,"$sql",$sql)
    
    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 = 0                 ; maximum records returned by query
        $o_ADOrs.Open ($sql, $o_ADOcn)
    Endif
EndFunc

; Exclude List
Func ExcludeList()
    $msg = ""
    $file = FileOpen(@ScriptDir & "\" & StringMid(@ScriptName,1,StringLen(@ScriptName) - 4) & ".ini", 0)
    ; Check if file opened for writing OK
        If $file = -1 Then
            MsgBox(0, "Error", "Unable to open file.")
            Exit
        EndIf
        $row = 1
        While 1
            $line = FileReadLine($file)
            If @error = -1 Then ExitLoop
            If StringInStr($line,"Exclude=") > 0 And StringUpper(StringMid($line,1,1)) = "E" Then
                $Excluded[$row] = StringUpper(StringMid($line,9,255))
                $msg = $msg & ":" & $Excluded[$row] & ":" & @CR
                $row = $row + 1
            EndIf
        WEnd
        $Excluded[0] = $row - 1
    FileClose($file)
EndFunc
; Exclude
Func Exclude($ExcludeCheck)
    $Exclude = False
    For $row = 1 To $Excluded[0]
        If StringMid($Excluded[$row],1,StringLen($Excluded[$row])) = StringMid($ExcludeCheck,1,StringLen($Excluded[$row])) Then
            $Exclude = True
        EndIf
    Next
    Return $Exclude
EndFunc

Func StatusWindow(); $StartTime, $Label2a, $Label4a, $Label2, $Label4, $Label5, $Label6, $progressbar1, $progressbar2, $progressbar3 Get Updated
    GUICreate("Progress",420,150, -1,-1,$WS_POPUPWINDOW)
    $Label1a = GUICtrlCreateLabel(@ScriptName & " Started: " & $StartTime,10,10,400,15)
    $Label1a = GUICtrlCreateLabel("File:",10,30,25,15)
    $Label2a = GUICtrlCreateLabel("",40,30,30,15)       ;Gets Updated
    $Label3a = GUICtrlCreateLabel("of",75,30,10,15)
    $Label4a = GUICtrlCreateLabel("",90,30,30,15)       ;Gets Updated
    $Value3 = GUICtrlCreateLabel("Loading...",260,30,150,15)
    $progressbar1 = GUICtrlCreateProgress (10,50,400,10);Gets Updated
    $Label1 = GUICtrlCreateLabel("Row:",10,70,25,15)
    $Label2 = GUICtrlCreateLabel("",40,70,30,15)        ;Gets Updated
    $Label3 = GUICtrlCreateLabel("of",75,70,10,15)
    $Label4 = GUICtrlCreateLabel("",90,70,30,15)        ;Gets Updated
    $progressbar2 = GUICtrlCreateProgress (10,90,400,10);Gets Updated
    $progressbar3 = GUICtrlCreateProgress (10,110,400,10);Gets Updated
; Display Current File Name
    $Label5 = GUICtrlCreateLabel("Current File:",10,130,65,15)
    $Label6 = GUICtrlCreateLabel("",70,130,335,15)      ;Gets Updated
    GUISetState ()
EndFunc

Func LoadingAnim()
If $loadingCount > 1000 Then $loadingCount = 1
    Select
        Case $loadingCount = 0
            $showDots = ""
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 100 
            $showDots = "   ."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 200 
            $showDots = "   .."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 300 
            $showDots = "   ..."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 400 
            $showDots = "   o.."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 500 
            $showDots = "   Oo."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 600 
            $showDots = "   oOo"
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 700 
            $showDots = "   .oO"
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 800 
            $showDots = "   ..o"
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 900 
            $showDots = "   ..."
            GUICtrlSetData($Value3,"Loading" & $showDots)
        Case $loadingCount = 1000 
            $showDots = "    .."
            GUICtrlSetData($Value3,"Loading" & $showDots)
    EndSelect                   
    $loadingCount = $loadingCount + 1   
EndFunc

Func SetupINI($RootDir)
    $file = FileOpen($RootDir & "\setup.ini", 0)
    ; Check if file opened for writing OK
        If $file = -1 Then
            MsgBox(0, "Error", "Unable to open file. setup.ini")
            Exit
        EndIf
        $row = 1
        While 1
            $line = FileReadLine($file)
            If @error = -1 Then ExitLoop
            If StringInStr($line,"MultiFileDir=") > 0 And StringUpper(StringMid($line,1,1)) = "M" Then
                $refs[1] = StringStripWS(StringMid($line,StringInStr($line,"=") + 1,StringLen($line) + (StringInStr($line,"=") + 1)),3)
                $row = $row + 1
            EndIf
            If StringInStr($line,"refs=") > 0 And StringUpper(StringMid($line,1,1)) = "r" Then
                $refs[2] = StringStripWS(StringMid($line,StringInStr($line,"=") + 1,StringLen($line) + (StringInStr($line,"=") + 1)),3)
                $row = $row + 1
            EndIf
        WEnd
    FileClose($file)
    Return $refs
EndFunc


; AT THE END OF SCRIPT
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"     & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc
Link to comment
Share on other sites
WebRaider Seeker

WebRaider

Posted
  • Author
Posted

You've included this file MSsql.au3 in your script.

I searched for it but could not find it.

Can you please direct me to where you got it.

Thanks

I assume you did not mean MYsql.au3??

hmm, I can't find it anymore. Maybe whoever created had some of the same problems I am having and removed it. If you want a copy I could add the contents of the file here. It'll have to be when I get back to work in the morning, I thought I had a copy here at home.

Anyway, I changed the way I was doing what I need to do and got it to work and way faster than that code worked in another script.

Anyway, I'll check to see if you want that file in the morning.

Thanks

Chuck

Link to comment
Share on other sites
WebRaider Seeker

WebRaider

Posted
  • Author
Posted

hmm, I can't find it anymore. Maybe whoever created had some of the same problems I am having and removed it. If you want a copy I could add the contents of the file here. It'll have to be when I get back to work in the morning, I thought I had a copy here at home.

Anyway, I changed the way I was doing what I need to do and got it to work and way faster than that code worked in another script.

Anyway, I'll check to see if you want that file in the morning.

Thanks

Chuck

Here's the contents of MSsql.au3:

; ------------------------------------------------------------------------------
;
; 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[10000], $s_Nm, $i
    $i = 1
    While 1
        $var = RegEnumVal($s_RegEntry, $i)
        If @error <> 0 then ExitLoop
        $s_Entries[$i] =  $var
        $i = $i + 1
    WEnd
    $s_Entries[0] = $i
    ReDim $s_Entries[$s_Entries[0]]
    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 )
    ClipPut($s_Query);************************************************************************************
    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
Link to comment
Share on other sites
1905russell Prodigy

1905russell

Posted
Posted

Here's the contents of MSsql.au3:

Thanks but it does not help because SpecPro_Functions.au3 is also included and that's not available either.

Anyway now that you have reworked your code can we see it please?

I have also been having ADO\SQL problems but am presently in the thick of my heavy tax season and can't attend to it till end of April (in Canada).

Cheers

Russell

Link to comment
Share on other sites
  • 3 months later...

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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...