Jump to content
Sign in to follow this  
sykes

Database Creation

Recommended Posts

Just thought I would share these. 3 separate functions here. One creates a access database, one adds a table to a database and the last adds fields to a database.

Func _CreateDB($dbname)
$newMdb = ObjCreate("ADOX.Catalog")
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$newmdb.ActiveConnection.Close
EndFunc

Func _CreateTBL($dbname, $tblname)
$addtbl = ObjCreate("ADODB.Connection")
$addTbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$addTbl.Execute("CREATE TABLE " & $tblname)
$addtbl.Close
EndFunc

Func _CreateFLD($dbname, $tblname, $fldname, $format)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD " & $fldname & " " & $format)
$addfld.Close
EndFunc

Feel free to expand on these if you wish.


We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

Nice work, I added a little quick find SQL to show how to grab data out of it, you can use similar SQL to populate the fields and such.

For now after you make the file, and put some data in, this will pull it back out.

; Script Start - Add your code below here
$dbname="c:\tempDB.mdb"
$tblname="table1"
$fldname="field1"
$format="Text(50)"

;_CreateDB($dbname)
;_CreateTBL($dbname, $tblname)
;_CreateFLD($dbname, $tblname, $fldname, $format)


$SQL = "SELECT * FROM table1"
$DSN_Connect=_getfield($SQL,$dbname,$fldname)
msgbox(1,"info",$dsn_connect)

Func _getfield($_sql,$_dbname,$_field)
   dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate ("ADODB.Recordset")
$adoSQL = $_sql
$adoRs.CursorType = 2
$adoRs.LockType = 3
$adoRs.Open($adoSql, $adoCon)

With $adoRs
If .RecordCount Then
While Not .EOF
$_output=$_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
return $_output
EndFunc


Func _CreateDB($dbname)
$newMdb = ObjCreate("ADOX.Catalog")
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$newmdb.ActiveConnection.Close
EndFunc

Func _CreateTBL($dbname, $tblname)
$addtbl = ObjCreate("ADODB.Connection")
$addTbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$addTbl.Execute("CREATE TABLE " & $tblname)
$addtbl.Close
EndFunc

Func _CreateFLD($dbname, $tblname, $fldname, $format)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD " & $fldname & " " & $format)
$addfld.Close
EndFunc

AutoIt3, the MACGYVER Pocket Knife for computers.

Share this post


Link to post
Share on other sites

Looks great haven't tested it yet however as sykes first sample showed how to create in access.

Is there a similar way with sql using the previous sample?

I have a requirement to create a SQL DB for blank, using MS SQL 2005.

Once created test it is there then create the fields and data etc.

After that I also want to delete the whole db once the db has done its job.

Any ideas?

jp

Share this post


Link to post
Share on other sites

If you're creating, using and then deleting, why does it have to be in SQL?

I used access databases because you can create and read/write with them without any extra software installed (i.e. Microsoft Access)

I found this on anther forum though ... maybe it will get you started in the right direction.

ADOX catalog is supported only for MS Jet (MS Access) databases.

You may create MS SQL Server database this way:

1. Connect to master database with TADOConnection.

2. Execute CREATE DATABASE statement.


We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

If you're creating, using and then deleting, why does it have to be in SQL?

I used access databases because you can create and read/write with them without any extra software installed (i.e. Microsoft Access)

I found this on anther forum though ... maybe it will get you started in the right direction.

<{POST_SNAPBACK}>

I've got a legacy system created in VB .NET and it has a front end program that requires SQL as the repository source. The project seems to have stalled hence the need for a tool to do the create and delete functions.

Instead of installing VB Studio and costing heaps I wondered if AutoIT could do the same thing and go even further.

So SQL is in and Access is not an option.

Thanks

Share this post


Link to post
Share on other sites

Its possible to put date in the AccessDb?

like these: _putdate($dbname,$fldname, $value)


Sorry, which my English is so bad. I come from Switzerland ;-)

Share this post


Link to post
Share on other sites

I've got a legacy system created in VB .NET and it has a front end program that requires SQL as the repository source. The project seems to have stalled hence the need for a tool to do the create and delete functions.

Instead of installing VB Studio and costing heaps I wondered if AutoIT could do the same thing and go even further.

So SQL is in and Access is not an option.

Thanks

<{POST_SNAPBACK}>

The following worked for me for Microsoft SQL:

$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:

$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

Edited by sykes

We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

Its possible to put date in the AccessDb?

like these: _putdate($dbname,$fldname, $value)

<{POST_SNAPBACK}>

Below is a quick example of adding data to an access database:

$dt = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb;")
$adoRS = ObjCreate("ADODB.Recordset")
$adoRS.CursorType = 2
$adoRS.LockType = 3
$adoRS.Open("SELECT * FROM tblData", $adoCon)
$adoRS.AddNew
$adoRS.Fields("fldData").Value = $dt
$adoRS.Fields("fldInfo").Value = $dt
$adoRS.Update
$adoRS.Close
$adoCon.Close

We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

The following worked for me for Microsoft SQL:

$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:

$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

<{POST_SNAPBACK}>

Thanks will give it a wherl on the dev system and see what happens.

If the systems are local without IP can I just obmit the ip addrress and put local instead, will try and see what happens. As the system/s are local and running MSDE 2000 or SQL 2005 Express Edition which is the replacement for MSDE 2000 and so at least at this point no actual server connection is required.

jp

Share this post


Link to post
Share on other sites

Thanks will give it a wherl on the dev system and see what happens.

If the systems are local without IP can I just obmit the ip addrress and put local instead, will try and see what happens. As the system/s are local and running MSDE 2000 or SQL 2005 Express Edition which is the replacement for MSDE 2000 and so at least at this point no actual server connection is required.

jp

<{POST_SNAPBACK}>

If SQL is running on the same machine as your program you should be able to use "localhost" or "127.0.0.1" to connect.

We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

Func _adddt($dbname, $tblname, $fldname, $dt)
   $adoCon = ObjCreate ("ADODB.Connection")
   $adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)
   $adoRs = ObjCreate ("ADODB.Recordset")
   $adoRS.CursorType = 2
   $adoRS.LockType = 3
   $adoRS.Open ("SELECT * FROM " & $tblname, $adoCon)
   $adoRS.AddNew
   $adoRS.Fields ($fldname).Value = $dt
   $adoRS.Update
   $adoRS.Close
   $adoCon.Close
EndFunc  ;==>_adddt

I write this Function.

It's possible to overwrite Date? how?

I would like e.g. write directly into the 5. Line

thx

Edited by silvanr

Sorry, which my English is so bad. I come from Switzerland ;-)

Share this post


Link to post
Share on other sites

Has anyone tried this with mySQL?

I have a LARGE script that generates a simple text log file and Screenshots. I would like to move this over to posting infomation into a mySQL database so the information can be stored on the fly but I just want to make sure it's possible before I spend the time downloading, installing, and learning mySQL.

one note is that I want to be able to upload the screenshots into the mySQL database "BLOB I think it's called". I know, I know, lots of people say this is not a great thing to do but with the screenshots being taken from 10 different comptuers and the image names are being auto-generated I won't be able to store them on the File system without possibly overwriting an old screenshot.

So, if anyone out there has mySQL that they could try with, please this let me know before I spend the Days setting one up when I don't know if it will even work with AutoIT.

Thanks a bunch,

Mike


hmm... I guess I have to have a signature...

Share this post


Link to post
Share on other sites

If SQL is running on the same machine as your program you should be able to use "localhost" or "127.0.0.1" to connect.

<{POST_SNAPBACK}>

Yeh, I had a look and tried that as well as the local PC name and the Name works fine for me.

Thanks for following up.

I do have an issue with

"$sqlCon.Execute("CREATE DATABASE Username_DB")"

as it works with AU3 file and not when compiled.

Actually I posted a followup and mentioned else where but if you can help I would appreciate it.

Here's the link, I love the way we can jump in and out of our posts and search the forum and update our messages. Thanks guys and gals.

#87470

jp

Edited by John-Paul

Share this post


Link to post
Share on other sites

I do have an issue with

"$sqlCon.Execute("CREATE DATABASE Username_DB")"

as it works with AU3 file and not when compiled.

<{POST_SNAPBACK}>

Are you by chance using SCiTE? If you are compile the au3 file using Alt+F7

We have enough youth. How about a fountain of SMART?

Share this post


Link to post
Share on other sites

Are you by chance using SCiTE?  If you are compile the au3 file using Alt+F7

<{POST_SNAPBACK}>

Thanks for the tip.

I am using SciTE for all my coding but never got around to using the compile option - duh!

On my system its Ctrl+F7 not sure about others.

But I see what you're saying and I love it.

Learning more every day, its great.

jp

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...