The DBDataSource manager also allows update on joined queries, but it is not tested.
You find the text of the generated sql statements in the unixODBC trace.
The current interface accepts a query string, a table name, the prinary keys and the list of columns.
The DBDataSource methods are:
void setQuery(const QString query, bool removeTables = false);
void addTable(const char *table, const char *primaries, const char *columns);
A comfortable wrapper should encapsulate the sql details.
But up to now this is what you get:
You can connect a detail datasouce to a master datasource. The result set of the detail source is restricted to the detail records of the current record of the master source. Look at DataWidgetTest7 for an example.
The DBDataSource method:
void setMaster(DBDataSource *master, const char *join, const char *init);
A detail datasource needs a query with a '?' for each foreign key eg. "select id, mid, avarchar from detail where mid = ?". You have to assign the master column positions to the parameter positions. The string "join" describes the assignment - pairs of index=index seperated by blanks eg. "1=0 2=1". The first one is the index of the field of the master datasource. The second is the index of the parameter.
If you assign master column position to detail colum positions, appending a new record will initialize the foreign keys from the corresonding fields. The string "Init" consists of pairs of index=index seperated by blanks eg. "1=0 2=1". The first one is the index of the field of the master datasource. The second is the index of the field of the detail source.
This chapter shows a tiny application.
It uses Mysql via a odbc DSN "mysqltest". The "Test Database" chapter shows the create statement of the "master" table. I hope, after reading the demo, you understand the datasouredemo classes.
demo.h declares a qt widget class:
// The demo application uses some qt classes.
#include <qapp.h>
#include <qmainwindow.h>
#include <qmessagebox.h>
#include <qlayout.h>
// It opens a odbc connection.
#include <sql.h>
#include <dbrecordodbc3.h>
// The DataWidget classes
#include <dbdatasource.h>
#include <dbgrid.h>
#include <dblineedit.h>
#include <dbnavigator.h>
// A simple Qt Widget
class Demo : public QMainWindow
{
Q_OBJECT
SQLHENV henv;
SQLHDBC hdbc;
DBDataSource *data;
public:
Demo();
~Demo();
protected slots:
void dataStateChanged();
};
#include "demo.h"
// The constructor creates a databasec connection, a DBDataSource
// manager and some widgets.
Demo::Demo()
{
// of cause, you can use SQLAllocEnv, SQLAllocConnect, SQLDriverConnect
QString error;
if ( !DBRecordODBC3::connect("DSN=mysqltest;OPTION=1", &henv, &hdbc, &error) )
QMessageBox::critical( this, "Database Error", error);
// create a datasource
data = new DBDataSource(new DBRecordODBC3(henv, hdbc));
connect(data, SIGNAL(stateChanged()), this, SLOT(dataStateChanged()));
data->setQuery("select id, anint, avarchar from master order by anint");
data->addTable("master", "id=0", 0);
data->open();
data->first();
// a layout
QGridLayout *lo1 = new QGridLayout (this, 3, 1, 4);
lo1->setRowStretch(0, 1);
lo1->addRowSpacing(1, 20);
lo1->addRowSpacing(2, 20);
// create some widgets
DBGrid *g1 = new DBGrid(data, this);
lo1->addWidget(g1, 0, 0);
g1->addColumn(0);
g1->addColumn(1);
g1->addColumn(2);
DBLineEdit *ed1 = new DBLineEdit(data, 2, this);
lo1->addWidget(ed1, 1, 0);
DBNavigator *nav1 = new DBNavigator(data, this);
lo1->addWidget(nav1, 2, 0);
}
// The destructor closes the database connection.
Demo::~Demo()
{
delete data;
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// This slot shows the errormessages.
void Demo::dataStateChanged()
{
if ( data->isError() )
QMessageBox::critical(this, "Error", data->getErrorInfo());
}
// This it the usual Qt main function.
int main( int argc, char ** argv ) {
QApplication a( argc, argv );
a.setFont(QFont("helvetica",12));
Demo *demo = new Demo();
a.setMainWidget(demo);
demo->show();
return a.exec();
}
moc demo.h -o demo.moc.cpp
cc demo.cpp demo.moc.cpp -o demo -ldatawidget -lodbc -lqt -lX11
-I/usr/lib/qt/include -I/usr/X11R6/include -L/usr/X11R6/lib
The default values of the query strings go with this database:
# # Table structure for table 'master' # CREATE TABLE master ( id int NOT NULL auto_increment, anint int, avarchar varchar(100), areal real, anamount decimal(14,2), adate date, atime time, adatetime datetime, anbool tinyint(1), atext text, ablob blob, PRIMARY KEY (id) ); # # Dumping data for table 'master' # INSERT INTO master VALUES (1,0,'',1.2346,0.00,'1903-02-01','04:05:06','1909-08-07 10:11:12',0,'',''); # # Table structure for table 'detail' # CREATE TABLE detail ( mid int(10) NOT NULL, id int(5) NOT NULL auto_increment, avarchar varchar(255), KEY id (id), PRIMARY KEY (mid,id) ); # # Table structure for table 'lookup' # CREATE TABLE lookup ( id smallint(1) NOT NULL auto_increment, info varchar(255), PRIMARY KEY (id) ); # # Dumping data for table 'lookup' # INSERT INTO lookup VALUES (1,'lookup one'); INSERT INTO lookup VALUES (2,'lookup two'); INSERT INTO lookup VALUES (3,'lookup three'); INSERT INTO lookup VALUES (4,'lookup four'); INSERT INTO lookup VALUES (5,'lookup five'); INSERT INTO lookup VALUES (6,'lookup six'); INSERT INTO lookup VALUES (7,'lookup seven');