Sign in to follow this  
Followers 0
BigDaddyO

MSDE SQL Database commands

3 posts in this topic

#1 ·  Posted (edited)

I am setting up a MSDE SQL Database and below is some code I am using Specifically with SQL.

The Database Table for testing is setup like so.

Table Name: Test_Logs

Columns: Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail

Column Setup: VarChar(20), VarChar(20), VarChar(20), DateTime, VarChar(10)

The Start_Time column will be the identifier as it should be different for each test.

$Time = @HOUR & ":" & @MIN & ":" & @SEC
;Format Required to import Date into the SQL database
Global $StartDateTime = @YEAR & "/" & @MON & "/" & @MDAY & " " & $Time
;Format Required to search for the Date in the SQL database
Global $PullDateTime = @MON & "/" & @MDAY & "/" & @YEAR & " " & $Time

Dim $1 = "'test1'", $2 = "'HP Vectra'", $3 = "'VectraISO1'", $4 = "'" & $StartDateTime & "'", $5 = "'Passed'"
$SQLFields = "Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail
$SQLData = $1 & ", " & $2 & ", " & $3 & ", " & $4 & ", " & $5 & ", "

;Connecting to the SQL Database:
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; _
              Data Source=ServerIP; _
              User ID=UserName; _
              Password=IDpassword;")

;Insert a new Row with Data into the SQL Database:
$sqlCon.Execute("INSERT INTO Test_Logs(" & $SQLFields & ") Values (" & $SQLData & ")")

;Used to delete rows from the Test_Logs table
$sqlCon.Execute("Delete From Test_Logs where Test_Name = 'test1'")


; This is used to update specific information in a Row of data.  
$sqlCon.execute("Update Test_Logs Set Pass_Fail = 'Passed' Where Start_Time = '" & $PullDateTime & "'")

; Lastly of course close the connection to the database before exiting the script
$sqlCon.close

I am still trying to work on pulling information from the database but as of right now I keep getting Junk returned.

$data = $sqlCon.Execute("Select Start_Time From Test_Logs")

MsgBox (0, "Data", $Data)

This is just showing some eC junk message, If I figure it out, Ill update this post.

EDIT: By looking at the code from ScriptKitty I was able to put this together for pulling data from my SQL Database.

Dim $DataRead
$sqlRs = ObjCreate ("ADODB.Recordset")
$sqlRS.open("Select * From Test_Logs where Start_Time >= '06/27/2005 11:00:00'", $sqlCon);This line allows logical exp[b][/b]ressions for searching out specifics
With $sqlRS
If .RecordCount Then
While Not .EOF
$DataRead = $DataRead & .Fields('Test_Name').Value & " - "
$DataRead = $DataRead & .Fields('Computer_Name').Value & " - " 
$DataRead = $DataRead & .Fields('Image_Name').Value & " - "
$DataRead = $DataRead & .Fields('Start_Time').Value & " - "
$DataRead = $DataRead & .Fields('Pass_Fail').Value & " - "
$DataRead = $DataRead & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$sqlCon.Close
MsgBox(0, "return", $DataRead)

I am starting to really get the hang of this SQL stuff.

Edited by MikeOsdx

Share this post


Link to post
Share on other sites



I am setting up a MSDE SQL Database and below is some code I am using Specifically with SQL.

The Database Table for testing is setup like so.

Table Name:  Test_Logs

Columns: Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail

Column Setup:  VarChar(20), VarChar(20), VarChar(20), DateTime, VarChar(10)

The Start_Time column will be the identifier as it should be different for each test.

$Time = @HOUR & ":" & @MIN & ":" & @SEC
;Format Required to import Date into the SQL database
Global $StartDateTime = @YEAR & "/" & @MON & "/" & @MDAY & " " & $Time
;Format Required to search for the Date in the SQL database
Global $PullDateTime = @MON & "/" & @MDAY & "/" & @YEAR & " " & $Time

Dim $1 = "'test1'", $2 = "'HP Vectra'", $3 = "'VectraISO1'", $4 = "'" & $StartDateTime & "'", $5 = "'Passed'"
$SQLFields = "Test_Name, Computer_Name, Image_Name, Start_Time, Pass_Fail
$SQLData = $1 & ", " & $2 & ", " & $3 & ", " & $4 & ", " & $5 & ", "

;Connecting to the SQL Database:
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; _
              Data Source=ServerIP; _
              User ID=UserName; _
              Password=IDpassword;")

;Insert a new Row with Data into the SQL Database:
$sqlCon.Execute("INSERT INTO Test_Logs(" & $SQLFields & ") Values (" & $SQLData & ")")

;Used to delete rows from the Test_Logs table
$sqlCon.Execute("Delete From Test_Logs where Test_Name = 'test1'")
; This is used to update specific information in a Row of data.  
$sqlCon.execute("Update Test_Logs Set Pass_Fail = 'Passed' Where Start_Time = '" & $PullDateTime & "'")

; Lastly of course close the connection to the database before exiting the script
$sqlCon.close

I am still trying to work on pulling information from the database but as of right now I keep getting Junk returned.

$data = $sqlCon.Execute("Select Start_Time From Test_Logs")

MsgBox (0, "Data", $Data) 

This is just showing some eC junk message, If I figure it out, Ill update this post.

<{POST_SNAPBACK}>

I'm new to AutoIt but your code snipit returns to $data and you are displaying $Data in the message box. These would be two diferent values. :)

Share this post


Link to post
Share on other sites

Figured out the Reading of data by looking through ScriptKittys origional Database posts. It is a whole lot more complicated than I thought but eh... what ever. I have updated the my post above with the display code.


Share this post


Link to post
Share on other sites

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
Sign in to follow this  
Followers 0