Results 1 to 5 of 5

Thread: QSqlDatabase: Insert values from a database to another

  1. 8th October 2015, 19:35 #1
    Alberto7
    Alberto7 is offline Beginner
    Join Date
    Oct 2015
    Location
    Barcelona, Spain
    Posts
    7
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows Android

    Default QSqlDatabase: Insert values from a database to another

    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.

    Qt Code: 
    1. hospital=QSqlDatabase::addDatabase("QSQLITE");
    2.             hospital.setDatabaseName("C:/Sqlite3/Hospital.sqlite");
    3.  
    4.             if(hospital.open()){
    5.                 qDebug()<<"11.Se ha conectado a la base de datos Hospital";
    6.             }else{
    7.                 qDebug()<<"11.ERROR. No se ha conectado a la base de datos Hospital";
    8.             }
    9.  
    10.                 QSqlQuery seleccionar2;
    11.                 seleccionar2.prepare("SELECT*FROM Partes WHERE N_Parte=:ID");
    12.                 seleccionar2.bindValue(":ID",ui->lineEditN_Parte->text());
    13.  
    14.                     if(seleccionar2.exec())
    15.                     {
    16.                         qDebug()<<"12.Los datos del parte se han seleccionado correctamente";
    17.                     }else{
    18.                         qDebug()<<"12.ERROR. Los datos del parte no se han seleccionado correctamente";
    19.                         qDebug()<<"12.ERROR:"<<seleccionar2.lastError();
    20.                     }
    21.  
    22.                     seleccionar2.next();
    23.  
    24.     //-----------------------------------------------------------------
    25.     //-----------------------------------------------------------------
    26.     //-----------------------------------------------------------------
    27.  
    28.     QSqlQuery guardar_s2;
    29.             guardar_s2.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision) "
    30.                           "VALUES (:N_Solicitud, :Fecha_Emision)");
    31.             guardar_s2.bindValue(":N_Solicitud", seleccionar2.value(0).toByteArray().constData());
    32.             guardar_s2.bindValue(":Fecha_Emision", seleccionar2.value(1).toByteArray().constData());
    33.  
    34.                 if(guardar_s2.exec( ))
    35.                 {
    36.                     ui->label_Guardar->setText("Solicitud guardada correctamente");
    37.                     qDebug()<<"13.Los datos del parte se han guardado en la Solicitud de Trabajo";
    38.                 }
    39.                 else
    40.                 {
    41.                     ui->label_Guardar->setText("La solicitud no se ha guardado correctamente");
    42.                     qDebug()<<"13.ERROR. Los datos del parte no se han guardado en la Solicitud de Trabajo";
    43.                     qDebug()<<"13.ERROR:"<<guardar_s2.lastError();
    44.                 }
    To copy to clipboard, switch view to plain text mode 

    This code shows this error:
    Qt Code: 
    1. 13.ERROR: QSqlError("", "Parameter count mismatch", "")
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code: 
    1. empresa=QSqlDatabase::addDatabase("QSQLITE");
    2.           empresa.setDatabaseName("C:/Sqlite3/Empresa.sqlite");
    3.  
    4.           if(empresa.open()){
    5.               qDebug()<<"8.Se ha conectado a la base de datos Empresa";
    6.           }else{
    7.               qDebug()<<"8.ERROR. No se ha conectado a la base de datos Empresa";
    8.           }
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code: 
    1. seleccionar2.value(0).toByteArray().constData()
    To copy to clipboard, switch view to plain text mode 
    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!
    Reply With Quote Reply With Quote
  2. 8th October 2015, 19:37 #2
    Alberto7
    Alberto7 is offline Beginner
    Join Date
    Oct 2015
    Location
    Barcelona, Spain
    Posts
    7
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows Android

    Default QSqlDatabase: Insert values from a database to another

    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.

    Qt Code: 
    1. hospital=QSqlDatabase::addDatabase("QSQLITE");
    2.             hospital.setDatabaseName("C:/Sqlite3/Hospital.sqlite");
    3.  
    4.             if(hospital.open()){
    5.                 qDebug()<<"11.Se ha conectado a la base de datos Hospital";
    6.             }else{
    7.                 qDebug()<<"11.ERROR. No se ha conectado a la base de datos Hospital";
    8.             }
    9.  
    10.                 QSqlQuery seleccionar2;
    11.                 seleccionar2.prepare("SELECT*FROM Partes WHERE N_Parte=:ID");
    12.                 seleccionar2.bindValue(":ID",ui->lineEditN_Parte->text());
    13.  
    14.                     if(seleccionar2.exec())
    15.                     {
    16.                         qDebug()<<"12.Los datos del parte se han seleccionado correctamente";
    17.                     }else{
    18.                         qDebug()<<"12.ERROR. Los datos del parte no se han seleccionado correctamente";
    19.                         qDebug()<<"12.ERROR:"<<seleccionar2.lastError();
    20.                     }
    21.  
    22.                     seleccionar2.next();
    23.  
    24.     //-----------------------------------------------------------------
    25.     //-----------------------------------------------------------------
    26.     //-----------------------------------------------------------------
    27.  
    28.     QSqlQuery guardar_s2;
    29.             guardar_s2.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision) "
    30.                           "VALUES (:N_Solicitud, :Fecha_Emision)");
    31.             guardar_s2.bindValue(":N_Solicitud", seleccionar2.value(0).toByteArray().constData());
    32.             guardar_s2.bindValue(":Fecha_Emision", seleccionar2.value(1).toByteArray().constData());
    33.  
    34.                 if(guardar_s2.exec( ))
    35.                 {
    36.                     ui->label_Guardar->setText("Solicitud guardada correctamente");
    37.                     qDebug()<<"13.Los datos del parte se han guardado en la Solicitud de Trabajo";
    38.                 }
    39.                 else
    40.                 {
    41.                     ui->label_Guardar->setText("La solicitud no se ha guardado correctamente");
    42.                     qDebug()<<"13.ERROR. Los datos del parte no se han guardado en la Solicitud de Trabajo";
    43.                     qDebug()<<"13.ERROR:"<<guardar_s2.lastError();
    44.                 }
    To copy to clipboard, switch view to plain text mode 

    This code shows this error:
    Qt Code: 
    1. 13.ERROR: QSqlError("", "Parameter count mismatch", "")
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code: 
    1. empresa=QSqlDatabase::addDatabase("QSQLITE");
    2.           empresa.setDatabaseName("C:/Sqlite3/Empresa.sqlite");
    3.  
    4.           if(empresa.open()){
    5.               qDebug()<<"8.Se ha conectado a la base de datos Empresa";
    6.           }else{
    7.               qDebug()<<"8.ERROR. No se ha conectado a la base de datos Empresa";
    8.           }
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code: 
    1. seleccionar2.value(0).toByteArray().constData()
    To copy to clipboard, switch view to plain text mode 
    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!
    Reply With Quote Reply With Quote
  3. 8th October 2015, 22:07 #3
    ChrisW67
    ChrisW67 is offline Guru
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: QSqlDatabase: Insert values from a database to another

    You can hold both database open at the same time by naming one or both connections and building queries on the correct connection:
    Qt Code: 
    1. // One time set up independent connections
    2. hospDb = QSqlDatabase::addDatabase("QSQLITE", "hospital");
    3. hospDb.setDatabaseName("...");
    4. hospDb.open();
    5. EmpDb = QSqlDatabase::addDatabase("QSQLITE", "empressa");
    6. EmpDb.setDatabaseName("...");
    7. EmpDb.open();
    8.  
    9. // use connections independently
    10. hospDb = QSqlDatabase::database("hospital");
    11. QSqlQuery hospQry(hospDb);
    12. hospQry.prepare("..."); ...
    13.  
    14. EmpDb = QSqlDatabase::database("empressa");
    15. QSqlQuery empQry(EmpDb);
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  4. 9th October 2015, 03:31 #4
    Alberto7
    Alberto7 is offline Beginner
    Join Date
    Oct 2015
    Location
    Barcelona, Spain
    Posts
    7
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows Android

    Default Re: QSqlDatabase: Insert values from a database to another

    I've saved every value of each database in QStrings like these:

    Qt Code: 
    1. QString _Telefono = seleccionar2.value(8).toByteArray().constData();
    2. QString _Tecnico_Asignado = seleccionar2.value(0).toByteArray().constData();
    3. QString _Estado_Solicitud = seleccionar2.value(7).toByteArray().constData();
    To copy to clipboard, switch view to plain text mode 

    And when I use this code:

    Qt Code: 
    1. QSqlQuery guardar_s;
    2.                 guardar_s.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision, Unidad_Hospitalaria,"
    3.                                   "Codigo_Equipo, Equipo, Marca)"
    4.                                   "VALUES (:N_Solicitud, :Fecha_Emision, :Unidad_Hospitalaria, :Codigo_Equipo, :Equipo,"
    5.                                    ":Marca)");
    6.                 guardar_s.bindValue(":N_Solicitud", _N_Solicitud);
    7.                 guardar_s.bindValue(":Fecha_Emision", _Fecha_Emision);
    8.                 guardar_s.bindValue(":Unidad_Hospitalaria", _Unidad_Hospitalaria);
    9.                 guardar_s.bindValue(":Codigo_Equipo", _Codigo_Equipo);
    10.                 guardar_s.bindValue(":Equipo", _Equipo);
    11.                 guardar_s.bindValue(":Marca", _Marca);
    To copy to clipboard, switch view to plain text mode 

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

    Qt Code: 
    1. QSqlQuery guardar_s;
    2.     guardar_s.prepare("INSERT INTO Solicitudes_Trabajo (N_Solicitud, Fecha_Emision, Unidad_Hospitalaria, Codigo_Equipo,"
    3.                       "Equipo, Marca, Modelo, N_Serie, Localizacion, Unidad_Tecnica, Peticionario, Telefono, "
    4.                       "Descripcion_Solicitud, Tecnico_Asignado, Tipo_Solicitud, Estado_Solicitud) "
    5.                   "VALUES (:N_Solicitud, :Fecha_Emision, :Unidad_Hospitalaria, :Codigo_Equipo, :Equipo, :Marca, :Modelo,"
    6.                       ":N_Serie, :Localizacion, :Unidad_Tecnica, :Peticionario, :Telefono, :Descripcion_Solicitud,"
    7.                       ":Tecnico_Asignado, :Tipo_Solicitud, :Estado_Solicitud)");
    8.     guardar_s.bindValue(":N_Solicitud", _N_Solicitud);
    9.     guardar_s.bindValue(":Fecha_Emision", _Fecha_Emision);
    10.     guardar_s.bindValue(":Unidad_Hospitalaria",_Unidad_Hospitalaria);
    11.     guardar_s.bindValue(":Codigo_Equipo", _Codigo_Equipo);
    12.     guardar_s.bindValue(":Equipo", _Equipo);
    13.     guardar_s.bindValue(":Marca", _Marca);
    14.     guardar_s.bindValue(":Modelo", _Modelo);
    15.     guardar_s.bindValue(":N_Serie", _N_Serie);
    16.     guardar_s.bindValue(":Localizacion", _Localizacion);
    17.     guardar_s.bindValue(":Unidad_Tecnica", _Unidad_Tecnica);
    18.     guardar_s.bindValue(":Peticionario", _Peticionario);
    19.     guardar_s.bindValue(":Telefono", _Telefono);
    20.     guardar_s.bindValue(":Descripcion_Solicitud", _Descripcion_Solicitud);
    21.     guardar_s.bindValue(":Tecnico_Asignado", _Tecnico_Asignado);
    22.     guardar_s.bindValue(":Tipo_Solicitud", "a");
    23.     guardar_s.bindValue(":Estado_Solicitud", _Estado_Solicitud);
    To copy to clipboard, switch view to plain text mode 

    It doesn't work and shows the error:
    Qt Code: 
    1. .ERROR: QSqlError("", "Parameter count mismatch", "").
    To copy to clipboard, switch view to plain text mode 

    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?
    Reply With Quote Reply With Quote
  5. 9th October 2015, 07:10 #5
    jefftee
    jefftee is offline Advanced user
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: QSqlDatabase: Insert values from a database to another

    Since you are using SQLITE, you could ATTACH DATABASE to a 2nd database and simplify your code IMHO. Use DETACH DATABASE when you are ready to close your database, etc.

    That way you only have to handle one database connection and simplify your code.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. How to insert values in QTreeWidget from Text file Using QlistIterator ?
    By Shahbaz in forum Newbie
    Replies: 2
    Last Post: 14th June 2015, 21:39
  2. QSqlDatabase cannot open database
    By nowrep in forum Qt Programming
    Replies: 2
    Last Post: 9th December 2011, 23:21
  3. Insert values into database table
    By l0ner in forum Newbie
    Replies: 3
    Last Post: 20th June 2011, 20:03
  4. Insert record into PostgreSQL table using default DB values
    By m15ch4 in forum Qt Programming
    Replies: 4
    Last Post: 10th May 2011, 13:19
  5. QSqlDatabase and Postgres - Insert bytes into database
    By goocreations in forum Qt Programming
    Replies: 1
    Last Post: 12th September 2010, 19:54

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.