Create new table and naming it with user input from QLineEdit
-
Im making a MYSQL database using QT5 on my raspberry pi. Already made all app and localhost and everything ok so far. My question is on how to create a new table named by user from a qlineEdit on the UI, when pressing a button?
Here the code I have for the button slot:
void MainWindow::on_pushButton_clicked() { QSqlQuery query; query.prepare( "CREATE TABLE `QT`.`tlb` ( `id` INT NOT NULL AUTO_INCREMENT , `desde` VARCHAR(10) NOT NULL , `hasta` VARCHAR(10) NOT NULL , `name` VARCHAR(50) NOT NULL , `color` TEXT NOT NULL , `circuits_qty` INT(65) NOT NULL , `customer` TEXT NULL , `program` TEXT NULL , `family` TEXT NULL , `location` VARCHAR(40) NULL , `con1` LONGBLOB NULL , `con2` LONGBLOB NULL , `con3` LONGBLOB NULL , `con4` LONGBLOB NULL , `con5` LONGBLOB NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB"); QString name; name = ui->lineEdit->text(); query.bindValue(":'tlb', 'name'"); // query.prepare("ALTER TABLE 'tlb' RENAME TO name;) query.exec(); }
As you can see, code works when just giving a name by me. But when trying to make the user give the name to the table, nothing happens. I made a QString to get the name from the qlineEdit store the name then, tried to bind it, but did not work, then tried an alter table but still nothing happened. Any help is more than welcome. Commented out the alter table btw just to go back and forward trying different ways to get this works.
-
@GARUCHIRAZUMAN I never used query.prepare or bind values but in general you can substitute in two ways:
- Less elegant way:
query.exec("create table "+ui->lineEdit->text()+"(id integer primary key, something varchar(100));");
That is rather lazy way and I would not recommend. - Proper substitution:
QString q = QString("create table %1(id integer primary key, something varchar(100));").arg(ui->lineEdit-text()); query.exec(q);
Also, when you need to specify text in the db query params take form
'%1'
since % parameters just insert literal.
Please read QString and QSqlQuery documentation for your version of Qt - I know that is a lot of reading but it is totally worth it. - Less elegant way:
-
@GARUCHIRAZUMAN said in Create new table and naming it with user input from QLineEdit:
query.bindValue(":'tlb', 'name'");
What is this?!
Makes no sense.
Should be:query.bindValue(":tlb", name);
You can see how it is done in the documentation: https://doc.qt.io/qt-5/qsqlquery.html
-
@GARUCHIRAZUMAN
Further to the points from @artwaw & @jsulm.I believe you will find that not all SQL statements allow for variable binding. I do not know how/where you discover which statements do/do not, but
CREATE TABLE
may be one which does not allow the table name to come from a bound variable. If that is the case you would need to construct your statement like @artwaw showed in his examples. -
@GARUCHIRAZUMAN One more thing: you can check how the executed query looks like: https://doc.qt.io/qt-5/qsqlquery.html#executedQuery
And please also check the errors: https://doc.qt.io/qt-5/qsqlquery.html#lastError -
@artwaw Thank you So much! Problem solved with both ways. I also spent sometime reading about QString and QSqlQuery (can't believe that QString has such a world of functions) and for now no more issues to keep progressing on my projects, thanks to you !