Database Creation

[sykes]

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.

[scriptkitty]

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.

expandcollapse popup

; 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

[John-Paul]

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

[sykes]

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.

[John-Paul]

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

[silvanr]

Its possible to put date in the AccessDb?

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

[sykes]

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 June 14, 2005 by sykes

[sykes]

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

[John-Paul]

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

[sykes]

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.

[silvanr]

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 June 15, 2005 by silvanr

[BigDaddyO]

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

[John-Paul]

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 June 22, 2005 by John-Paul

[sykes]

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

[John-Paul]

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