Connecting to MS SQL Server and querying a table from a DB

[readmedottxt]

I have a need to query a table from our MS SQL 2005 server,

I'm new to querying SQL however I can get the data I need by performing the following:

Open SQL Management Studio

Navigate to the database called 'EmailSender'

Navigate to the table called 'dbo.EmailSendLog'

Run the following query:

select * from EmailLog where Action like 'Create%'

Then it returns all the rows and columns that I need to process.

I've looked at the following functions

expandcollapse popup

;===============================================================================
;
; Function Name:    _SQLConnect
; Description:      Initiate a connection to a SQL database
; Syntax:           $oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", _
;                       $sDriver = "{SQL Server}")
; Parameter(s):     $sServer - The server your database is on
;                   $sDatabase - Database to connect to
;                   $fAuthMode - Authorization mode (0 = Windows Logon, 1 = SQL) (default = 0)
;                   $sUsername - The username to connect to the database with (default = "")
;                   $sPassword - The password to connect to the database with (default = "")
;                   $sDriver (optional) the ODBC driver to use (default = "{SQL Server}")
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns the connection object for subsequent SQL calls
;                   On failure - returns 0 and sets @error:
;                       @error=1 - Error opening database connection
;                       @error=2 - ODBC driver not installed
;                       @error=3 - ODBC connection failed
; Author(s):        SEO and unknown
; Note(s):          None
;
;===============================================================================
Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")
    Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
    If @error or $sVal = "" Then Return SetError(2, 0, 0)
    $oConn = ObjCreate("ADODB.Connection")
    If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
    If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
    If @error Then Return SetError(1, 0, 0)
    Return $oConn
EndFunc   ;==>_SQLConnect
 
;===============================================================================
;
; Function Name:    _SQLQuery
; Description:      Send a query to a SQL database and return the results as an object
; Syntax:           $oQuery = _SQLQuery($oConn, $sQuery)
; Parameter(s):     $oConn - A database connection object created by a previous call to _SQLConnect
;                   $sQuery - The SQL query string to be executed by the SQL server
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns the query result as an object
;                   On failure - returns 0 and sets @error:
;                       @error=1 - Unable to process the query
; Author(s):        SEO and unknown
; Note(s):          None
;
;===============================================================================
Func _SQLQuery($oConn, $sQuery)
    If IsObj($oConn) Then Return $oConn.Execute($sQuery)
    Return SetError(1, 0, 0)
EndFunc ;==>_SQLQuery
 
;===============================================================================
;
; Function Name:    _SQLDisconnect
; Description:      Disconnect and close an existing connection to a SQL database
; Syntax:           _SQLDisconnect($oConn)
; Parameter(s):     $oConn - A database connection object created by a previous call to _SQLConnect
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns 1 and closes the ODBC connection
;                   On failure - returns 0 and sets @error:
;                       @error=1 - Database connection object doesn't exist
; Author(s):        SEO and unknown
; Note(s):          None
;
;===============================================================================
Func _SQLDisconnect($oConn)
    If NOT IsObj($oConn) Then Return SetError(1, 0, 0)
    $oConn.Close
    Return 1
EndFunc   ;==>_SQLDisconnect

And don't know how to connect to a specific table within a database. This is all I can come up with but it doesn't specify which DB to connect to - we have about 10 DB's on this server.

$oSQL = _SQLConnect("corp-DB04", "EmailSender", 0, "domain\account", "password")

Can anyone guide me or show some examples of how to connect and query this?

Thanks

[llewxam]

Here is how I do it:

Configure the database server IP and login credentials:

Local $ServerAddress = "   INSERT YOUR SQL SERVER IP HERE    "
Local $ServerUserName = "   INSERT YOUR USERNAME HERE      "
Local $ServerPassword = "    INSERT YOUR PASSWORD HERE  "
Local $DatabaseName = "    INSERT YOUR DATABASE NAME HERE   "

Next, start the SQL service:

_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$OADODB = _SQL_Startup()
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $ServerAddress, $DatabaseName, $ServerUserName, $ServerPassword) = $SQL_ERROR Then
MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf

Then you should be all set. To query the database, something like this:

Local $fullSQL
If _Sql_GetTableAsString(-1, "SELECT Brand, Description, UPC, Price, Updated FROM Inventory ORDER BY Brand, Description;", $fullSQL) = $SQL_OK Then
Else
MsgBox(0 + 16 + 262144, "SQL Error", _SQL_GetErrMsg())
EndIf

$fulLSQL will be your string. I slightly modified _sql.au3 to have a different delimiter between lines, but that's just me, the above will at least help you get the server info configured and get ready to query it.

Enjoy!

Ian

Edited September 1, 2011 by llewxam

[hanwar00]

very helpful post... just wondering if there is a way to connect to the SQL server without using IP instead using the server name.

[water]

Just give it a try.

[hanwar00]

i have tried but getting the following error any idea how to go about it?

###############################
err.description is:  [Microsoft][ODBC SQL Server Driver][sql Server]Login failed for user 'peter_t'.
err.windescription: 
err.number is:  80020009
err.lastdllerror is:  0
err.scriptline is:  220
err.source is:  Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:  
err.helpcontext is:  0###############################
###############################
err.description is:  Operation is not allowed when the object is closed.
err.windescription: 
err.number is:  80020009
err.lastdllerror is:  0
err.scriptline is:  373
err.source is:  ADODB.Connection
err.helpfile is:  C:WINDOWSHELPADO270.CHM
err.helpcontext is:  1240653###############################

[Ahile07]

I know is an old post! But. Can anyone help to show me how to write into a database?

I manage to connect to one...pick up some data....put it in some vars and then i want to insert data in a different database. But i don't know how to do it!

[mLipok]

use my ADO UDF and simple SQL statement:
 

....
Local $sQuery = "INSERT INTO [DBNAME].dbo.[TABLENAME]([ColName1],[ColName2],[ColName3]) VALUES('TEST STRING',2,1)"
_ADO_Execute($oConnection, $sQuery, True)
....

 

[jchd]

SQL offers the INSERT INTO ... statement for inserting data. I would recommend googling for a decent tutorial in SQL, preferably focussed onthe RDBMS engine you use.

[Ahile07]

Thank you @mLipok that's the code i am using
 connecting to the database in a different way. Wondering if can write into database this way?!

Func WriteTCard()

;Server ID and credentials for tCards
Global $ServerAddressT = "aa"
Global $ServerUserNameT = "bb"
Global $ServerPasswordT = "cc"
Global $DatabaseNameT = "dd"

;Connect to DB
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$OADODB = _SQL_Startup()
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $ServerAddressT, $DatabaseNameT, $ServerUserNameT, $ServerPasswordT) = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf

Local $sQuery = "INSERT INTO [dummy].[dbo].[tbldDummy]([Owning_Panel], [Created_date], [User_id], [Completed], [Card_Date], [Removed], [Y_Coord], [Colour], [Tee_Start], [Start_Time], [Tee_End], [Tee3], [Tee4], [Tee5], [Tee6], [Tee7], [Tee8], [Tee9], [Tee10], [Tee11], [Tee12], [Tee13], [Tee14], [Tee15], [Tee16], [Tee17], [Tee18], [Tee19], [Tee20]) VALUES('1', GETDATE(), 'tecan1', '0', GETDATE(), '0', '950', '16777215', GETDATE(), GETDATE(), GETDATE(), 'FLOFLO', 'Test2', 'Test3', 'Test4', 'Test5', 'Test6', 'Test7', 'Test8', 'Test9', 'Test10', 'Test11', 'Test12', 'Test13', 'Test14', 'Test15', 'Test16', 'Test17', 'Test18')"


MsgBox(16,"Insert status","Done - new t-card inserted")

EndFunc

i don't want to complicate things...as i don't know to many things about programming

Thank you all

[mLipok]

I suppose that this not all values values are string

so this:

..... GETDATE(), '0', '950', '16777215', GETDATE() .....

should be like this:

..... GETDATE(), 0, 950, 16777215, GETDATE() .....

 

and you should read about COM ERROR Handling with:
 

ObjEvent()

 

[Ahile07]

@mLipok i took out all the apostrophies but's not working.

Also no error received maybe that's why you're asking to read about that. But wondering how i can use it in my case.

[mLipok]

I see you are using this 

_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

But I do not remember how it works (even if it works at all).
This is because I do not use this UDF from more than year, and I'm using my own ADO.au3 (this is strongly rewrited _SQL.au3 UDF ).

 

[Ahile07]

I can't adapt your ado to my code. is to complecated for me

Cheers.

[MuffinMan]

It looks to me like you may be setting up your insert correctly (depending on how your DB is setup), but you are just storing the INSERT in a variable, you haven't executed it yet (at least in the code you've shown).

Go here and take a look at the _SQL.au3 examples...

Especially pay close attention to lines like these:

If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

 

[mLipok]

Good catch @MuffinMan 
Do not know where I had my eyes, .... in OP example from post #9  there is indeed SqlQuery declared by 

Local $sQuery = "INSERT INTO ....

but this is never used with _SQL_Execute() function.