Getting It Back Out Of Sql

[GreatWest]

Okay I've learned how to put data in my MS SQL database. The script works great. Now I'm trying to figure out how to pull the data out and place that data into a variable. I've looked many examples but am having difficulty understanding what variable is actually holding the data. I see such thing like:

$cmboVal[2] = $cmboVal[2] & .Fields("Gerätetyp").Value & "|"

I don't understand what the .Fields("fieldname").Value & is. AutoIT doesn't mention any "." Type commands or variables and I can't find anything in SQL about it. Can someone explain this to me? What would be of great help would be a simple script that pulls the data from a table and a single column, and place that data into a variable. My table has record numbers so what I need to be able to do is tell the script to go to record 4 (which is easy) and put column1 into $variable1, column2 into $variable2etc, that's the part I'm having trouble with.

Any help is greatly appreciated.

[Joon]

Are you using cdKid's MySQL UDF?

http://www.autoitscript.com/forum/index.php?showtopic=20814

[GreatWest]

Are you using cdKid's MySQL UDF?

http://www.autoitscript.com/forum/index.php?showtopic=20814

No because I'm using MS SQL not MySQL.

Here's where I dump the data into the database.

; Dump the data collected into the variables to be placed into the SQL database.

$AssetCurrency = ""

$AssetID = ""

$AssetStatus = "Deployed"

$Category = "Hardware"

$Manufacturer = $man

$Item = $pro

$HowUsed = $use

$Region = "Americas - U.S.A."

$Site = "City:Location"

$AssetLoc = "PCN"

$TagNum = "ES" & $ser

$EndUserEmpID = $namex

$ReportingMgrAID = ""

$SerialNumber = $ser

$SystemID = $namec

$MaintVenName = ""

$ReceivedDate = ""

$InstallDate = ""

$DisposalDate = ""

$OwnerCostCenter = ""

$PRNumber = ""

$PONumber = ""

$OwnershipType = "Expense"

$SupportGroup = "EPS IT"

$LoanerReturnDate = ""

$UserDefined1 = "Auto Update"

$UserDefined2 = ""

$UserDefined3 = ""

$SQLQuery = "INSERT INTO Table (" _

& "AssetCurrency," _

& "AssetID," _

& "AssetStatus," _

& "Category," _

& "Manufacturer," _

& "Item," _

& "HowUsed," _

& "Region," _

& "Site," _

& "AssetLoc," _

& "TagNum," _

& "EndUserEmpID," _

& "ReportingMgrAID," _

& "SerialNumber," _

& "SystemID," _

& "MaintVenName," _

& "ReceivedDate," _

& "InstallDate," _

& "DisposalDate," _

& "OwnerCostCenter," _

& "PRNumber," _

& "PONumber," _

& "OwnershipType," _

& "SupportGroup," _

& "LoanerReturnDate," _

& "UserDefined1," _

& "UserDefined2," _

& "UserDefined3" _

& ") VALUES (" _

& "'" & $AssetCurrency & "'" & "," _

& "'" & $AssetID & "'" & "," _

& "'" & $AssetStatus & "'" & "," _

& "'" & $Category & "'" & "," _

& "'" & $Manufacturer & "'" & "," _

& "'" & $Item & "'" & "," _

& "'" & $HowUsed & "'" & "," _

& "'" & $Region & "'" & "," _

& "'" & $Site & "'" & "," _

& "'" & $AssetLoc & "'" & "," _

& "'" & $TagNum & "'" & "," _

& "'" & $EndUserEmpID & "'" & "," _

& "'" & $ReportingMgrAID & "'" & "," _

& "'" & $SerialNumber & "'" & "," _

& "'" & $SystemID & "'" & "," _

& "'" & $MaintVenName & "'" & "," _

& "'" & $ReceivedDate & "'" & "," _

& "'" & $InstallDate & "'" & "," _

& "'" & $DisposalDate & "'" & "," _

& "'" & $OwnerCostCenter & "'" & "," _

& "'" & $PRNumber & "'" & "," _

& "'" & $PONumber & "'" & "," _

& "'" & $OwnershipType & "'" & "," _

& "'" & $SupportGroup & "'" & "," _

& "'" & $LoanerReturnDate & "'" & "," _

& "'" & $UserDefined1 & "'" & "," _

& "'" & $UserDefined2 & "'" & "," _

& "'" & $UserDefined3 & "'" _

& ")"

$DSN = "Provider=SQLOLEDB;SERVER=servername;DATABASE=databasename;UID=userid;PWD=password"

ExecuteQuery($DSN, $SQLQuery)

Func ExecuteQuery ($DSN,$SQLQuery)

$adoSQL = $SQLQuery

$adoCon = ObjCreate("ADODB.Connection")

$adoCon.Open ($DSN)

$adoCon.Execute($adoSQL)

$adoCon.Close

EndFunc

I know this could be made much cleaner but I'm going for functionality right now, and this works.

What I guess I'm needing help with is how to create the functon to put the data back into variables. I can do a SELECT without any problem, but I don't know how to take the data collected and place it into a variable.

[billmez]

Okay I've learned how to put data in my MS SQL database. The script works great. Now I'm trying to figure out how to pull the data out and place that data into a variable. I've looked many examples but am having difficulty understanding what variable is actually holding the data. I see such thing like:

$cmboVal[2] = $cmboVal[2] & .Fields("Gerätetyp").Value & "|"

I don't understand what the .Fields("fieldname").Value & is. AutoIT doesn't mention any "." Type commands or variables and I can't find anything in SQL about it. Can someone explain this to me? What would be of great help would be a simple script that pulls the data from a table and a single column, and place that data into a variable. My table has record numbers so what I need to be able to do is tell the script to go to record 4 (which is easy) and put column1 into $variable1, column2 into $variable2etc, that's the part I'm having trouble with.

Any help is greatly appreciated.

Open your connection as $DBsource with the query $SqlQuery. Put the constants at the top of your script.

expandcollapse popup

Const $adLockReadOnly = 1
Const $adLockPessimistic = 2
Const $adLockOptimistic = 3
Const $adLockBatchOptimistic = 4
Const $adOpenForwardOnly = 0
Const $adOpenKeyset = 1
Const $adOpenDynamic = 2
Const $adOpenStatic = 3
Const $adUseServer = 2
Const $adUseClient = 3
Const $adCmdUnknown = "&H0008"
Const $adCmdText = "&H0001"
Const $adCmdTable = "&H0002"
Const $adCmdStoredProc = "&H0004"
Const $adCmdFile = "&H0100"
Const $adCmdTableDirect = "&H0200"


$ObjCon = ObjCreate("ADODB.Connection")
$ObjCon.Open($DBsource)
$ObjRS =ObjCreate("ADODB.Recordset")
$ObjRS.CursorType = $adOpenStatic
$ObjRS.CursorLocation = $adUseClient
$ObjRS.LockType = $adLockReadOnly

$ObjRS.Open ($SqlQuery, $ObjCon,-1 ,-1 ,$adCmdText)
$RecordCount = $ObjRS.RecordCount
$ObjRS.Move(4); move to the 4th record 

$field1Name = $ObjRS.Fields(0).Name
$field1Value = $ObjRS.Fields(0).Value
$field2Name = $ObjRS.Fields(1).Name
$field2Value = $ObjRS.Fields(1).Value
$field3Name = $ObjRS.Fields(2).Name
$field3Value = $ObjRS.Fields(2).Value
; etc for all fields you want

$ObjRS.Close
$ObjCon.Close

The . is for the object.properties

[GreatWest]

Open your connection as $DBsource with the query $SqlQuery. Put the constants at the top of your script.

expandcollapse popup

Const $adLockReadOnly = 1
Const $adLockPessimistic = 2
Const $adLockOptimistic = 3
Const $adLockBatchOptimistic = 4
Const $adOpenForwardOnly = 0
Const $adOpenKeyset = 1
Const $adOpenDynamic = 2
Const $adOpenStatic = 3
Const $adUseServer = 2
Const $adUseClient = 3
Const $adCmdUnknown = "&H0008"
Const $adCmdText = "&H0001"
Const $adCmdTable = "&H0002"
Const $adCmdStoredProc = "&H0004"
Const $adCmdFile = "&H0100"
Const $adCmdTableDirect = "&H0200"


$ObjCon = ObjCreate("ADODB.Connection")
$ObjCon.Open($DBsource)
$ObjRS =ObjCreate("ADODB.Recordset")
$ObjRS.CursorType = $adOpenStatic
$ObjRS.CursorLocation = $adUseClient
$ObjRS.LockType = $adLockReadOnly

$ObjRS.Open ($SqlQuery, $ObjCon,-1 ,-1 ,$adCmdText)
$RecordCount = $ObjRS.RecordCount
$ObjRS.Move(4); move to the 4th record 

$field1Name = $ObjRS.Fields(0).Name
$field1Value = $ObjRS.Fields(0).Value
$field2Name = $ObjRS.Fields(1).Name
$field2Value = $ObjRS.Fields(1).Value
$field3Name = $ObjRS.Fields(2).Name
$field3Value = $ObjRS.Fields(2).Value
; etc for all fields you want

$ObjRS.Close
$ObjCon.Close

The . is for the object.properties

I'll give this a try - and thanks! So this is more like object oriented programming...er uh, scripting? And can you point me in the direction of where I can learn about the lines you've used such as:

Const $adLockReadOnly = 1

Const $adLockPessimistic = 2

Const $adLockOptimistic = 3

Const $adLockBatchOptimistic = 4

Const $adOpenForwardOnly = 0

Const $adOpenKeyset = 1

Const $adOpenDynamic = 2

Const $adOpenStatic = 3

Const $adUseServer = 2

Const $adUseClient = 3

Const $adCmdUnknown = "&H0008"

Const $adCmdText = "&H0001"

Const $adCmdTable = "&H0002"

Const $adCmdStoredProc = "&H0004"

Const $adCmdFile = "&H0100"

Const $adCmdTableDirect = "&H0200"

Where did you learn what these do? Is this part of SQL, AutoIT or just something I can learn about in say a VB book? Using someone elses code is nice but understanding it would be much better

Such as I see the $ObjCon = ObjCreate("ADODB.Connection") but what does that mean in the lower line where it says $OjbRS.Open ($SqlQuery, $ObjCon,-1,-1,$adCmdText)

What are the negative one's for and such? I don't expect you to explain and teach me all this of course but if you could point me to some good books, websites...etc that would be great.

Thanks!

[billmez]

I'll give this a try - and thanks! So this is more like object oriented programming...er uh, scripting? And can you point me in the direction of where I can learn about the lines you've used such as:

Const $adLockReadOnly = 1

Const $adLockPessimistic = 2

Const $adLockOptimistic = 3

Const $adLockBatchOptimistic = 4

Const $adOpenForwardOnly = 0

Const $adOpenKeyset = 1

Const $adOpenDynamic = 2

Const $adOpenStatic = 3

Const $adUseServer = 2

Const $adUseClient = 3

Const $adCmdUnknown = "&H0008"

Const $adCmdText = "&H0001"

Const $adCmdTable = "&H0002"

Const $adCmdStoredProc = "&H0004"

Const $adCmdFile = "&H0100"

Const $adCmdTableDirect = "&H0200"

Where did you learn what these do? Is this part of SQL, AutoIT or just something I can learn about in say a VB book? Using someone elses code is nice but understanding it would be much better

Such as I see the $ObjCon = ObjCreate("ADODB.Connection") but what does that mean in the lower line where it says $OjbRS.Open ($SqlQuery, $ObjCon,-1,-1,$adCmdText)

What are the negative one's for and such? I don't expect you to explain and teach me all this of course but if you could point me to some good books, websites...etc that would be great.

Thanks!

The lines:

Const $adLockReadOnly = 1

Const $adLockPessimistic = 2

Const $adLockOptimistic = 3

are ADO constants from the ado.vbs file and they are used to set the connection and recordset parameters.

You are:

1. creating a connection object

2. creating a recordset object

3. opening the connection

4. opening the recordset $OjbRS.Open ($SqlQuery, $ObjCon,-1,-1,$adCmdText) with the query and the connection object ($ObjCon). The -1 is used in AutoIT to designate a default value. In VBscript, it would just be blank. You are usig the default values here because the cursor type and location and lock types were set by:

$ObjRS.CursorType = $adOpenStatic

$ObjRS.CursorLocation = $adUseClient

$ObjRS.LockType = $adLockReadOnly

earlier in the code

The $adCmdText is a constant telling the ADO engine to expect a text query to be passed.

You are using AutoIt's ability to interact with ActiveX objects. Technically you are using ADO (active data objects), and any good book on ADO (preference using VB script since it is the most similar to AutoIT) will be helpful.

You can also try the MS technet site, there is a lot of good ADO information.

Glad I could help

[GreatWest]

Fantastic! I kind of recognized the ADO stuff from website that had a tutorial on it. I've worked a bit with VB script so I'm familiar with some of that too. Thanks again for the info. I've modified what you wrote up and it's working fairly well, more cleanup than anything. You know, getting the variables in the right order...etc I'll for sure read up on what all the ado stuff is. One more time, thanks a lot!

[billmez]

Fantastic! I kind of recognized the ADO stuff from website that had a tutorial on it. I've worked a bit with VB script so I'm familiar with some of that too. Thanks again for the info. I've modified what you wrote up and it's working fairly well, more cleanup than anything. You know, getting the variables in the right order...etc I'll for sure read up on what all the ado stuff is. One more time, thanks a lot!

Here is a link to the ADO info and programmers guide on MSDN:

http://msdn.microsoft.com/library/default....adooverview.asp

Most DB applications will only use a small set of the available ADO options. SQL queries are also important, you can find a lot of good information on the net for them too. My advice is to keep it simple, don't make it more complicated than you need to. Also make sure you close/release any objects that you open, especialy if you are in a shared environment.

[GreatWest]

Here is a link to the ADO info and programmers guide on MSDN:

http://msdn.microsoft.com/library/default....adooverview.asp

Most DB applications will only use a small set of the available ADO options. SQL queries are also important, you can find a lot of good information on the net for them too. My advice is to keep it simple, don't make it more complicated than you need to. Also make sure you close/release any objects that you open, especialy if you are in a shared environment.

Cool!

I'm fairly good with the SQL quary tool and AutoIT scipting. It's the merging that I'm having trouble with - ADO...etc. I'll look up the stuff you've pointed to. Yep I remembered to close the SQL connection in my script from the VB scripting I used to do. One of the SQL guys jumped on me when I first got into it because I kept leaving connections open all over the place. Oh well, that's how you learn. I agree on keeping it simple. That's the best way to do anything, less chance of failure. In fact on the code you gave me I started working on how to count the records so I could pull the data and exit on the last record. Then low and behold I saw you had taken care of that for me with $RecordCount = $ObjRS.RecordCount so I just did a Do loop until $RecNum = $RecrodCount and viola, she stopped on queue. This is too cool being able to interface with SQL so well. Once I get a little more experiance under my belt that'll change to "cool being able to interface with SQL so easily".

[billmez]

Cool!

I'm fairly good with the SQL quary tool and AutoIT scipting. It's the merging that I'm having trouble with - ADO...etc. I'll look up the stuff you've pointed to. Yep I remembered to close the SQL connection in my script from the VB scripting I used to do. One of the SQL guys jumped on me when I first got into it because I kept leaving connections open all over the place. Oh well, that's how you learn. I agree on keeping it simple. That's the best way to do anything, less chance of failure. In fact on the code you gave me I started working on how to count the records so I could pull the data and exit on the last record. Then low and behold I saw you had taken care of that for me with $RecordCount = $ObjRS.RecordCount so I just did a Do loop until $RecNum = $RecrodCount and viola, she stopped on queue. This is too cool being able to interface with SQL so well. Once I get a little more experiance under my belt that'll change to "cool being able to interface with SQL so easily".

Good show, glad you're on the right track. One caution with ObjRS.RecordCount, you need to have the Cursor set to a client side cursor (as it is in the example $ObjRS.CursorLocation = $adUseClient) or else it will not return a value.

[Wooltown]

Trying to follow your examples, but fail, can yuo help me?

It fails on the line: $ObjRS.Open ($SqlQuery, $ObjCon,-1 ,-1 ,$adCmdText),

Error: The requested action with this object has failed.

Regards

Sven

expandcollapse popup

$DBsource = "Provider=SQLOLEDB;SERVER=DB1serv;DATABASE=DB1;UID=Name99;PWD=secret99"
    $SQLQuery = "SELECT tbl_nr1.* FROM tbl_nr1"
    ExecuteQuery($DBsource, $SQLQuery)  

Func ExecuteQuery ($DBsource,$SQLQuery)

    Const $adLockReadOnly = 1
    Const $adLockPessimistic = 2
    Const $adLockOptimistic = 3
    Const $adLockBatchOptimistic = 4
    Const $adOpenForwardOnly = 0
    Const $adOpenKeyset = 1
    Const $adOpenDynamic = 2
    Const $adOpenStatic = 3
    Const $adUseServer = 2
    Const $adUseClient = 3
    Const $adCmdUnknown = "&H0008"
    Const $adCmdText = "&H0001"
    Const $adCmdTable = "&H0002"
    Const $adCmdStoredProc = "&H0004"
    Const $adCmdFile = "&H0100"
    Const $adCmdTableDirect = "&H0200"

    $ObjCon = ObjCreate("ADODB.Connection")
    $ObjCon.Open($DBsource)
    $ObjRS = ObjCreate("ADODB.Recordset")
    $ObjRS.CursorType = $adOpenStatic
    $ObjRS.CursorLocation = $adUseClient
    $ObjRS.LockType = $adLockReadOnly
    
    $ObjRS.Open ($SqlQuery, $ObjCon,-1 ,-1 ,$adCmdText)
    With $ObjRS
        If .RecordCount Then
            GUICtrlDelete($IRListView)
            $IRListView = GUICtrlCreateListView(10,50,1180,840)
            While Not .EOF
            
                .MoveNext
            Wend
        EndIf
    EndWith
    $ObjRS.Close
    $ObjCon.Close
    

EndFunc