Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QPSQL unable to COPY large files



  • Hello.
    I'm trying to make a function for create a large table from file using COPY.
    My function create the *.csv file properly.
    After, my function generate the query, who calls to a postgres function that uses COPY command.

    This is the postgres function:

    CREATE OR REPLACE FUNCTION importar_copy(
        _nombretabla character varying,
        _ruta character varying)
      RETURNS boolean AS
    $BODY$
    DECLARE
    texto text;
    BEGIN
    	texto = FORMAT ('COPY %I FROM %s DELIMITER %s NULL AS ''NULL''', _nombretabla, quote_literal(_ruta), quote_literal(chr(9)));
    	execute (texto);
    	raise notice '%',texto;
    	return true;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    

    Well, it's very curious (and very annoying) to see that looks as if the query executed from my application isn't not able to read so a large file, and looks as if it stop to read in any point of the file.
    I think that the file and the query is OK because if I copy the query generate in Qt and I run it in pgAdmin, there are no problem.

    This is the snippet of Qt where I buid the file and after I try to execute the query:

    QTextStream tConceptos;
    QFile file;
     if (tipo == SIMPLIFICADO)
     {
         file.setFileName("conceptos.csv");
         file.open(QIODevice::WriteOnly | QIODevice::Text);
         tConceptos.setDevice(&file);
         tConceptos.setCodec("UTF-8");
     }
    ---------------
    (feeding the QTextStream)
    tConceptos<<"field"<<"\t"<<"field"<<"\t"<<......."\n";
    ----------------
     QString cadenaimportar = "SELECT importar_copy('" + tabla + "','/path/conceptos.csv',)";
     QSqlQuery consulta(db);
     if(consulta.prepare(cadenaimportar))
     {
         consulta.exec();
     }
    qDebug()<<consulta.lastError()<<"--"<<consulta.lastQuery();
    file.close();
    

    I would like to know if I am doing anything wrong, or it is a limitation of Qt (QPSQL driver?) because I think that itsn't a problem of the csv files or queries, because I can execute the same queries without problems in pgAdmin.



  • Well, I have discovered my problem.
    I was trying to read the file before closing it:

    QString cadenaimportar = "SELECT importar_copy('" + tabla + "','/path/conceptos.csv',)";
     ......
    file.close();
    

    And it would be:

    file.close();
     QString cadenaimportar = "SELECT importar_copy('" + tabla + "','/path/conceptos.csv',)";
     QSqlQuery consulta(db);
     if(consulta.prepare(cadenaimportar))
     {
         consulta.exec();
     }
    qDebug()<<consulta.lastError()<<"--"<<consulta.lastQuery();
    


  • Well, I have discovered my problem.
    I was trying to read the file before closing it:

    QString cadenaimportar = "SELECT importar_copy('" + tabla + "','/path/conceptos.csv',)";
     ......
    file.close();
    

    And it would be:

    file.close();
     QString cadenaimportar = "SELECT importar_copy('" + tabla + "','/path/conceptos.csv',)";
     QSqlQuery consulta(db);
     if(consulta.prepare(cadenaimportar))
     {
         consulta.exec();
     }
    qDebug()<<consulta.lastError()<<"--"<<consulta.lastQuery();
    

Log in to reply