_SQL.au3. ADODB.Connection

[Shrapnel]

Im using SQL Server 2012 using Windows Authentication to log in and I could not get this UDF to work for me.

It took me a while, but i figured out how to get it to work (Thanks to Syed23 for figuring out how to get the UDF to work for SQL Sever 2008, which put me on the right track to find this website http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/ and get it working)

Here is the function i created. I hope that some find this useful

; #FUNCTION# ===================================================================
; Name ..........: _SQL_2012_Connect
; Description ...: Starts a Database Connection
; Syntax.........:  _SQL_2012_Connect($ADODBHandle,$server, $db)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $server   - The server to connect to.
;                  $db    - The database to open.
; Return values .: On Success   - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......: Erik Haspel (Shrapnel)
; Remarks .......: This function only works with SQL Server 2012 using Windows Authentication
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_2012_Connect($ADODBHandle, $server, $db)
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    $ADODBHandle.Open("DRIVER={SQL Server Native Client 11.0};SERVER=" & $server & ";DATABASE=" & $db & ";Trusted_Connection=yes;")

    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_2012_Connect

Edited July 10, 2013 by Shrapnel

[jchd]

This functon won't work as it is.

[Shrapnel]

This functon won't work as it is.

 

jchd, why do you say that, and what do you think should be changed?

Just wondering, because it works for me...

Edited July 11, 2013 by Shrapnel

[jchd]

Glad it actually works for you!

I find it very strange that you inherit and don't Return the connection handle for further use.

Then how do you use the connection after that? Doesn't the Open change the connection object?

I don't use MS SQL myself but ADO connections don't work this way with other engines. (Not that it's any surprising that MS stuff work weird!)

Last question I ask myself is how you would open more than one simultaneous connections.

[Shrapnel]

I just modified Syed23's function () by removing the "If $SQLAuth = True" statement and made it a single line, since I will never have $SQLAuth = false. Syed23's function only changed one line of the original function.

I save the connection handle that is is generated with the _SQL_Startup() function to a variable in my script and pass it to the _SQL_2012_Connect() function. I do not believe that the .open command changes the connection handle, but i have not done extensive testing with this yet (just got it working yesterday )

As for multiple connections, i would assume that you would just use _SQL_Startup() multiple times, saving to a different variable each time? Not sure though. (I will only be connecting to one DB for my script)

[jchd]

Umm, so it seems MS SQL ADO is really awkward from what I understand.

Never mind if that works for you.

[TheBG]

so, I have been using _sql.au3 to capture and record data from various pc's. and now Im just looking to pull data once in while.

I'm seeing something strange.

Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable2D(-1,"SELECT * FROM MigInfo WHERE [User] LIKE 'pwmill%';",$aData,$iRows,$iColumns)
    If $iRval = $SQL_OK then _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

this is straight from the example code. and as you can see I query a table for a specific user. the query pulls all the data, but will not display some of the columns it in the array. (see attachment).

I can run the data to string from the example code, and I receive ALL data. the query does work, it's just wont display in the array.

thought?

 

[jchd]

Where did you get _sql.au3?

[TheBG]

from this forum... 

[jchd]

That isn't a very informative answer. Care to point out where exactly?

[TheBG]

http://www.autoitscript.com/forum/index.php?app=core&module=attach&section=attach&attach_id=33839

the first entry on this thread has DL links. I have the one listed as the new _sql.au3

[jchd]

Strange indeed. I don't see how .getrows would emasculate certain columns just to make you crazy. Dumb question: are you positive that these columns are not NULLs?

Can you issue the same query thru the DB engine manager and check that you actually get data in the empty columns? Which types do they have?

Last possibility: do you use x86 or x64 application and does it behave the same in both modes?

[SnArF]

I can't figure out how to get a value back from _SQL-Execute.

I just want to execute: "DBCC CHECKIDENT (database, NORESEED)" and get the value in a string or array.

Please help, 

[mLipok]

show us how you trying to do that

[SnArF]

Mlipok,

I have used:  $Data = _SQL_Execute(-1,"DBCC CHECKIDENT (database, RESEED, 0)") to set the ID value.

When I use this command, DBCC CHECKIDENT (database, RESEED, 0), in OSQL I get "Checking identity information: current identity value '0',...." This confirms that the command completed successfully.

 $Data = _SQL_Execute(-1,"DBCC CHECKIDENT (database, RESEED, 0)") works but how do I get the confirmation that the command completed successfully?

So in short, how do I get the results from the executed commands in a variable

[mLipok]

Look in _SQL_GetTable2D()

there is an example:

expandcollapse popup

$objquery = _SQL_Execute($hConHandle, $vQuery)
    ;end mod
    If @error Then
        $SQLErr = "Query Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    If $objquery.eof Then
        $SQLErr = "Query has no data"
        $objquery = 0 ;sp mod
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    With $objquery

        $aResult = .GetRows()

        If IsArray($aResult) Then
            $iColumns = UBound($aResult, 2)
            $iRows = UBound($aResult)

            ReDim $aResult[$iRows + 1][$iColumns];Adjust the array to fit the column names and move all data down 1 row

            For $x = $iRows To 1 Step -1
                For $y = 0 To $iColumns - 1
                    $aResult[$x][$y] = $aResult[$x - 1][$y]
                Next
            Next
            ;Add the coloumn names
            For $i = 0 To $iColumns - 1 ;get the column names and put into 0 array element
                $aResult[0][$i] = .Fields($i).Name
            Next

        Else
            $SQLErr = "Unable to retreive data"
            $objquery = 0 ;sp mod
            Return SetError($SQL_ERROR, 0, $SQL_ERROR)
        EndIf;IsArray()

    EndWith

[mLipok]

below are a few interesting links:

http://www.connectionstrings.com/sql-server/

 

http://www.connectionstrings.com/formating-rules-for-connection-strings/

http://www.connectionstrings.com/all-sql-server-connection-string-keywords/

[suthern101]

Note that this simple query produces the "Operation not allowed when object is closed" error using Windows Authentication on MSSQL 

$sSQL = "declare @p11 varchar(255) set @p11='No Error' select @p11"

But when you put SET NOCOUNT ON prior to the declare section, the query works as expected.

$sSQL = "set nocount on declare @p11 varchar(255) set @p11='No Error' select @p11"

Is this a bug in adodb? in _sql.au3?

[mLipok]

This simple query is only a string. Show us how you use it , then we be able to show you where is a isue.

mLipok

[StefanoVR]

Hello everybody! I have exactly same problem of suthern101.

A simple query with a declaration inside didn't work correctly in _SQL.au3 but works perfectly in Sql Server Management studio..  

And really I don't uderstand why.

Function _SQL_GetTable2D end with the error "Unable to retreive data".

This because "$aResult = .GetRows() - > If IsArray($aResult) Then" fails to recognize  $aResult as Array. 

Probably because $aResult = .GetRows() isn't really an array or is closed.. 

Adding set nocount on before the declaration everything works.. 

Query not working 

$sQuery =  "DECLARE @topCount INT SET @topCount = '15' SELECT TOP (@topCount) [FIELDS] FROM (SELECT [FIELDS] FROM [Virtual].[dbo].[VIEW] WHERE [FIELD] > 100  ) t ORDER BY [FIELD] DESC;

Query working 

$sQuery =  "SET NOCOUNT ON DECLARE @topCount INT SET @topCount = '15' SELECT TOP (@topCount) [FIELDS] FROM (SELECT [FIELDS] FROM [Virtual].[dbo].[VIEW] WHERE [FIELD] > 100  ) t ORDER BY [FIELD] DESC;

 

Could you help me to understand where is the error?? 

Thanks in advance