PDA

View Full Version : QSqlite in QT4



sophister
3rd April 2009, 16:19
I am trying to use QSqlite in my QT project.
according to the QT assistant, I use the QSqlquery to insert data into database, but failed!!
Here's my code:
QSqlQuery insert(db);
insert.exec("select * from Person");
int num = insert.size();
if( !insert.isActive())
QMessageBox::information(this, "Query not activited", "Sorry but this query is not activited!");
bool ok = insert.exec( QString("insert into Person values (%1, \'%2\', \'%3\', %4)").arg(num + 1).arg(name).arg(sex).arg(age));

Plz tell me how to make it if anybody know it.
Thanks!!

JhonJames
3rd April 2009, 16:25
Hi!

It's: %4, %1, %2, %3

With '%' before digits.

mcosta
3rd April 2009, 16:30
1) The formatting is wrong: "1%" instead of "%1"

2) You open the database before use QSqlQuery?

For Example


QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbName);
db.open();


3) Using "tr" in QUERY string it's dangerous (wrong). You must not translate queries.

sophister
3rd April 2009, 16:43
Thanks, I do open the database before using QSqlQuery.
I want to insert some virables into the table. If I don't use tr, how can I insert the virables then??
My platform is XP.

sophister
3rd April 2009, 16:44
thanks, I have corrected it.
But now what do trouble me is how to insert data into the table.
It's really difficult.

mcosta
3rd April 2009, 16:46
I want to insert some virables into the table. If I don't use tr, how can I insert the virables then??



insert.exec( QString("insert into Person values (%4, \'%1\', \'%2\', %3)").arg(num + 1).arg(name).arg(sex).arg(age))


Pay attention to values type. I think "name" and "sex" are strings.

sophister
3rd April 2009, 16:50
I find some guides to SQLite, whick say that we can use the following code to insert data into one table----

insert into table_name values(data1, data2, data3, ...);


But when I use it, the application just cannot do that successfully.

mcosta
3rd April 2009, 16:52
I edited my previous post.

The problem is that some values are strings (name and sex); in this case you have to wrap with ''


insert.exec( QString("insert into Person values (%4, \'%1\', \'%2\', %3)").arg(num + 1).arg(name).arg(sex).arg(age))

mcosta
3rd April 2009, 16:53
I edited my previous post.

The problem is that some values are strings (name and sex); in this case you have to wrap with ''


insert.exec( QString("insert into Person values (%4, \'%1\', \'%2\', %3)").arg(num + 1).arg(name).arg(sex).arg(age))

__________________

sophister
3rd April 2009, 16:59
yeah, you guess it!!
I really appreciate your help!!!
I successfully insert the values into the table.

But just one problem, the "age"'s type is int, how can I insert it into the table?

JhonJames
3rd April 2009, 17:06
Like strings... But without ' ':



... QString("INSERT INTO table VALUES ( %1, %2 )").arg( int1 ).arg( int2 ) ...


Note:
String, date with ' '.
Double, float, integer: without ' '.

sophister
3rd April 2009, 17:13
you see, when I show the table in the QTextEdit, the INTEGER cannot display appropriately.

I use the following code to display the int age---
age = query.value(3).toInt();

Where am I wrong??

sophister
3rd April 2009, 17:16
yeah, thanks!!
But now I have another question.

you see, when I show the table in the QTextEdit, the INTEGER cannot display appropriately.

I use the following code to display the int age---
age = query.value(3).toInt();

Where am I wrong??
Would you plz tell me??

JhonJames
3rd April 2009, 17:29
mmm...



int age = query.value(3).toInt();
...
textEdit->append( QString("Age = %1").arg( age ) );


That should work... If "age" is in column 3 of table and "age"'s type is Integer.

sophister
3rd April 2009, 17:44
Thank you very much!!
But why the following codes don't work----
name = query.value(1).toString();
sex = query.value(2).toString();
age = query.value(3).toInt();
textEdit->append( name + '\t' + sex + '\t' +age);
Sorry to trouble you again.

JhonJames
3rd April 2009, 17:50
QString name = query.value(1).toString();
QString sex = query.value(2).toString();
int age = query.value(3).toInt();
textEdit->append( QString("%1\t%2\t%3").arg( name ).arg( sex ).arg( age ) );

//or

textEdit->append( name + "\t" + sex + "\t" + QString::number(age) );

sophister
3rd April 2009, 18:04
Thanks!!
I really learn a lor from you!!

sophister
3rd April 2009, 18:12
Sorry to bother you again, I use the following codes to insert data into table, but I am sure there is something wrong with the following codes---
QSqlQuery insert(db);
insert.exec("select * from Person");
int num = insert.size();

How can I get the numbers of the records in this table??
QSqlQuery::size, or QSqlQuery::numberRowAffected(), do not work!!
They two return -1!!
Where is wrong??

JhonJames
3rd April 2009, 18:24
Try with this:


QSqlQuery insert(db);
if (!insert.exec("select * from Person"))
QMessageBox::critical(0, "Error", insert.lastError().text());
else
QMessageBox::information(0, "Successfull", QString("Query successfully with %1 rows affected").arg( insert.numRowsAffected() ));


With this code you can see the error or the number of rows affected by query.

talk2amulya
3rd April 2009, 18:35
try:



QSqlQuery query("SELECT * FROM Person", db);
int num = query.size();
textEdit->append("Number of persons: " + QString::number(num));

sophister
3rd April 2009, 18:44
I have tried this, but failed.
I changed it to
QSqlQuery insert(db);
insert.exec("select * from Person");
insert.last();
bool toInt;
int num = insert.value(0).toInt(&toInt) + 1;
insert.exec( QString("insert into Person values (%1, \'%2\', \'%3\', %4)").arg(num).arg(name).arg(sex).arg(age));

Only in this way, I inserted the values into the table!!
I don't know why the .size() return -1, nor does .numRowsAffected().

sophister
3rd April 2009, 18:46
I don't knwo why the .size() and .numRowAffected() both return -1.
It puzzled me.

JhonJames
3rd April 2009, 20:18
If you want insert a new row on database with column_0 = column_0_of_last_row + 1, you can try set AUTOINC to column_0.

Then, to insert new rows:



... QString("INSERT INTO table(column1_name, column2_name, column3_name) VALUES ( '%1', '%2', %3 )").arg( name ).arg( sex ).arg( age ) ...

mcosta
3rd April 2009, 21:12
For QSQLITE driver the size() function doesn't work.

Try
QSqlDriver::hasFeature

sophister
4th April 2009, 18:26
Then how can I get the size of the one SQLite database, or how can I insert data into the end of the table??I use the following codes and successfully insert into the table, but the QThelp says that QSqlQuery::last() is very slow. Is there another way to insert data into the end of the table??

QSqlQuery insert(db);
insert.exec("select * from Person");
insert.last();
insert.exec( QString("insert into Person values (%1, \'%2\', \'%3\', %4)").arg(num).arg(name).arg(sex).arg(age));

sophister
4th April 2009, 18:48
Yeah, but I did not find any functions to set AUTOINC to column_0 in the QSqlQuery??

sophister
5th April 2009, 13:52
Thank you!! I succeeded inserting values into the table using your approach.