SQL Server Connection

[lab]

Hello all,

I'm trying to find a way using AutoIt to connect to a SQL Server.

I read several topics in this forum without finding an answer to my questions.

The idea is to use an AutoIt script in order to connect to my SQL database that is running on my local network, on a specified domain. What I'm trying to do is give acces to any computer without necessarily being part of the domain.

These 'Workgroup PCs' should then be able to make different requests on the server, read-only status is enough they don't need to have write or full access.

Any ideas ???

Thanks in advance

Lab

[scriptkitty]

this discussion might be helpful.

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

As far as permissions, that is all in how you set up passwords on the server. You have to give them, or set up a seperate set of rights and username/password combo for read access to all the tables/queries/etc that you want to give them access too.

To make it easy on ya, set up a link in Start/settings/control panel/administrative tools/data sources(ODBC)

You can set that as a read only flag, and set that as your DSN. It gives you a very similar platform that way, when connecting to all ODBC sources. Your script for MSSQL server, MySQL, MSAccess, ect, will be pretty much the same that way.

also, you can check to make sure you have the right connections before starting on your script writting. As far as the permissions, you would want to set that password thing up because they could just as easily click the read only box off.

Edited June 22, 2005 by scriptkitty

[John-Paul]

I seem to be in the same boat, maybe one step closer now thanks to searching the forum and getting feedback.

I was given the following tip to help create and delete a database in MS SQl and it works, as vinilla syntax goes.

"CODE.

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open("Provider=SQLOLEDB; _

Data Source=ip address of server; _

User ID=username; _

Password=password;")

$sqlCon.Execute("CREATE DATABASE Username_DB")

Substitute This:CODE$sqlCon.Execute("DROP DATABASE Username_DB")To delete the database when done.

Of course the one I was testing on had no password so I omitted the password part"

Hope it helps and if you look in the help file there some links to lookup COM data use, however I have not looked at these myself.

jp

[John-Paul]

"CODE.

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open("Provider=SQLOLEDB; _

Data Source=ip address of server; _

User ID=username; _

Password=password;")

$sqlCon.Execute("CREATE DATABASE Username_DB")

<{POST_SNAPBACK}>

[BigDaddyO]

Hello,

I have been fighting with Database connections this whole Week and I have setup a MSDE database and setup permissions and the whole thing.

I have been able to create a database using the code from above but for some reason I am totally Stuck when it comes to adding data "rows" to a Table.

I currently have a database called TestMatrix with a table called MSPatches

In this table i currently have the following columns. Index, Computer, PatchName, ComputerImage, OperatingSystem, ServicePack, ScreenShots. the only Column that can not be null is Index.

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; _
              Data Source=111.111.111.111; _
              User ID=testUser; _
              Password=testlogin;")

$sqlCon.execute("INSERT INTO MSPATCHES(index, Computer) Values (2, 'EvoD530')")

I keep getting The requested action with this object has failed on the INSERT line.. Has anyone manually added data to a table with AutoIT yet?

Thanks for any assistance,

Mike

[scriptkitty]

I have done it in access from AutoIt:

$dbname="c:\tempDB.mdb"
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$adoCon.execute("INSERT INTO table1 (field1, field2) Values (2, 'EvoD530')")
$adoCon.Close
Exit

Don't have SQL server set up here to test.

Are you sure your connection is up?

As a side note, you can also link the tables and update them that way as well.

c:\tempDB.mdb has a mysql table link to (MySQL) table2 (has a globe type icon)

I ran this same code only changing table1 to table2.

My MySQL table got a new row with these values.

Sorry again about not having a MSSQL server to confirm.

Edited June 22, 2005 by scriptkitty

[John-Paul]

$sqlCon.execute("INSERT INTO MSPATCHES(index, Computer) Values (2, 'EvoD530')")

I keep getting The requested action with this object has failed on the INSERT line.. 

<{POST_SNAPBACK}>

Seems very similar to my problem in the previous post, I'm wondering if its something to do with the $sqlCon.execute command.

Seems not to like the "execute" phrase.

Maybe someone else may be able to put more light on it.

jp

[John-Paul]

One issue that has loomed up is that when the SQL connects and works, its fine.

My question is how to error trap a process when the SQL bites back (gives an error).

The attached image shows an error on failure to connect to the SQL server, is it possible to retrieve the reason for the error.

This was a test and there were 4 fields - Server name, DB name, User and Password the latter was entered incorrectly to produce this error.

Alternating the accuracy of the fields always produces the same error.

Any AutoIT-SQL guru's able to help out?

thanks,

jp

Edited June 23, 2005 by John-Paul