Auto It - SQL server Connection

[Vivek_303]

Hi All,

 

 

I am new to Autoit and facing huddles while connecting with SQL server. Please let me know how to connect with SQL Server.

Also, what are the prerequisite before establish the connection eg : installation of any driver, tool, path variable etc.

 

 

Here is what i tried :

 

$conn = ObjCreate( "ADODB.Connection" )
$rs = ObjCreate( "ADODB.RecordSet" )

$DSN = "DRIVER={SQL Server}; SERVER=MYSERVERNAME; DATABASE=MYDATABASE; UID=USERNAME; PWD=PASSWORD;"
$conn.Open($DSN)

$rs.Open( "Select * FROM t_task_ISL", $conn )

MsgBox(0, "AutoIT-SQL Result", "Value = " & $rs.Fields.item(0).value )

$conn.close
 

 

Thanks

Vivek

[faustf]

you have look it ?

 

[mLipok]

I would suggest to you to look here ADO.au3 UDF  

 

and:

to the forum.

 

Edited December 20, 2018 by mLipok

[YFridman]

mLipok, i downloaded and included ADO.au3 in my script.  when i ran it, SciTe opened ADO.au3 and pointed out that _ArrayDisplay, in 2 cases, is called with the wrong number of arguments.  I am not sure if the function changed since you included it or something else is the issue.

[YFridman]

mLipok, what is the function name for adding a new record, in ADO.au3?

[jchd]

Use _ADO_Execute() to perform an INSERT INTO T ... SQL statement.

[Skysnake]

On 3/6/2019 at 12:04 AM, YFridman said:

mLipok, what is the function name for adding a new record, in ADO.au3?

Try this:

 

[grahamc]

This is how I connect to SQL servers

<code>

; Create DB objects
$oDB = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.RecordSet")

; Open DB connection
$oDB.Open("Provider=SQLOLEDB; Data Source=<server>; initial catalog=<database>; User ID=<username>; Password=<pwd>") ; Provide credentials

OR

$oDB.Open("Provider=SQLOLEDB; Data Source=<server>; initial catalog=<database>; Trusted_Connection=YES") ; Uses credentials of account running script/exe

 

</code>

[YFridman]

I tried this to add a record to the table, connection to which is open successfully.  

$string="'" & $Date1 & "','" & $Date2 & "','" & $Time1 & "','" & $Time2 & "','" & $Installs & "','" & $ConfigFile & "','" & $Version & "','" & $Date & "','" & $Location & "','" & $Drive & "','" & $Completed & "','" & $Runs & "','" & $Chassis

$result = $adCN.Execute("INSERT INTO ImagingData")
$result = $adCN.Execute("VALUES (" & $string & ")")

but get an error 

The requested action with this object has failed.:
$result = $adCN.Execute("INSERT INTO ImagingData")
$result = $adCN^ ERROR

Any ideas what's wrong here?

[jchd]

You need to supply a valid SQL statement. This should work a bit better (depends on the table schema):

"INSERT INTO ImagingData (<column>) VALUES (" & $string & ")"

[YFridman]

that was the problem.  when i was looking at the SQL statement it was on two lines.  It also said that <column> is not needed if i am populating all columns.  So should it be like this in AutoIT then?
$result = $adCN.Execute("INSERT INTO ImagingData VALUES (" & $string & ")")

[jchd]

Maybe. If you intend to populate ALL columns, then $string needs to contain the values for ALL columns, using correct syntax, separated by commas. That is SQL dictating this, not AutoIt. For instance if the table has 3 columns:

FirstName TEXT, LastName TEXT, Age INT

then, an example of content could be:

$string = "'Fred', 'Mc Kenzie', 43"

In SQL literal strings are delimited by single quotes.

Edited March 11, 2019 by jchd

[Skysnake]

The problem is not with AutoIt, but with your SQL.

The syntax of the typical insert statement is

INSERT INTO _table_ (col1, coln) VALUES (val1,valn);

in your example you seem to list only a table or its column? You need to tell SQL which table and column(s) you want to insert data into.

Have a look at the SQLite examples.

[mLipok]

11 hours ago, YFridman said:

but get an error 

The requested action with this object has failed.:
$result = $adCN.Execute("INSERT INTO ImagingData")
$result = $adCN^ ERROR

Any ideas what's wrong here?

 

You will see what is wrong in COM Error Handler.
What you see there ?