PsaltyDS Posted February 4, 2009 Posted February 4, 2009 (edited) I'm running this script as an experiment in scripting queries on an MSSQL 2005 server: CODE; Declare COM Object error handler: Global $oComError = ObjEvent("AutoIt.Error", "_ComErrFunc") ; Get connection to Master DB in MSSQL $sSQLServer = "(local)" $sDatabase = "Master" $oSqlConn = ObjCreate("ADODB.Connection") $oSqlConn.ConnectionString = "DRIVER={SQL Server}" & _ ";SERVER=" & $sSQLServer & _ ";DATABASE=" & $sDatabase & ";" $oSqlConn.Open ; List logins $oRS = ObjCreate("ADODB.RecordSet") $sQuery = "EXEC sp_helplogins" $oRS.Open($sQuery, $oSqlConn, 2) ; 2 = adOpenDynamic $iCnt = $oRS.RecordCount $sResults = "$oRS.RecordCount = " & $iCnt & @CRLF & @CRLF ; $sResults &= $oRS.GetString() & @CRLF & @CRLF With $oRS $iRec = 0 While Not .EOF $iFields = .Fields.Count $sResults &= "Record: " & $iRec & " Field: LoginName = " & .Fields.Item("LoginName" ).Value & @CRLF .MoveNext $iRec += 1 WEnd EndWith $oSqlConn.Close ; Display results $PID = Run("Notepad.exe") WinWait("Untitled - Notepad") $hWin = WinGetHandle("Untitled - Notepad") ControlSetText($hWin, "", "[CLASS:Edit; INSTANCE:1]", $sResults) ;-------------------------------------- ; Function _ComErrFunc() ; Custom COM object error handler ;-------------------------------------- Func _ComErrFunc() Local $HexNumber = Hex($oComError.number, 8) MsgBox(16, "AutoIT COM Error Occured!", _ "Error Number: " & $HexNumber & @CRLF & _ "Line Number: " & $oComError.scriptline & @CRLF & _ "Description: " & $oComError.description & @CRLF & _ "WinDescription: " & $oComError.windescription) SetError(1); something to check for when this function returns EndFunc ;==>_ComErrFunc It works perfectly except for one thing: $oRS.RecordCount = -1 every time (the actual count is 28 and they are correctly displayed when enumerated). I got excited when I found this: Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor. Note: The Recordset object must be open when calling this property. If this property is not supported it will return -1. I tried every option available for cursor type and I still get -1. Is there a way to tell if it's really not supported, or is my usage just wrong? Edited February 4, 2009 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Authenticity Posted February 5, 2009 Posted February 5, 2009 (edited) Hmm, so why don't you open it first adOpenKeyset, adLoadOptimistic to get the RecordCount or perhaps use the slow way like counter and Do...Until $RecordSet.EOF or something... typo... Edited February 5, 2009 by Authenticity
PsaltyDS Posted February 5, 2009 Author Posted February 5, 2009 Hmm, so why don't you open it first adOpenKeyset, adLoadOptimistic to get the RecordCount or perhaps use the slow way like counter and Do...Until $RecordSet.EOF or something...typo...Look at the code. That's exactly what $iRec is doing. That's how I know there are actually 28 records.I want .RecordCount if its available, as in -- can you tell from a schema check or some other documentation if it is valid in context?All four CursorTypeEnum Values (0 thru 3) were tried. The code posted has 2 (adOpenDynamic), which is the only one that explicitly says: ...all types of movement through the Recordset are allowed Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Spiff59 Posted February 5, 2009 Posted February 5, 2009 RecordCount works for me banging on an Advantage Server Database via ADO. I can also get the same number by using "$iCnt = $oRS.Fields(0).value". Can't say what that'll do to MSSQL.
ResNullius Posted February 5, 2009 Posted February 5, 2009 (edited) Is there a way to tell if it's really not supported, or is my usage just wrong?Check out the ADO "Supports" method: http://www.w3schools.com/ado/met_rs_supports.aspThat should let you know Edit: If it's not supported, a quicker workaround then moving through the recordset and counting each record until EOF maybe to use the ADO GetRows() method ( http://www.w3schools.com/ado/met_rs_getrows.asp ) and grab the count from the resulting array. Edited February 5, 2009 by ResNullius
PsaltyDS Posted February 5, 2009 Author Posted February 5, 2009 Check out the ADO "Supports" method: http://www.w3schools.com/ado/met_rs_supports.asp That should let you know Ooh... good one! Thanks for the reference. I don't see .RecordCount as one of the things you can test with that, but I can ensure the other cursor types are valid anyway. I also found a work-around in .GetRows, as I just get all the data in a 2D array, and I hope I already know how to use Ubound! The demo now gets the whole result in one string with .GetString(), then requeries and gets it all in a 2D array with .GetRows(): expandcollapse popup#include <Array.au3> ; Declare COM Object error handler: Global $oComError = ObjEvent("AutoIt.Error", "_ComErrFunc") Global $sSQLServer, $sDatabase, $oSqlConn, $oRS, $sResults, $avResults, $PID, $hWin ; Get connection to Master DB in MSSQL $sSQLServer = "(local)" $sDatabase = "Master" $oSqlConn = ObjCreate("ADODB.Connection") $oSqlConn.ConnectionString = "DRIVER={SQL Server}" & _ ";SERVER=" & $sSQLServer & _ ";DATABASE=" & $sDatabase & ";" $oSqlConn.Open ; Query for logins $oRS = ObjCreate("ADODB.RecordSet") $sQuery = "EXEC sp_helplogins" $oRS.Open($sQuery, $oSqlConn, 2); 2 = adOpenDynamic ; List all records as a string $sResults &= $oRS.GetString() & @CRLF & @CRLF $PID = Run("Notepad.exe") WinWait("Untitled - Notepad") $hWin = WinGetHandle("Untitled - Notepad") ControlSetText($hWin, "", "[CLASS:Edit; INSTANCE:1]", $sResults) ; Requery and list all records again as a 2D array $oRS.ReQuery() $avResults = $oRS.GetRows() _ArrayDisplay($avResults, "$avResults") $oSqlConn.Close ;-------------------------------------- ; Function _ComErrFunc() ; Custom COM object error handler ;-------------------------------------- Func _ComErrFunc() Local $HexNumber = Hex($oComError.number, 8) MsgBox(16, "AutoIT COM Error Occured!", _ "Error Number: " & $HexNumber & @CRLF & _ "Line Number: " & $oComError.scriptline & @CRLF & _ "Description: " & $oComError.description & @CRLF & _ "WinDescription: " & $oComError.windescription) SetError(1); something to check for when this function returns EndFunc ;==>_ComErrFunc Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
ResNullius Posted February 5, 2009 Posted February 5, 2009 Ooh... good one! Thanks for the reference. I don't see .RecordCount as one of the things you can test with that, but I can ensure the other cursor types are valid anyway.I also found a work-around in .GetRows, as I just get all the data in a 2D array, and I hope I already know how to use Ubound! Walk away from the computer for a few minutes, come and edit my post to mention GetRows(), post the edit and lo and behold the venerable old bird has already taken that flightpath
ChrisL Posted February 5, 2009 Posted February 5, 2009 No help what so ever but I had the same issue here http://www.autoitscript.com/forum/index.php?showtopic=51952 and I used GetRows() in the end too! [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire
DaRam Posted February 5, 2009 Posted February 5, 2009 (edited) Have you tried this combination? With $oRS .CursorLocation = 3; UseClient .CursorType = 2; OpenDynamic .LockType = 2; LockPessimistic .Open $sQuery, $oSqlConn Msgbox(0, "RecordCount", $oRS.RecordCount()) End With If that does not work and you don't mind the overhead, you could dump the recordset to disk and reopen to find the .reccount working. After $oRS.Open... $TmpFile = "C:\MSSQL.ADG" If FileExists($TmpFile) Then FileDelete($TmpFile) $oRS.Save $TmpFile, 0; 0=Adv. Data table Gram Fmt, 1=XML $oRS = ObjCreate("ADODB.RecordSet") $oRS.Open $TmpFile Msgbox(0, "RecordCount", $oRS.RecordCount()) No help what so ever but I had the same issue here http://www.autoitscript.com/forum/index.php?showtopic=51952 and I used GetRows() in the end too!Edit: Corrected RecCount to RecordCount Edited February 5, 2009 by DaRam
PsaltyDS Posted February 5, 2009 Author Posted February 5, 2009 Here was my experiment with .Supports: expandcollapse popup#include <Array.au3> ; Declare COM Object error handler: Global $oComError = ObjEvent("AutoIt.Error", "_ComErrFunc") Global $sSQLServer, $sDatabase, $oSqlConn, $oRS, $sResults, $PID, $hWin ; Get connection to Master DB in MSSQL $sSQLServer = "(local)" $sDatabase = "Master" $oSqlConn = ObjCreate("ADODB.Connection") $oSqlConn.ConnectionString = "DRIVER={SQL Server}" & _ ";SERVER=" & $sSQLServer & _ ";DATABASE=" & $sDatabase & ";" $oSqlConn.Open ; Query for logins $oRS = ObjCreate("ADODB.RecordSet") $sQuery = "EXEC sp_helplogins" ; Cursor types: Global $avCursors[5][2] = [[-1, "adOpenUnspecified"], _ [0, "adOpenForwardOnly (default)"], _ [1, "adOpenKeyset"], _ [2, "adOpenDynamic"], _ [3, "adOpenStatic"]] ; Cursor options: Global $avOptions[13][2] = [[0x100, "adHoldRecords"], _ [0x200, "adMovePrevious"], _ [0x2000, "adBookmark"], _ [0x4000, "adApproxPositin"], _ [0x10000, "adUpdateBatch"], _ [0x20000, "adResync"], _ [0x40000, "adNotify"], _ [0x80000, "adFind"], _ [0x100000, "adIndex"], _ [0x200000, "adSeek"], _ [0x1000400, "adAddNew"], _ [0x1000800, "adDelete"], _ [0x1008000, "adUpdate"]] $sResults = "" For $c = 0 To UBound($avCursors) - 1 ; Try each cursor type: $oRS.Open($sQuery, $oSqlConn, $avCursors[$c][0]) $sResults &= "Cursor type " & $avCursors[$c][0] & ": " & $avCursors[$c][1] & @CRLF ; List supported cursor options For $n = 0 To UBound($avOptions) - 1 If $oRS.Supports($avOptions[$n][0]) Then $iSupported = "Yes" Else $iSupported = "No" EndIf $sResults &= @TAB & "Option 0x" & Hex($avOptions[$n][0], 8) & ": " & $avOptions[$n][1] & " = " & $iSupported & @CRLF Next $oRS.Close $sResults &= @CRLF Next $oSqlConn.Close ; Display results $PID = Run("Notepad.exe") WinWait("Untitled - Notepad") $hWin = WinGetHandle("Untitled - Notepad") ControlSetText($hWin, "", "[CLASS:Edit; INSTANCE:1]", $sResults) ;-------------------------------------- ; Function _ComErrFunc() ; Custom COM object error handler ;-------------------------------------- Func _ComErrFunc() Local $HexNumber = Hex($oComError.number, 8) MsgBox(16, "AutoIT COM Error Occured!", _ "Error Number: " & $HexNumber & @CRLF & _ "Line Number: " & $oComError.scriptline & @CRLF & _ "Description: " & $oComError.description & @CRLF & _ "WinDescription: " & $oComError.windescription) SetError(1); something to check for when this function returns EndFunc ;==>_ComErrFunc Here are the results: expandcollapse popupCursor type -1: adOpenUnspecified Option 0x00000100: adHoldRecords = No Option 0x00000200: adMovePrevious = No Option 0x00002000: adBookmark = No Option 0x00004000: adApproxPositin = No Option 0x00010000: adUpdateBatch = No Option 0x00020000: adResync = No Option 0x00040000: adNotify = Yes Option 0x00080000: adFind = Yes Option 0x00100000: adIndex = No Option 0x00200000: adSeek = No Option 0x01000400: adAddNew = No Option 0x01000800: adDelete = No Option 0x01008000: adUpdate = No Cursor type 0: adOpenForwardOnly (default) Option 0x00000100: adHoldRecords = No Option 0x00000200: adMovePrevious = No Option 0x00002000: adBookmark = No Option 0x00004000: adApproxPositin = No Option 0x00010000: adUpdateBatch = No Option 0x00020000: adResync = No Option 0x00040000: adNotify = Yes Option 0x00080000: adFind = Yes Option 0x00100000: adIndex = No Option 0x00200000: adSeek = No Option 0x01000400: adAddNew = No Option 0x01000800: adDelete = No Option 0x01008000: adUpdate = No Cursor type 1: adOpenKeyset Option 0x00000100: adHoldRecords = No Option 0x00000200: adMovePrevious = Yes Option 0x00002000: adBookmark = Yes Option 0x00004000: adApproxPositin = No Option 0x00010000: adUpdateBatch = No Option 0x00020000: adResync = Yes Option 0x00040000: adNotify = Yes Option 0x00080000: adFind = Yes Option 0x00100000: adIndex = No Option 0x00200000: adSeek = No Option 0x01000400: adAddNew = No Option 0x01000800: adDelete = No Option 0x01008000: adUpdate = No Cursor type 2: adOpenDynamic Option 0x00000100: adHoldRecords = No Option 0x00000200: adMovePrevious = Yes Option 0x00002000: adBookmark = No Option 0x00004000: adApproxPositin = No Option 0x00010000: adUpdateBatch = No Option 0x00020000: adResync = No Option 0x00040000: adNotify = Yes Option 0x00080000: adFind = Yes Option 0x00100000: adIndex = No Option 0x00200000: adSeek = No Option 0x01000400: adAddNew = No Option 0x01000800: adDelete = No Option 0x01008000: adUpdate = No Cursor type 3: adOpenStatic Option 0x00000100: adHoldRecords = No Option 0x00000200: adMovePrevious = Yes Option 0x00002000: adBookmark = Yes Option 0x00004000: adApproxPositin = No Option 0x00010000: adUpdateBatch = No Option 0x00020000: adResync = No Option 0x00040000: adNotify = Yes Option 0x00080000: adFind = Yes Option 0x00100000: adIndex = No Option 0x00200000: adSeek = No Option 0x01000400: adAddNew = No Option 0x01000800: adDelete = No Option 0x01008000: adUpdate = No Nothing in there tells me if the .RecordCount property is valid for this recordset, but it was very educational anyway. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
PsaltyDS Posted February 5, 2009 Author Posted February 5, 2009 Have you tried this combination? With $oRS .CursorLocation = 3; UseClient .CursorType = 2; OpenDynamic .LockType = 2; LockPessimistic .Open $sQuery, $oSqlConn Msgbox(0, "RecCount", $oRS.Reccount()) End With If that does not work and you don't mind the overhead, you could dump the recordset to disk and reopen to find the .reccount working. After $oRS.Open... $TmpFile = "C:\MSSQL.ADG" If FileExists($TmpFile) Then FileDelete($TmpFile) $oRS.Save $TmpFile, 0; 0=Adv. Data table Gram Fmt, 1=XML $oRS = ObjCreate("ADODB.RecordSet") $oRS.Open $TmpFile Msgbox(0, "RecCount", $oRS.Reccount()) No, I didn't. Thanks for the suggestion. Is .Reccount a valid alias for .RecordCount in ADO, or is that because you have a different client version...? Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
DaRam Posted February 5, 2009 Posted February 5, 2009 .RecordCount is correct (.Reccount a typo, sorry about that). Correcting the code...No, I didn't. Thanks for the suggestion. Is .Reccount a valid alias for .RecordCount in ADO, or is that because you have a different client version...?
ResNullius Posted February 5, 2009 Posted February 5, 2009 No help what so ever but I had the same issue here http://www.autoitscript.com/forum/index.php?showtopic=51952 and I used GetRows() in the end too!Another little trick is to use the COUNT() function: SQL statement as follows SELECT COUNT(1) FROM MyRecordSet
PsaltyDS Posted February 5, 2009 Author Posted February 5, 2009 Another little trick is to use the COUNT() function: SQL statement as follows SELECT COUNT(1) FROM MyRecordSet Since I am doing the sp_helplogins stored procedure from ADO, that recordset doesn't exist at the SQL query level does it? I tried this, but it doesn't like the syntax because EXEC can't be the source for COUNT: $sQuery = "SELECT COUNT(1) FROM (EXEC sp_helplogins)" $oRS.Open($sQuery, $oSqlConn, 2); 2 = adOpenDynamic I wouldn't mind figuring this out for the sake of my continuing education, but .GetRows() is working beautifully for me for my current purposes. Thanks again for trying to edumacate me! Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
ResNullius Posted February 6, 2009 Posted February 6, 2009 Since I am doing the sp_helplogins stored procedure from ADO, that recordset doesn't exist at the SQL query level does it? I tried this, but it doesn't like the syntax because EXEC can't be the source for COUNT: $sQuery = "SELECT COUNT(1) FROM (EXEC sp_helplogins)" $oRS.Open($sQuery, $oSqlConn, 2); 2 = adOpenDynamic I wouldn't mind figuring this out for the sake of my continuing education, but .GetRows() is working beautifully for me for my current purposes. Thanks again for trying to edumacate me! Sorry Psalty, don't know on stored procedures. But in the past, I can recall that I have opened a RecordSet, then done a SELECT from that into a new RecordSet when I couldn't make things work on the orignal. Probably not the most elegant way, but IIRC it worked for whatever I was trying to do at the time. Also, it appears my COUNT() syntax could be a little bugged for some situations, ideally it should be SELECT COUNT(*) FROM MyTable[code=auto:0] Note the * instead of the 1.
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