PDA

View Full Version : QSqlDatabase: Insert values from a database to another



Alberto7
8th October 2015, 19:35
Hello,
Sorry for my English. I'm trying to select some data from a database table in order to insert the into a table from another database.
Clarifications:

- The table from where the data is extracted (selected) is named "Partes" and belongs to the "Hospital" database.

- The table where I want to insert the data is named
"Solicitudes_Trabajo" and belongs to the "Empresa" database.

- "seleccionar2" extracts (selects) all the data that belongs to the ID
written by the user in the lineEdit. These data belongs to the table
"Partes" from "Hospital" database.

- "guardar_s2" tries to select (between the data extracted with
"seleccionar2") the data "N_Solicitud" and "Fecha_Emision" that
belong to the "Hospital" database and correspond to the ID written by
the user, and tries to insert them in "Solicitudes_Trabajo" table
from the "Empresa" database.


hospital=QSqlDatabase::addDatabase("QSQLITE");
hospital.setDatabaseName("C:/Sqlite3/Hospital.sqlite");

if(hospital.open()){
qDebug()<<"11.Se ha conectado a la base de datos Hospital";
}else{
qDebug()<<"11.ERROR. No se ha conectado a la base de datos Hospital";
}

QSqlQuery seleccionar2;
seleccionar2.prepare("SELECT*FROM Partes WHERE N_Parte=:ID");
seleccionar2.bindValue(":ID",ui->lineEditN_Parte->text());

if(seleccionar2.exec())
{
qDebug()<<"12.Los datos del parte se han seleccionado correctamente";
}else{
qDebug()<<"12.ERROR. Los datos del parte no se han seleccionado correctamente";
qDebug()<<"12.ERROR:"<<seleccionar2.lastError();
}

seleccionar2.next();

//-----------------------------------------------------------------
//-----------------------------------------------------------------
//-----------------------------------------------------------------

QSqlQuery guardar_s2;
guardar_s2.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision) "
"VALUES (:N_Solicitud, :Fecha_Emision)");
guardar_s2.bindValue(":N_Solicitud", seleccionar2.value(0).toByteArray().constData());
guardar_s2.bindValue(":Fecha_Emision", seleccionar2.value(1).toByteArray().constData());

if(guardar_s2.exec( ))
{
ui->label_Guardar->setText("Solicitud guardada correctamente");
qDebug()<<"13.Los datos del parte se han guardado en la Solicitud de Trabajo";
}
else
{
ui->label_Guardar->setText("La solicitud no se ha guardado correctamente");
qDebug()<<"13.ERROR. Los datos del parte no se han guardado en la Solicitud de Trabajo";
qDebug()<<"13.ERROR:"<<guardar_s2.lastError();
}

This code shows this error:


13.ERROR: QSqlError("", "Parameter count mismatch", "")

It's obvious because the database that is open when it tries to insert the data is "Hospital" database and not "Empresa" database that is where it should insert the data.
One of my attempts to solve it was opening "Empresa" database once again in the place that I've written the three lines of dashes with this code:


empresa=QSqlDatabase::addDatabase("QSQLITE");
empresa.setDatabaseName("C:/Sqlite3/Empresa.sqlite");

if(empresa.open()){
qDebug()<<"8.Se ha conectado a la base de datos Empresa";
}else{
qDebug()<<"8.ERROR. No se ha conectado a la base de datos Empresa";
}

But it was expected that it doesn't solve the problem.

Comes to my mind as a possible solution the idea of saving the value:

seleccionar2.value(0).toByteArray().constData() in a variable that doesn't need to access to the database.
But I don't know how it could be possible to save this value inside a variable.

Could anyone help me with this last possible solution of saving the value in a variable?

Does anyone comes up with a better idea?

Thank you very much!

Alberto7
8th October 2015, 19:37
Hello,
Sorry for my English. I'm trying to select some data from a database table in order to insert the into a table from another database.
Clarifications:

- The table from where the data is extracted (selected) is named "Partes" and belongs to the "Hospital" database.

- The table where I want to insert the data is named
"Solicitudes_Trabajo" and belongs to the "Empresa" database.

- "seleccionar2" extracts (selects) all the data that belongs to the ID
written by the user in the lineEdit. These data belongs to the table
"Partes" from "Hospital" database.

- "guardar_s2" tries to select (between the data extracted with
"seleccionar2") the data "N_Solicitud" and "Fecha_Emision" that
belong to the "Hospital" database and correspond to the ID written by
the user, and tries to insert them in "Solicitudes_Trabajo" table
from the "Empresa" database.


hospital=QSqlDatabase::addDatabase("QSQLITE");
hospital.setDatabaseName("C:/Sqlite3/Hospital.sqlite");

if(hospital.open()){
qDebug()<<"11.Se ha conectado a la base de datos Hospital";
}else{
qDebug()<<"11.ERROR. No se ha conectado a la base de datos Hospital";
}

QSqlQuery seleccionar2;
seleccionar2.prepare("SELECT*FROM Partes WHERE N_Parte=:ID");
seleccionar2.bindValue(":ID",ui->lineEditN_Parte->text());

if(seleccionar2.exec())
{
qDebug()<<"12.Los datos del parte se han seleccionado correctamente";
}else{
qDebug()<<"12.ERROR. Los datos del parte no se han seleccionado correctamente";
qDebug()<<"12.ERROR:"<<seleccionar2.lastError();
}

seleccionar2.next();

//-----------------------------------------------------------------
//-----------------------------------------------------------------
//-----------------------------------------------------------------

QSqlQuery guardar_s2;
guardar_s2.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision) "
"VALUES (:N_Solicitud, :Fecha_Emision)");
guardar_s2.bindValue(":N_Solicitud", seleccionar2.value(0).toByteArray().constData());
guardar_s2.bindValue(":Fecha_Emision", seleccionar2.value(1).toByteArray().constData());

if(guardar_s2.exec( ))
{
ui->label_Guardar->setText("Solicitud guardada correctamente");
qDebug()<<"13.Los datos del parte se han guardado en la Solicitud de Trabajo";
}
else
{
ui->label_Guardar->setText("La solicitud no se ha guardado correctamente");
qDebug()<<"13.ERROR. Los datos del parte no se han guardado en la Solicitud de Trabajo";
qDebug()<<"13.ERROR:"<<guardar_s2.lastError();
}

This code shows this error:


13.ERROR: QSqlError("", "Parameter count mismatch", "")

It's obvious because the database that is open when it tries to insert the data is "Hospital" database and not "Empresa" database that is where it should insert the data.
One of my attempts to solve it was opening "Empresa" database once again in the place that I've written the three lines of dashes with this code:


empresa=QSqlDatabase::addDatabase("QSQLITE");
empresa.setDatabaseName("C:/Sqlite3/Empresa.sqlite");

if(empresa.open()){
qDebug()<<"8.Se ha conectado a la base de datos Empresa";
}else{
qDebug()<<"8.ERROR. No se ha conectado a la base de datos Empresa";
}

But it was expected that it doesn't solve the problem.

Comes to my mind as a possible solution the idea of saving the value:

seleccionar2.value(0).toByteArray().constData() in a variable that doesn't need to access to the database.
But I don't know how it could be possible to save this value inside a variable.

Could anyone help me with this last possible solution of saving the value in a variable?

Does anyone comes up with a better idea?

Thank you very much!

ChrisW67
8th October 2015, 22:07
You can hold both database open at the same time by naming one or both connections and building queries on the correct connection:


// One time set up independent connections
hospDb = QSqlDatabase::addDatabase("QSQLITE", "hospital");
hospDb.setDatabaseName("...");
hospDb.open();
EmpDb = QSqlDatabase::addDatabase("QSQLITE", "empressa");
EmpDb.setDatabaseName("...");
EmpDb.open();

// use connections independently
hospDb = QSqlDatabase::database("hospital");
QSqlQuery hospQry(hospDb);
hospQry.prepare("..."); ...

EmpDb = QSqlDatabase::database("empressa");
QSqlQuery empQry(EmpDb);

Alberto7
9th October 2015, 03:31
I've saved every value of each database in QStrings like these:


QString _Telefono = seleccionar2.value(8).toByteArray().constData();
QString _Tecnico_Asignado = seleccionar2.value(0).toByteArray().constData();
QString _Estado_Solicitud = seleccionar2.value(7).toByteArray().constData();

And when I use this code:


QSqlQuery guardar_s;
guardar_s.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision, Unidad_Hospitalaria,"
"Codigo_Equipo, Equipo, Marca)"
"VALUES (:N_Solicitud, :Fecha_Emision, :Unidad_Hospitalaria, :Codigo_Equipo, :Equipo,"
":Marca)");
guardar_s.bindValue(":N_Solicitud", _N_Solicitud);
guardar_s.bindValue(":Fecha_Emision", _Fecha_Emision);
guardar_s.bindValue(":Unidad_Hospitalaria", _Unidad_Hospitalaria);
guardar_s.bindValue(":Codigo_Equipo", _Codigo_Equipo);
guardar_s.bindValue(":Equipo", _Equipo);
guardar_s.bindValue(":Marca", _Marca);

It works fine, and inserts the values into the table perfectly, but when I use this other code;


QSqlQuery guardar_s;
guardar_s.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision, Unidad_Hospitalaria, Codigo_Equipo,"
"Equipo, Marca, Modelo, N_Serie, Localizacion, Unidad_Tecnica, Peticionario, Telefono, "
"Descripcion_Solicitud, Tecnico_Asignado, Tipo_Solicitud, Estado_Solicitud) "
"VALUES (:N_Solicitud, :Fecha_Emision, :Unidad_Hospitalaria, :Codigo_Equipo, :Equipo, :Marca, :Modelo,"
":N_Serie, :Localizacion, :Unidad_Tecnica, :Peticionario, :Telefono, :Descripcion_Solicitud,"
":Tecnico_Asignado, :Tipo_Solicitud, :Estado_Solicitud)");
guardar_s.bindValue(":N_Solicitud", _N_Solicitud);
guardar_s.bindValue(":Fecha_Emision", _Fecha_Emision);
guardar_s.bindValue(":Unidad_Hospitalaria",_Unidad_Hospitalaria);
guardar_s.bindValue(":Codigo_Equipo", _Codigo_Equipo);
guardar_s.bindValue(":Equipo", _Equipo);
guardar_s.bindValue(":Marca", _Marca);
guardar_s.bindValue(":Modelo", _Modelo);
guardar_s.bindValue(":N_Serie", _N_Serie);
guardar_s.bindValue(":Localizacion", _Localizacion);
guardar_s.bindValue(":Unidad_Tecnica", _Unidad_Tecnica);
guardar_s.bindValue(":Peticionario", _Peticionario);
guardar_s.bindValue(":Telefono", _Telefono);
guardar_s.bindValue(":Descripcion_Solicitud", _Descripcion_Solicitud);
guardar_s.bindValue(":Tecnico_Asignado", _Tecnico_Asignado);
guardar_s.bindValue(":Tipo_Solicitud", "a");
guardar_s.bindValue(":Estado_Solicitud", _Estado_Solicitud);

It doesn't work and shows the error:
.ERROR: QSqlError("", "Parameter count mismatch", "").

Are these too much data to insert for Qt Creator? Why it is able to insert 6 values but it isn't able to insert 16 values?

jefftee
9th October 2015, 07:10
Since you are using SQLITE, you could ATTACH DATABASE (https://www.sqlite.org/lang_attach.html) to a 2nd database and simplify your code IMHO. Use DETACH DATABASE (https://www.sqlite.org/lang_detach.html) when you are ready to close your database, etc.

That way you only have to handle one database connection and simplify your code.