New Plugin for DBP: DBP_TPC_Databases

Author

Message

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 29th Jun 2005 06:43 Edited at: 30th Jun 2005 01:14

Link

Howdy folks. Working on a Database TPC for DBP that will encompass MS Access, SQL Server, and hopefully MySQL. The current version is the beta release for the MS Access (.mdb) portion so far. The TPC is free for use and redist etc. The download includes the tpc dll file, documentation as a word .doc file, and a keyword ini file. Hopefully some of you can give it a go and let me know what you think.

Download the TPC from:
http://www.mod2software.com/dbp/dbp.htm
(located at bottom of table, requires DBP and .NET Framework 1.1 or higher)

DBP Plugins

Latest Plugin: DBP_TPC_Databases

Back to top

Profile PM

Vues3d on Kalimee

20

Years of Service

User Offline

Joined: 12th May 2004

Location: NYON (SWITZERLAND)

Posted: 29th Jun 2005 19:03

Link

Excellent idea & exactly what I was wishing for!
I will have a try this week-end!!!!!!

Guido
http://www.vues3d.com

Back to top

Profile PM Email Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 29th Jun 2005 23:37

Link

Thanks

Update: I added the keyword file to the zip. If you already downloaded the zip you can redownload it to obtain the keyword file. Copy the keyword ini file to your Editor/Keywords directory. The doc and the dll have not changed.

Here is some sample DBP code I was using during testsing. It contains a Connection String example (i had mdb file in same dir with dbp exe), as well as a bunch of the commands. Its not a single piece of code so I wouldnt suggest unremming the whole thing and running it

The database file contained the following format

tbl_Test
col: RID - AutoNumber, not for replication, primary key
col: Test1 - Text 50
col: Test2 - Text 50
col: Test3 - Text 50

sConn as string

sConn = "Jet OLEDB:Global Partial Bulk Ops=2;" + _
                  "Jet OLEDB:Registry Path=;" + _
                  "Jet OLEDB:Database Locking Mode=1;" + _
                  "Jet OLEDB:Database Password=;" + _
                  "Data Source=" + ".\dbp_tpc.mdb;" + _
                  "Password=;Jet OLEDB:Engine Type=5;" + _
                  "Jet OLEDB:Global Bulk Transactions=1;" + _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" + _
                  "Jet OLEDB:System database=;" + _
                  "Jet OLEDB:SFP=False;Extended Properties=;" + _
                  "Mode=Share Deny None;Jet OLEDB:New Database Password=;" + _
                  "Jet OLEDB:Create System Database=False;" + _
                  "Jet OLEDB:Don't Copy Locale on Compact=False;" + _
                  "Jet OLEDB:Compact Without Replica Repair=False;" + _
                  "User ID=Admin;Jet OLEDB:Encrypt Database=False"

mdb_init

iConnRet as integer
iConnRet=MDB_SetConnection(sConn)

print "conn="+str$(iConnRet)
`wait key
`MDB_ClearParams

`MDB_ClearParams
`MDB_AddParam "pTest1", "new1"
`MDB_AddParam "pTest2", "new2"
`MDB_AddParam "pTest3", "new3"

iRows As Integer
iCols as integer
`iRows = MDB_RunQuery("sp_TestInsert3Param")

`print "write db ok"
`wait key

`mdb_clearparams
`iRows = MDB_RunQuery("sp_GetAll")
`iCols = MDB_GetColumnsUpperbound()
`print "sp_GetAll=" + str$(irows) + " rows UB"
`print "colsUB=" + str$(iCols)
`wait key

`sCol0 as string
`sCol1 as string
`sCol2 as string
`sCol3 as string

`iLoop as integer
`for iLoop=0 to iRows

`     sCol0=MDB_GetRowDataByColIndex(iLoop,0)
`     sCol1=MDB_GetRowDataByColIndex(iLoop,1)
`     sCol2=MDB_GetRowDataByColIndex(iLoop,2)
`     sCol3=MDB_GetRowDataByColIndex(iLoop,3)

`     print scol0 + "|" + scol1 + "|" + scol2 + "|" + sCol3
`next iLoop

`print "read db ok"
`wait key

`irows=MDB_RunStatement("INSERT INTO tbl_Test (Test1,Test2,Test3) VALUES ('S','Q','L')")
irows=MDB_RunStatement("SELECT * FROM tbl_Test WHERE RID=8")
print str$(irows)

print str$(mdb_getrowsupperbound())
print str$(mdb_getcolumnsupperbound())
wait key

sRow as String
sRow=MDB_GetWholeRow(0,"*")
print srow
wait key

+ Code Snippet

sConn as string

sConn = "Jet OLEDB:Global Partial Bulk Ops=2;" + _
                  "Jet OLEDB:Registry Path=;" + _
                  "Jet OLEDB:Database Locking Mode=1;" + _
                  "Jet OLEDB:Database Password=;" + _
                  "Data Source=" + ".\dbp_tpc.mdb;" + _
                  "Password=;Jet OLEDB:Engine Type=5;" + _
                  "Jet OLEDB:Global Bulk Transactions=1;" + _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" + _
                  "Jet OLEDB:System database=;" + _
                  "Jet OLEDB:SFP=False;Extended Properties=;" + _
                  "Mode=Share Deny None;Jet OLEDB:New Database Password=;" + _
                  "Jet OLEDB:Create System Database=False;" + _
                  "Jet OLEDB:Don't Copy Locale on Compact=False;" + _
                  "Jet OLEDB:Compact Without Replica Repair=False;" + _
                  "User ID=Admin;Jet OLEDB:Encrypt Database=False"

mdb_init

iConnRet as integer
iConnRet=MDB_SetConnection(sConn)

print "conn="+str$(iConnRet)
`wait key
`MDB_ClearParams

`MDB_ClearParams
`MDB_AddParam "pTest1", "new1"
`MDB_AddParam "pTest2", "new2"
`MDB_AddParam "pTest3", "new3"

iRows As Integer
iCols as integer
`iRows = MDB_RunQuery("sp_TestInsert3Param")

`print "write db ok"
`wait key


`mdb_clearparams
`iRows = MDB_RunQuery("sp_GetAll")
`iCols = MDB_GetColumnsUpperbound()
`print "sp_GetAll=" + str$(irows) + " rows UB"
`print "colsUB=" + str$(iCols)
`wait key


`sCol0 as string
`sCol1 as string
`sCol2 as string
`sCol3 as string

`iLoop as integer
`for iLoop=0 to iRows

`     sCol0=MDB_GetRowDataByColIndex(iLoop,0)
`     sCol1=MDB_GetRowDataByColIndex(iLoop,1)
`     sCol2=MDB_GetRowDataByColIndex(iLoop,2)
`     sCol3=MDB_GetRowDataByColIndex(iLoop,3)

`     print scol0 + "|" + scol1 + "|" + scol2 + "|" + sCol3
`next iLoop

`print "read db ok"
`wait key

`irows=MDB_RunStatement("INSERT INTO tbl_Test (Test1,Test2,Test3) VALUES ('S','Q','L')")
irows=MDB_RunStatement("SELECT * FROM tbl_Test WHERE RID=8")
print str$(irows)

print str$(mdb_getrowsupperbound())
print str$(mdb_getcolumnsupperbound())
wait key

sRow as String
sRow=MDB_GetWholeRow(0,"*")
print srow
wait key

DBP Plugins-Latest: Databases Plugin!

Back to top

Profile PM

OSX Using Happy Dude

21

Years of Service

User Offline

Joined: 21st Aug 2003

Location: At home

Posted: 29th Jun 2005 23:57

Link

Encroaching on mySQL database access, eh ?

Atomz is at http://homepages.nildram.co.uk/~nickk
http://spaces.msn.com/members/BouncyBrick/

Back to top

Profile PM Email Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 30th Jun 2005 00:34 Edited at: 30th Jun 2005 00:36

Link

Actually I'll gladly bypass MySQL and just do Access and SQL Server, I wasnt sure if your tpc was still around/supported/fully functional/etc...

I'll take your post as a "yes it is".

DBP Plugins

Latest Plugin: DBP_TPC_Databases

Back to top

Profile PM

OSX Using Happy Dude

21

Years of Service

User Offline

Joined: 21st Aug 2003

Location: At home

Posted: 30th Jun 2005 01:45

Link

Yes, it is...

Atomz is at http://homepages.nildram.co.uk/~nickk
http://spaces.msn.com/members/BouncyBrick/

Back to top

Profile PM Email Website

Jimmy

21

Years of Service

User Offline

Joined: 20th Aug 2003

Location: Back in the USA

Posted: 30th Jun 2005 03:16

Link

Why couldn't you do MySQL? You're using .NET so isn't it all pretty much the same?

My blog Coding Competition

Back to top

Profile PM Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 30th Jun 2005 03:47

Link

I never said "couldn't", I said "hopefully" since I never worked with mysql nor do I have an mysql dbl to develop against. That whole point is moot anyway since TCA has a mysql tpc already. I will finish MSAccess and add SQL Server.

DBP Plugins

Latest Plugin: DBP_TPC_Databases

Back to top

Profile PM

Baggers

20

Years of Service

User Offline

Joined: 31st May 2004

Location: Yonder over dem dere hills

Posted: 30th Jun 2005 15:38

Link

Great work Rustler, if I hadnt just sorted out how im storing all my data id use this !
Keep it up.

Back to top

Profile PM Email

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 30th Jun 2005 22:28

Link

Thanks.

Once I have some feedback on the ms access bit I'll add the SQL Server stuff.

DBP Plugins

Latest Plugin: DBP_TPC_Databases

Back to top

Profile PM

APEXnow

Retired Moderator

21

Years of Service

User Offline

Joined: 15th Apr 2003

Location: On a park bench

Posted: 1st Jul 2005 02:20

Link

CR, you PWN!

Home of the Cartography Shop - DarkBASIC Professional map importer

Back to top

Profile PM Email Website

David R

21

Years of Service

User Offline

Joined: 9th Sep 2003

Location: 3.14

Posted: 1st Jul 2005 02:47

Link

Wow this is great stuff !

Fantastic and very useful.

One thing though; Doesn't M$ have some legal restriction on the usage/manipulation of Jet-based databases? Not sure if that's true, but I'm sure I've heard it somewhere.

Anyway. Good stuff

[url=www.lightningstudios.co.uk]

[/url]

Back to top

Profile PM Email Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 1st Jul 2005 07:26

Link

lol Pex.

DavidR, I'm not sure. I dont think I am breaking any m$ laws by bridging the gap between dbp and mssql/access

As long as the database files you manipulate are your own, or used by permission from their creators I dont see a problem. Let me know how you get on, and/or if you get arrested

j/k

DBP Plugins

Latest Plugin: DBP_TPC_Databases

Back to top

Profile PM

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 5th Jul 2005 01:05 Edited at: 10th Jul 2005 01:37

Link

UPDATE: DBP_TPC_MSAccess.zip

I decided to repackage this plugin as a separate plugin per type. I realized it would be silly to have it all jammed in one tpc, and thusly all compiled into your dbp exe when you are probably only going to use a single type at any one time.

The package that was posted on the web page has been replaced with DBP_TPC_MSAccess.zip. The core code hasnt changed but the files have been renamed accordingly. You may want to delete the old files from your system and replace them with the newly named ones for clarity.

I will be adding the DBP_TPC_SQLServer.zip package to the website either today or tomorrow. Any feedback that you can provide regarding the MSAccess package is greatly appreciated. Has anyone tried it out?

DBP Plugins

Latest: MSAccess, SQLServer, MySQL plugins for DBP

Back to top

Profile PM

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 5th Jul 2005 04:26 Edited at: 10th Jul 2005 01:38

Link

UPDATE: DBP_TPC_SQLServer.zip

The SQL Server plugin has been posted on the site. The file is DBP_TPC_SQLServer.zip, located at the bottom of the page. It contains the dll, a document, and a keywords ini file. All feedback is welcome.

Sample DBP code.
You'll need to adjust your connection string to contain your workstation and/or SQL server machine names, and the database name. The code below is not a complete program so don't unrem the whole thing and expect to run it straight away. It's posted as a reference only.

iConn as integer
sConn as string
irows as integer
icols as integer

sConn = "workstation id=JAB-UBER2K;packet size=4096;" + _
        "user id=sa;data source=JAB-UBER2K;" + _
        "persist security info=False;initial catalog=Test"

sql_Init

iconn=sql_SetConnection(sConn)

print "conn=" + str$(iconn)
wait key

remstart
SQL_ClearParams
SQL_AddParam "@pval1", "param1"
SQL_AddParam "@pval2", "param2"
SQL_AddParam "@pval3", "param3"

`irows = SQL_RunSProc("sp_Insert3")
irows=sql_runstatement("insert into tbl_test(test1,test2,test3) values(@pval1,@pval2,@pval3)")

print "write db ok"
wait key
remend

`remstart
`irows = SQL_RunSProc("sp_GetAll")
irows = SQL_RunStatement("select * from tbl_Test where rid=3")

sCol0 as string
sCol1 as string
sCol2 as string
sCol3 as string

iLoop as integer
for iLoop=0 to iRows

sCol0=SQL_GetRowDataByColName(iLoop,"RID")
     sCol1=SQL_GetRowDataByColName(iLoop,"Test1")
     sCol2=SQL_GetRowDataByColName(iLoop,"Test2")
     sCol3=SQL_GetRowDataByColName(iLoop,"Test3")

print scol0 + "|" + scol1 + "|" + scol2 + "|" + sCol3
next iLoop

print "read db ok"
print "rowsUB=" + str$(irows)
wait key
`remend

+ Code Snippet

iConn as integer
sConn as string
irows as integer
icols as integer

sConn = "workstation id=JAB-UBER2K;packet size=4096;" + _
        "user id=sa;data source=JAB-UBER2K;" + _
        "persist security info=False;initial catalog=Test"

sql_Init

iconn=sql_SetConnection(sConn)

print "conn=" + str$(iconn)
wait key

remstart
SQL_ClearParams
SQL_AddParam "@pval1", "param1"
SQL_AddParam "@pval2", "param2"
SQL_AddParam "@pval3", "param3"

`irows = SQL_RunSProc("sp_Insert3")
irows=sql_runstatement("insert into tbl_test(test1,test2,test3) values(@pval1,@pval2,@pval3)")

print "write db ok"
wait key
remend


`remstart
`irows = SQL_RunSProc("sp_GetAll")
irows = SQL_RunStatement("select * from tbl_Test where rid=3")

sCol0 as string
sCol1 as string
sCol2 as string
sCol3 as string

iLoop as integer
for iLoop=0 to iRows

     sCol0=SQL_GetRowDataByColName(iLoop,"RID")
     sCol1=SQL_GetRowDataByColName(iLoop,"Test1")
     sCol2=SQL_GetRowDataByColName(iLoop,"Test2")
     sCol3=SQL_GetRowDataByColName(iLoop,"Test3")

     print scol0 + "|" + scol1 + "|" + scol2 + "|" + sCol3
next iLoop


print "read db ok"
print "rowsUB=" + str$(irows)
wait key
`remend

For this, or any of my other plugins, click the link in my sig.
(home of DBP_NETLIB, EZ_Serv(1.4 coming soon), EZS Tutorials for DBP, TCP/IP plugins, Database plugins)

DBP Plugins

Latest: MSAccess, SQLServer, MySQL plugins for DBP

Back to top

Profile PM

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 10th Jul 2005 01:49

Link

UPDATE: DBP_TPC_MySQL.zip

The MySQL Server plugin (generic ODBC) has been posted on the site. The file is DBP_TPC_MySQL.zip, located at the bottom of the page. It contains the dll, a document, and a keywords ini file. All feedback is welcome.

Sample DBP code.
You'll need to adjust your connection string to contain your MySQL server machine name, the database name, and any user/password credentials. The code below is not a complete program so don't unrem the whole thing and expect to run it straight away. It's posted as a reference only.

+ Code Snippet

iConn as integer
sConn as string

sConn="DRIVER={MySQL ODBC 3.51 Driver};" + _
      "SERVER=JAB-2K;" + _
      "DATABASE=Test;" + _
      "UID=root;" + _
      "PASSWORD=<your password here>;" + _
      "OPTION=3;"

mysql_init

iconn=mysql_setconnection(sconn)

print "Conn=" + str$(iconn)
wait key


irows as integer
icols as integer

irows=mysql_runstatement("select * from tbl_test where rid<4")

`icols=mysql_getcolumnsupperbound()

iLoop as integer

for iloop=0 to irows
    print mysql_getwholerow(iloop,"|")
next iloop

print "rowsUB=" + str$(irows)
`print "colsUB=" + str$(icols)
wait key

You may find it necessary to download and install an ODBC driver for your operating system in order for this tpc to work correctly. The driver I installed was named MyODBC-3.51.11-2-win.exe. After this installation it was not necessary to create a *DSN entry in the ODBC system entries. This tpc was developed on a win2k pro system using .NET 1.1, and mysql version 5.0.7 beta release.

For this, or any of my other plugins, click the link in my sig.
(home of DBP_NETLIB, EZ_Serv(1.4 coming soon), EZS Tutorials for DBP, TCP/IP plugins, and Database plugins)

DBP Plugins

Latest: MSAccess, SQLServer, MySQL plugins for DBP

Back to top

Profile PM

PowerSoft

20

Years of Service

User Offline

Joined: 10th Oct 2004

Location: United Kingdom

Posted: 11th Jul 2005 23:32

Link

can i use this in VB? as its a .NET dll?

Always available to help

Back to top

Profile PM Email Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 12th Jul 2005 00:17

Link

they are exported for DBP, so I wouldn't attempt to. Also, why would you want to? do you mean vb6 or .net? The plugins are dumbed down for dbp since dbp cant pass/return complex objects to/from tpcs. I am going to make a pure .NET version of these for my own needs in .net programming, which can pass Param() objects and entire dataset objects etc, so there is no need for the iteration functions etc.

DBP Plugins

Latest: MSAccess, SQLServer, MySQL plugins for DBP

Back to top

Profile PM

PowerSoft

20

Years of Service

User Offline

Joined: 10th Oct 2004

Location: United Kingdom

Posted: 12th Jul 2005 00:37

Link

Ah,

Yea i just want to be able to have a portable database with my *secret application*

Always available to help

Back to top

Profile PM Email Website

CattleRustler

Retired Moderator

21

Years of Service

User Offline

Joined: 8th Aug 2003

Location: case modding at overclock.net

Posted: 12th Jul 2005 09:57

Link

ok.
use ms access (.mdb)

but for vb/vbnet you can use ado/ado.net respectively. no need for these dll's in those langs.

DBP Plugins

Latest: MSAccess, SQLServer, MySQL plugins for DBP

Back to top

Profile PM

JoelJ

21

Years of Service

User Offline

Joined: 8th Sep 2003

Location: UTAH

Posted: 12th Jul 2005 15:05

Link

Quote: "That whole point is moot anyway"

hehehe, you said 'moot'

Jimmy wannabe

Back to top

Profile PM Website

PowerSoft

20

Years of Service

User Offline

Joined: 10th Oct 2004

Location: United Kingdom

Posted: 12th Jul 2005 18:38

Link

Ah rite, cheers

Always available to help

Back to top

Profile PM Email Website

Sorry your browser is not supported!

Work in Progress / New Plugin for DBP: DBP_TPC_Databases