Dear visitor, welcome to QtForum.org.
If this is your first visit here, please read the Help. It explains in detail how this page works.
To use all features of this page, you should consider registering.
Please use the registration form, to register here or read more information about the registration process.
If you are already registered, please login here.

Qt Programming + Database

I've been learning how to program with Qt since the last few months, and now I have decided to make a program with Qt that can utilize a database. I did such thing before, but it was with VB6, and the DBMS I used was MS Access 2003, a pseudo-RDBMS actually. In VB6, I used the ADO Data Control to connect my application to this MS Access DB I made using the JET Engine v4.0.

Now how am I going to this with Qt? I've been using Kubuntu 10.04 LTS (Lucid Lynx) Linux distro most of the time since last month (I'm dual booting Windows and Linux, btw), and so far, I have Qt (I think it's v4.6.2) and Qt Creator IDE installed on both OS's to make my applications.

What should I install or what should I do in order for me to get started with this kind of stuff? You can recommend a better RDBMS for me also, preferably, one that I can use on multiple-platforms (cross-platform). Actually, I found an RDBMS from the Internet, MySQL... but I don't know yet how to connect it with a Qt application. I've actually downloaded the compressed archive, no-install MySQL RDBMS when I was logged in to my Windows XP SP2 (I have Qt Creator with this OS as well) yesterday and made some basic databases with it. I was looking for the location of the database I've created aided with some Google searching, and found out the path for the databases I've created. But, what I found was a folder with my database name and some files in it(I think those are for the database' table information). There are MYD (Table data?), MYI (Table indices?), OPT (Table options?), and FRM (I don't know this one... ;p) files within that folder and while I was looking at those files I began to think if it's really possible to connect these databases I've created with MySQL to a Qt application.

Could you please give me a step-by-step procedure that applies on both aforementioned OS's, from installing the needed files / applications to making a simple Qt application that connects to a database?

The QT sql database objects are quite powerful. I've only wrapped and used the ODBC and Postgres QSqlDrivers myself so far. If you are familiar with ADO you should be able to pick up on how the QT classes work by looking through the examples. You may need to compile a specific database driver or plugin if you find yourse;f getting driver not loaded errors for whatever specific database technology you decide to go with.

"Live as if you were to die tomorrow, learn as if you were to live forever" - Gandhi

Ok, I downloaded pgSQL (the ZIP archive, not the one-click installer). I extracted the ZIP archive and did the following:

1. I have created a folder named "data" inside the main folder of pgSQL ("pgsql").
2. Entered directory "bin" thru Command Prompt (btw, I'm using Windows XP now...) and executed "initdb" by entering initdb -D ../data into the console.
3. Executed "pg_ctl" by entering "pg_ctl" -D "../data" -l logfile start into the shell.
4. I created a new DB by entering createdb DB1, where "DB1" is the name of my database.
5. Executed "psql", with the name of my DB as the parameter, as in "psql DB1"
6. I created a table named "People" by entering "CREATE TABLE People(..."
7. Added 2 data entries / rows.
8. Then I quit "psql"...
9. Entered pg_ctl -D ../data fast stop

After that, I checked if the database still exists by doing similar procedure as above and did "SELECT * FROM People". I could see the data entries but, where is the database file itself located? I mean, in MS-Access there's the MDB file, MS SQL Server has MDF I think, but how about Postgres? And now that I've created a basic database with pgSQL, could you please give me a step-by-step procedure on how am I going to connect this database with a Qt application? A basic Qt application, that could at least "insert", "modify", and "delete" rows of my DB's table.

Btw, do the classes I saw, from the link you've included with your post, comes with Qt v4? Or do I have to install somethin' else in order for me to do this kind of stuff? I have Qt 4 installed and Qt Creator as my Qt IDE, and pgSQL as my ORDMS. Are there anything else that should be downloaded and installed?

Sounds like you got a good start with pgsql ... the actual file can be stored in various places by default depending upon the version of postgres, the OS you are using, and your configuration settings. As with most databases it's better to backup and restore databases via the provided commands (pg_dump & pg_restore). postgres doesn't always save the file by the name of the database itself which is why you may be having trouble finding it but if you use the appropriate commands all you need is your database name.

As far as QT goes check out the SQL demos and experiment. Quick pointer on getting started you'll need to add

Source code

1

QT += sql

to your .pro file and

Source code

1

#include <QtSql>

to get all the headers for those classes I linked (or include just the ones you want). I'd also start by playing with QSqlDatabase and see if you can get a connection to your database first.

"Live as if you were to die tomorrow, learn as if you were to live forever" - Gandhi

Uhhmm... How do you connect your database with a Qt application dude? Doesn't pgSQL go like MS Access that has a "DB file" when you make a database (*.MDB)? Sorry dude, I'm still confused... hehehe... Anyway, perhaps there are tons of ways to connect a DB with a Qt app but the problem is I only know a single way to do such and you maybe also doing another way to do such... The only programming language that I can use to make programs that can connect to a database is VB6; MS Access as the DBMS. When I make this kind of application, I have my executable and the MDB file in the same folder. So whenever I have my VB6 application and MDB file to another PC, I can still have my VB6 application working fine. The reason I was looking for the "DB file" of the database I made with pgSQL is because of this concept. I thought that after knowing where my DB file is located, I could then put it in the same directory of the application I'll be making with my Qt IDE and then I'd try to connect it with my Qt app (aided with the link you've provided... thanks again... ) and utilize that DB to do some useful tasks.

Btw, isn't it that the actual file of my DB is stored in my "data" folder that I've created? Yeah, I can't find it in that folder, I thought that was the "DATADIR"?
Were you able to produce that "DB file" of your DB?

I hope I don't get annoying... I'm really having hard time getting this kind of stuff to work...

Hehehe, anyway, thanks again.. Thank you very much and God bless all...

MSAccess is not the same as databases like SQL Server or pgSQL ... rather than specifying a file you can reference the database by name and connect to the server hosting it. Let the database software and services worry about where files are you're a programmer not a DBA you don't need to care

Here's a quick and dirty example (sorry if I make typos):

Source code

#include <QtSql>
#include <QMessageBox>
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
// first we create a db object of the right type so it loads the QT sql driver we want
QSqlDatabase db(QSqlDatabase::addDatabase("QPSQL");
// then we assign members that will make up our equivalent to a connection string
db.setDatabaseName("DB1"); // using your example dbname from above
db.setHostName("localhost"); // host name of machine running pgsql
db.setUserName("shades"); // or whatever user
db.setPassword("password"); // you get the idea, set your desired options
if (!db.open()) // alternately you can pass user and pass to overloaded open
QMessageBox::critical(0, "Error", db.lastError().text());
// lastError() is a member of QSqlQuery too but I'll assume it works
QSqlQuery query(db); // new query using our open connection
query.prepare("SELECT * FROM People"); // make it
query.exec(); // do it
query.next(); // advance to result
QString result(query.value(0).toString()); // grab result in string format
QMessageBox::information(0, "Show me the People!", result);
return app.exec();
}

Check out QSqlTableModel and QTableView for a cool way to query and show results or perform queries without even knowing SQL syntax as well.

"Live as if you were to die tomorrow, learn as if you were to live forever" - Gandhi

Hehe, thanks again dude! Thank you very much... I appreciate your help regarding this matter, <respect++> hehe, lol...

Anyway, now that I know how pgSQL works... that pgSQL ain't like MS Access where you have to specify a file to connect your app with your DB, what if I have to run this application into another computer? I mean, I pretty much get the concept of connecting a pgSQL DB with a Qt app "locally" in my PC. But what if this application will be run into another PC? And that particular PC has no Internet connection or any way to connect with my PC? In VB6 + MSA, I would just specify the path of my MDB in my VB6 EXE source code and have my MDB file together with my VB6 EXE into another PC, so I could have my program running fine, connected with my DB. Is this possible in the case of Qt + pgSQL? Or the only way is to have a working network connection with my PC and the other PC?

*EDIT: Btw, I've found somethin' from the I-net....

pgSQL has this database named "template1". This DB contains some DB's information.
Inside the DB's directory (PGDATADIR) are some directories with some random numbers as its file name. This "random numbers" corresponds to a specific DB name. To find it out, I did the following:

1. In Command Prompt, I entered the command psql template1 to open "template1" DB.
2. Then I entered SELECT oid, datname FROM pg_database ORDER BY oid;

The "random numbers" of a DB is a DB's OID (Btw, I dunno what OID is, lol... I just found it somewhere from the I-net... That's what it's called according to that source of info... -_+).

Well, albeit I've found out the directory of my "DB1" database, I still have no idea if I could make this DB copied into another PC together with the Qt app that I'll be making so that I can make my Qt app, running into another PC, working with this DB I made. This is just an update of what I've found so far...

This post has been edited 1 times, last edit by "ShadeS_07" (Jun 24th 2010, 4:23pm)

Yeah that's what I was hinting at by pgsql not really using the dbname in the file name. It's usually some obfuscated string of numbers. As databases get more complex yet easier to use things like file manipulation become less important which to me is a good thing. SQL Server 2008 rocked my world when found I could make complex import packages in minutes through a graphical wizard with SSIS that would have required VB6 style DTS scripting and maybe a call out to a custom C++ app before in SQL 2000.

Anyway not having connectivity (internet, LAN, serial connection, etc) is going to cause a problem with cross machine communication no matter how you cut it. Using localhost or the ip address 127.0.0.1 still accesses the NIC card of the machine to resolve but doesn't require connectivity since it's local to that machine. However one of the benefits of a modern relational database is that you can persist your data in a single point of access while still backing it up or replicating to multiple locations. In other words you might do something like have a database server which is running postgres that has all the databases needed and then have server or client applications that can connect to it creating, accessing, and manipulating the shared data allowing them all to stay in sync with a consistent data model.

If making local copies of your database so that each application has it's own different version or information works for you then your data model is potentially simple enough to not need a database at all. You could save yourself a lot of overhead if what you really want is more along the lines of a configuration file, log file, or XML schema file for application specific settings. I don't really know what your goals are or what you're doing and it's not my call even if I did but it's always good to evaluate what the best solution for your problem is before you start coding B)

Have fun experimenting ...

EDIT: Oh yeah checkout using ":memory:" as your database name with QSqlDatabase as that allows you to use all the functionality while storing the data, tables, etc completely in your process memory. Obviously when the application closes you lose all this data unless you pipe it out to a file before closing but that could be useful for you as well. I believe this is how the QT SQL demos work without a real database to use.

"Live as if you were to die tomorrow, learn as if you were to live forever" - Gandhi

This post has been edited 1 times, last edit by "ajg_itracs" (Jun 24th 2010, 8:55pm)

Hehehe, yeah... The main purpose.... I forgot to mention about it... The main purpose of this is for me to learn how to make a C++ GUI program (aided with Qt) that can utilize a DB. Actually, I know how to make a program that can log somethin' into a file or read somethin' from a file using file I/O, but now I'm trying to learn how to make Qt application that can utilize a DB. As you could notice, I'm starting from the basics of it... Hehehe... -_+

Anyway, about what you've mentioned in your last post, does that mean that I can't do somethin' similar to what I did with VB6 + MSA? I made a VB6 program that utilizes an MSA DB (..an MDB file). It's a simple application, so it's just a single EXE file and an MDB file... that's all. I copied it from my PC (PC1) to my USB flash drive, then from my flash drive to another PC (PC2). No established network connection between the two computers, but I can still run the program on that PC (PC2) and it's able to utilize that DB I made for it. That's what exactly I want to do with Qt. Could that be possible dude?

Btw... Have you tried using MS SQL Server with Qt? Does MS SQL Server has its DB files organized by files (.MDF? Amiryt?) or by directories just like PostgreSQL? Well, just in case that the thing I want to do is impossible or cannot be done with Qt + PostgreSQL combination, let me ask this another question; Will it work with another DBMS (exempli gratia, Qt + MS SQL Server, Qt + MS Access, etc...)?

Oh, and I also tried the source code you've provided, at post No. 6... I've also got another question about that... What username / password should I enter? The login information that I use when I log in to Windows?

Hehehe, thanks again and sorry for my late response... I've also been searching/researching for answers regarding this matter so that I could also help myself answer my question...

Shades_07:
I don't have any example code, but it seems like you want a local database which you can ship easily with your application.
SQLite is made for that exact purpose. I suggest you try creating an sqlite database and try to access it through a qt sql connection class.