Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Program Announcements / DBConn - An ODBC Database Plugin for DBPro.

Author
Message
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 13th Aug 2009 19:15 Edited at: 13th Dec 2012 18:11
<<< Update Links to current web site structure >>>

DBConn Offical Page

DBConn_1_0_0.zip - Initial release.
DBConn_1_0_15.zip - Fixed : Bug that didn't clear the dataset pointer between SQL queries, so any query after the first one would be placed in the wrong position within the dataset.
DBConn_1_1_4.zip - Added : 10 new commands to pull specific data from your database without having to convert them from a string.
DBConn_1_1_5.zip - Fixed : Bug in the DBExecute command that always returned a 1 when successful, instead of the number of rows affected by the command.

DBConn is an ODBC Database plugin for DBPro. Installation is as simple as unzipping the attached file to your DBPro folder. Help files, Keyword file, the DLL, and a sample project are all there in the appropriate directories.

What this plugin lets you do is connect to darn near any data source. Any database, or datafile that has an ODBC Compliant driver should work. It's been tested with MS SQL Server 2000, MySQL Server, and an MS Access file.

DSNs: You'll need to set up a System DSN on the machine this runs on to access database servers. No DSN is needed for MS Access files. Windows has a generic entry in the User DSNs that takes care of that.

The commands available are,

DBConnect(connection string)
This sets the connection string and establishes the connection to the datasource. This command must be called before any other commands can be used. Once this command is called the connection will remain open until it times out. This allows you to run multiple commands on your database without the performance hit of having to open and close the connection after each command.

DBExecute(non-query sql statement)
This processes a non-query sql statement and returns a status code indicating the outcome. 0 = No rows were affected by the query, > 0 = The number of rows affected, -1 = An error occured.

DBRunSQL(sql statement)
This processes a standard query sql statement and returns a status code indicating the outcome. 0 = No rows were found matching your query, > 0 = The number of rows found to match your query, -1 = An error occured. The results from your query are stored in a persistent ResultSet that remains until you send another query, or close down your program.

DBGetDataByColName(row, column name)
This lets you retrieve data from the ResultSet that holds the results of your last query. In this case, you specify the row by it's index number in the ResultSet, and by Column Name. The data returned is always a string, so if a numeric value was expected you'll have to convert it.

DBGetDataByColNum(row, column)
This lets you retrieve data from the ResultSet that holds the results of your last query. In this case, you specify the row and column by their index numbers in the ResultSet. The data returned is also always in a string.

DBError()
This returns a string containing the full text of the error detected. Use this when one of the other commands returns a -1 condition to find out what the error is.

NEW Commands as of version 1.1.4

DBGetBool()
Returns a boolean value 0 or 1.

DBGetByte()
Returns a byte value from 0 to 255.

DBGetWord()
Returns a word value from 0 to 65535.

DBGetDWord()
Returns a dword value from 0 to 4,294,967,295.

DBGetFloat()
Returns a float value.

DBGetDouble()
Returns a double float value.

DBGetInt16()
Returns a 16 bit integer value from -32,768 to 32,767.

DBGetInt32()
Returns a 32 bit integer value from -2,147,483,648 to 2,147,483,647.

DBGetInt64()
Returns a 64 bit (double) integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

DBGetString()
Returns a string.

NOTE: All row and column indexes start at 0.

If there are any other commands you would like to see, let me know. I'm always open to suggestions.

This plugin is FREE to use in personal and commercial programs.

jeffhuys
11
Years of Service
User Offline
Joined: 24th May 2006
Location: No cheesy line here.
Posted: 13th Aug 2009 19:25 Edited at: 13th Aug 2009 19:26
Hey, KISTech!
Nice work!
Will this work, like, better than CattleRustler's DLL? Because I can have it to connect but GetDataByColName did'nt work (perhaps I did it wrong?).
Well, I'll test this as soon as I don't have any problems with Windows 7 anymore...

Jeff



You're the 'th to view this signature!
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 13th Aug 2009 19:56 Edited at: 13th Aug 2009 19:56
I haven't really put this through it's paces yet, so I can't say what it's performance is like.

I've been using CattleRustler's DLLs for almost 2 years and haven't really had any problems with the commands. In fact I typically wrap his commands in a DBPro function (mainly because of the very long command names) and it seems to work fine.

I put this DLL together to offer those with other data source needs a way to get at it inside DBPro. While this can access the same databases as CattleRustler's 3 DLLs, it isn't meant to compete with them. The decision of which to use just depends on your needs.

Dark Dragon
10
Years of Service
User Offline
Joined: 22nd Jun 2007
Location: In the ring, Kickin\' *donkeybutt*.
Posted: 13th Aug 2009 21:32
Whoa. Cool KiS, will test this out, sounds cool

(\__/) HHAHAHAHAHAH!
(O.o ) / WORLD DOMINATION!!!!!!!!!!
(> < )
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 14th Aug 2009 01:52
Thanks. Let me know how it goes.

HavokDelta6
7
Years of Service
User Offline
Joined: 22nd Aug 2009
Location: United Kingdom
Posted: 28th Aug 2009 01:11
would you ever make this Open Source? for others to learn.

GCSE results:
2 a's 3 b's AND
5 (FIVE) a*'s ^_^
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 28th Aug 2009 01:29
Sometime down the road maybe.

freight hopper
13
Years of Service
User Offline
Joined: 26th Dec 2003
Location: Just beyond the Dunsmuir yard limits
Posted: 28th Aug 2009 03:35 Edited at: 28th Aug 2009 03:43
Looks very exciting. Can't wait to delve into it, tho it will be awhile . Is there a simple tutorial for, say, Access? Or maybe a YouTube video! Not hard, just go to screencapturer.com, download it (free) and set it to 5Hz and it will produce good quality .wmv's that look and sound great on YouTube.

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 28th Aug 2009 18:50
It includes some good documentation and an example.

The only thing that differs between connecting to MySQL, MS SQL, or an Access File is the connection string. I've provided some examples in the documentation for that as well, and for further help in that area you can refer to http://www.connectionstrings.com

Let me know if you have any questions or run into any problems.

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 2nd Sep 2009 19:08
The first post has been updated with a new version.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 2nd Sep 2009 21:25
Ty

Working as expected now.
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 3rd Sep 2009 01:48
Thanks for finding that.

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 4th Sep 2009 01:31 Edited at: 4th Sep 2009 01:32
Updated first post with new version containing 10 new commands.

You can now retrieve data in it's native format from the database.

Data types available are,
+ Code Snippet

The minimum and maximum values of these are shown in the first post, and in the documentation.

Questions, comments, problems? Just let me know.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 5th Sep 2009 13:37
Found a small problem with statement affecting multiple rows which dosen't really affect me atm because I'm only working on 1 row at a time. Here's what I get:

result=dbexecute("UPDATE table SET name='test' WHERE id=1;")
Returns 1, 1 row affected.

result=dbexecute("UPDATE table SET name='test' WHERE id=5355345;")
Returns 0, 0 rows affected.

result=dbexecute("UPDATE tabxe SET name='test' WHERE id=1;")
Returns -1, error. (table name wrong)

result=dbexecute("UPDATE table SET name='test' WHERE id<=10;")
Returns 1, but in my DB should be 10 rows affected.
(And checking mysql admin they are all modified correctly, just the return code dosen't reflect the true amount)

I've not downloaded the new version yet, I'm at work so maybe that will fix it. In any case it's not by any means `critical`
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 5th Sep 2009 19:32
So I'm assuming that the last test is the only problem. I'll check it out. It's probably a little something I overlooked and it will be a quick fix.

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 6th Sep 2009 21:25
Version 1.1.5 has been posted. DBExecute return value has been fixed.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 7th Sep 2009 00:14
Foiled Again!

err... I mean great, thanks!
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 7th Sep 2009 02:25
Keep 'em coming, if you can find them.

Xenocythe
12
Years of Service
User Offline
Joined: 26th May 2005
Location: You Essay.
Posted: 7th Sep 2009 02:47
I was thinking, for convenience, why not make the DBConnect command as follows...

DBConnect(server string, user string, pass string, database string)

...and then piece each string accordingly in the plugin code?

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 7th Sep 2009 20:10
Each connection string is going to be different based on what you are connecting to. Some don't even need a username and password.

Have a look at some of the ODBC connection strings at http://www.connectionstrings.com and you'll have an idea how varried they can be with different applications.

It would be nice if ODBC had standard parameters across all datasources though. Some use PASSWORD, others use PWD. Some use UID and others use USERID.

For a game server you're really only setting this once aren't you?

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 13th Sep 2009 22:51
Is it acceptable to use a query type statement with dbExecute if you are not interested in the actual resulting data, just the return code? For example if I want to check if a record already exists for a certain entry, but not disturb an earlier query that I have already generated with dbRunSQL.

It all seems to work ok, but I just wondered if there might be consequences I'm not aware of. (Like sometimes clearing the previous query data anyway)

Thanks
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 14th Sep 2009 18:00
I can't say I've attempted something like that. I suspect it would be fine. If a record exists for the query you should only get back the number of rows, and if it doesn't then it should return a 0.

Using the DBExecute command wont interfere with the resultset at all, so you should be safe there.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 16th Sep 2009 22:28
Cheers, I tested it out anyway and seems ok.

Next question Can you use User-defined functions with the plugin? I am converting my database from custom format to mysql, and I had a function to fuzzy-search text. (Levenstein distance) From what I can see the only "proper" way to do this in mysql is with a UDF, but I haven't really looked into implementing it until I know it's worth the effort.
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 17th Sep 2009 19:02
You mean stored procedures?

I haven't looked into that, but I think you can run them with the DBExecute command.

I'm on day 2 now of disinfecting my computer, so it's going to be a bit before I can look into this further. I'm running on my Windows 7 drive to clean the Windows XP drive and don't have anything installed in Windows 7 yet.

10 viruses including 2 trjans and a rootkit, and for some reason my AV software was asleep at the wheel.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 17th Sep 2009 19:07
g/l with that

Combofix is a great program for helping remove rootkits but use with caution and backup anything vital before running it.

As for the functions, I have been reading lots about them so I might be able to ask a more informed question in a few days when I find out exactly what is actually needed.
sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 23rd Sep 2009 00:18
Hi Again, I have found a small problem which was exposed by a bug in my program. If you try to reconnect to the same database with dbconnect() then it causes the DBPro program to crash out. Of course there is no point trying to re-connect to a database you are already connected to, but it would probably be better if the dll just returned a success if attempted. (Or an `already connected` type error)

Using dbconnect works as described when connecting to different databases, it's only when trying to connect to the currently open database that the crash occurs.

As for the stored procedures, I tried using one I found on the net for levenshtein distance and while it would work from the mysql command line client, it would not work from within dbconn. The error returned is that the function does not exist. I also found a pre-compiled windows udf dll for lev.distance and installed that, but it seemed to make the mysql service crash too easily with malformed queries. (Possibly something I'm doing wrong). Anyway I gave up on it for now.
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 23rd Sep 2009 20:06
Ok, I'll check into the DBConnect issue. It's probably something simple and silly like I didn't check to see if there was a previous connection (and close it) before openning the next one.

After recovering from the virus infection, I've got about 50% of my applications reinstalled now. It's been a hell of a week.

I'll take a look into the specifics of stored procedures either today or tomorrow. It's something that sounds easy enough to add, I just need to check into it and implement it.

KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 23rd Sep 2009 21:37
Stored Procedures:

With a little bit of investigation into ODBC and how it handles things, Stored Procedures will take a little bit of time to put together, but it is possible. I need to take a bit and study it to figure out the cleanest and best way to present those commands to DBPro. I'll keep you posted here on progress.

Mnemonix
14
Years of Service
User Offline
Joined: 2nd Dec 2002
Location: Skaro
Posted: 1st Oct 2009 11:36
Does this plugin support SQLite or the open office database format?

Mnemonix
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 1st Oct 2009 19:46
It supports anything that has an ODBC driver. (in theory...)

I've not tested it with everything of course, but it works with MySQL, MS SQL, and MS Access, so I don't think SQLite should be a problem.

sladeiw
8
Years of Service
User Offline
Joined: 16th May 2009
Location: UK
Posted: 2nd Oct 2009 11:33
A really neat feature would be to be able to reference all the specific datatype commands with either number OR name. eg:

Return integer = DBGetInt32(Row, Column)
or
Return integer = DBGetInt32(Row, "ColumnName")

I know you can do this with DBGetDataByColName but all the specific type commands are much more intuitive.

Probably a pain determining the correct type of argument passed from DBpro though.
KISTech
9
Years of Service
User Offline
Joined: 8th Feb 2008
Location: Aloha, Oregon
Posted: 2nd Oct 2009 20:05
I haven't tried an overloaded call yet. I'm kind of new to this DLL plugin making deal, but I can give it a shot. It may be a while though. I'm refocusing my attention on one of our big projects that is falling behind.

Login to post a reply

Server time is: 2017-07-23 23:29:06
Your offset time is: 2017-07-23 23:29:06