Alternative for ODBC

[blueznl]

Any suggestions for an alternative to ODBC? I'd rather do all configuring from inside a PureBasic application, and don't like to have the user mucking around with ODBC settings. Especially as it turns out there are differences to 32 bits versus 64 bits...

I need to talk to a MySQL database as well as an MSSQL database. Haven't even tried talking to the latter, to be honest...

[bamsagla]

Hello blueznl.

For MYSQL you could maybe use the Connector/C library which is available on http://dev.mysql.com/downloads/connector/c/ for most systems.
An example for connecting to the database server as a first start is shown in this topic: http://forums.purebasic.com/german/view ... =3&t=22441 (don't know if this is up-to-date).
I wanted to take a look to this lib for myself, but sadly I don't have the time right now.
The documentation for the usage of the dll is here: http://dev.mysql.com/doc/refman/5.6/en/connector-c.html.
If I remember correctly, the native PureBasic database commandset cannot be used in combination with this library - so you have to create your own commandset.
Maybe if you have any success, a little code example would be great. If I suddenly have time to give this a try and this is what you are looking for I'll try to post a little code example.

As for MSSQL I'm sorry; only know the native client connection but maybe there is also a DLL or something...

Bye, Harry.

[Blankname]

Something Fred should really push for is a license to implement native MySQL support. It's the most used database engine and we can't even utilize it without having to setup ODBC (not a very good option) or writing a wrapper for it.

[Fred]

license for mysql redistribution are very high (many tousand of dollars), and it won't allow you to use it in your own software anyway. Feel free to subscribe it and use the mysql API, or better, switch to postgresql which is a real free and opensource database. Mysql is now handled by Oracle, and Oracle isn't know for their small prices.

[LuCiFeR[SD]]

may I heartily recommend mariadb

[Fangbeast]

may I heartily recommend mariadb

Good idea. Written by the founder of MySql (funny story behind the name too) and totally compatible with it.

[Fred]

Yes mariadb could be supported, and would need an extra DLL as the client connector is LGPL.

[bamsagla]

Hi Fred,

you are right, postgresql would really be worth a try. I'll give that a chance for my actual project, because there always are windows-related dependencies with odbc connections. The point is, I really like the mysql syntax because it's really handy and stable. But postgresql is a really good suggestion, because the complete database management and database connectivity is purebasic-integreated and should so be platform-independent.

Once more interesting to switch to postgresql because in the moment I have a "show stopper" on my project related to this bug report with unicode, odbc and getdatabasestring: http://www.purebasic.fr/english/viewtop ... =4&t=53943

Thanks for pushing me to the obvious and also thanks for your immense work.
Harry.

[blueznl]

I've been messing around with the different DB's, and was a bit puzzled by the whole MySQL license issue. It's a weird thing... it seems all sorts of different licenses apply, LGPL, GPL, proprietary, $$$ Oracle licenses...

As far as I can tell, it's a perfectly free database that you can install and use, BUT for all practical purposes you cannot build anything into your code that talks directly to it, and as far as I could tell you could not even include a MySQL DLL with your code (if there is one).

I never heard of MariaDB before, but it seems to be a drop-in replacement for MySQL. This is quite interesting...

Which brings up a 1 million dollar question:

As MariaDB is supposed to be a drop-in replacement, does that mean that MariaDB code (if included with PureBasic) would be able to talk directly to MariaDB as well as MySQL databases without resorting to ODBC?

That would be a great fix and pro for PureBasic, being able to talk to different databases without having to resort to ODBC.

[Fred]

You can already talk to postgresql and sqlite without having ODBC which covers main cases. ODBC is here to cover all extra cases.

[blueznl]

Unfortunately the few DB's I needed to talk to were either MySQL or MSSQL... poor me!

[captain_skank]

Yep another +1 for MariaDB support to included.

I think they even have their own c connector now.

I use MariaDB instead off Mysql because of the unclear licensing situation - but I still have to use Oracle's ODBC connector which works fine but is a pain.

Wish i'd used postgres but I'm so far down the mysql line now that changing would mean a massive alterations

[bamsagla]

Hi captain_skank,

I just ported my actual project and it was a good success. Some things are different from mysql to postgresql. Here are my moderate experiences so far:

Setting a "primary key" index for tables at creation:
mysql: DatabaseQuery (0, "CREATE TABLE somename (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), ...)")
postgresql: DatabaseQuery (0, "CREATE TABLE somename (id SERIAL UNIQUE PRIMARY KEY, ...)")

Reading index back when inserting a new value:
mysql: DatabaseUpdate (0, "INSERT INTO somename (test, newtest) VALUES (20, 'test');")
mysql: DatabaseQuery (0, "SELECT LAST_INSERT_ID();")
postgresql: DatabaseQuery (0, "INSERT INTO somename (test, newtest) VALUES (20, 'test') RETURNING id;")

Every other command like CONCAT, INNER JOIN, or JOINs generally are working just like before on mysql even most ALTER TABLE commands are the same, I believe.

The data types for table creation are even easier than on mysql. On postgresql I only use "SERIAL UNIQUE PRIMARY KEY" for index columns, "INTEGER" for integer numbers, "CHARACTER VARYING (x)" for strings, where x is the maximum length of the field but with variable length of the string, "TEXT" for 'unlimited' text strings and "NUMERIC(x,y)" for float numbers, where x is the complete length of the number with decimals and y are decimals only - so NUMERIC(9,2) holds for example 1234567.89 (where the dot is the decimal separator).

But the best thing when migrating to postgresql: now every ODBC-related code is gone (and this was some amount of connection-, disconnection- and error-correction-code). Now I even can write logoff-values back to the database when the user shuts the computer down without closing the programm accordingly - with mysql the odbc had been gone before I could do all database clearances.

Sorry for gettin' off-topic this far, but maybe this helps someone, Harry.