WebRaider Posted March 14, 2007 Share Posted March 14, 2007 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 DEDUPLICATEThe 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 EndFuncMy 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 More sharing options...
ptrex Posted March 15, 2007 Share Posted March 15, 2007 @ 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 More sharing options...
WebRaider Posted March 15, 2007 Author Share Posted March 15, 2007 @ 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: expandcollapse popup; ---------------------------------------------------------------------------- ; 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 More sharing options...
1905russell Posted March 15, 2007 Share Posted March 15, 2007 I got one of the MSsql.au3 from some where here.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.ThanksI assume you did not mean MYsql.au3?? Link to comment Share on other sites More sharing options...
WebRaider Posted March 16, 2007 Author Share Posted March 16, 2007 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.ThanksI 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.ThanksChuck Link to comment Share on other sites More sharing options...
WebRaider Posted March 16, 2007 Author Share Posted March 16, 2007 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: 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[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 More sharing options...
1905russell Posted March 16, 2007 Share Posted March 16, 2007 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).CheersRussell Link to comment Share on other sites More sharing options...
raezz Posted July 12, 2007 Share Posted July 12, 2007 Hi, try CursorType = 1. Maybe this helps. Rggs, raezz Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now