Qt 5.14.1 QSqlModel and French Characters.

ad5xj

I have created a QSqlQuery on an SQLite database. This query is used as a QSqlQueryModel and is the model for a QTableView. The data is found and displayed as expected, except that certain foreign names and words display certain characters with a special character that looks like a black diamond with a question mark in it.

I am using the QTranslator for the application. But that does not seem to help.

There also seems to be no way to use the toLocal8bit() function in a free-style or bound query or QSqlQueryModel. The QTableView only takes the model as-is and does not allow individual cells to format differently unless you inject massive overhead of reading each record and formatting each cell.

So then, how do I take the data in the database found with a QSqlQuery/QSqlQueryModel and make that the model for a QTableView with the right display?

Or am I missing something somewhere?

Christian Ehrlicher

How did you insert the data into the sqlite database? Which encoding did you use?

ad5xj

@Christian-Ehrlicher The data was inserted with the supplied QtSQL libraries using conventional SQLite SQL Statements from a QString. This should ensure UTF-8 string data going in, I think.

There are no other functions to change the defaults of UTF-8 (system default) when the database is created for SQLite nor QSqlDatabase and QSqlDriver for SQLITE3.

Christian Ehrlicher

And how did you insert the data? Where came it from? Since QSqlQueryModel does all right I would guess the insertion was wrong - please show us some code.

ad5xj

@Christian-Ehrlicher OK here is a code snippet from the database handler that does data insertions.

bool SQLiteProxy::addNewQSO(QString f)
{
    // QString f is a string with field data separated by '|' //
   //  Data values are inserted with bound values. This was one attempt to  //
  //   normalize the data.  //
    bool sqlOK = false;

    QString in_flds = f;
    QString strDML = "";
    QStringList flds = QStringList();
    QSqlQuery qry(sqd->db);

    flds.clear();
    flds = in_flds.split("|",QString::KeepEmptyParts);
    if ( flds[4].trimmed().length() <=0 ) return false;
    if ( flds.size() == 45 )  flds.append("1");

    strDML  = "INSERT INTO log ";
    strDML += "(";
    strDML += "id,lognumber,qso_date,time_on,time_off,";
    strDML += "call,contacted_op,freq,bandid,";
    strDML += "modeid,tx_pwr,pfx,qth,";
    strDML += "state,gridsquare,cnty,cont,";
    strDML += "lat,lon,dxcc,qsl_sent,";
    strDML += "qsl_rcvd,qslsdate,qslrdate,country,";
    strDML += "rst_sent, rst_rcvd,qsl_sent_via, qsl_rcvd_via,";
    strDML += "eqsl_qsl_rcvd,eqsl_qsl_sent,eqsl_qslrdate, eqsl_qslsdate,";
    strDML += "iota,ituz,iaruz,cqz,";
    strDML += "ten_ten,region,srx,stx,";
    strDML += "qslmsg,bearing,distance,notes,";
    strDML += "comment) ";
    strDML += "VALUES (";
    strDML += ":id,:logno,:qsodate,:timeon,:timeoff,";
    strDML += ":callsign,:opname,:frequency,:bandval,";
    strDML += ":modeval,:power,:prefix,:city,";
    strDML += ":st,:grid,:county,:continent,";
    strDML += ":latval,:lonval,:dxccval,:qslsent,";
    strDML += ":qslrcvd,:qslsdate,:qslrdate,:ctry,";
    strDML += ":rstsent,:rstrcvd,:sent_via,:rcvd_via,";
    strDML += ":eqslrcvd,:eqslsent,:eqslrdate,:eqslsdate,";
    strDML += ":iotano,:itu,:iaru,:cq,";
    strDML += ":tenten,:arrlregion,:srxval,:stxval,";
    strDML += ":qslmsgstr,:bearingval,:distanceval,:notesstr,:cmtstr)";
    qry.prepare(strDML);

    qry.bindValue(":qsodate", flds[1].trimmed()); /
    qry.bindValue(":timeon",flds[2].trimmed());   
    qry.bindValue(":timeoff",flds[3].trimmed());  
    qry.bindValue(":callsign",flds[4].trimmed()); 
    qry.bindValue(":opname",flds[5].trimmed());
    qry.bindValue(":frequency",flds[6].trimmed()); 
    qry.bindValue(":bandval",flds[7].trimmed().toInt());
    qry.bindValue(":modeval",flds[8].trimmed().toInt());
    qry.bindValue(":power",flds[9].trimmed().toInt());
    qry.bindValue(":prefix",flds[10].trimmed());  
    qry.bindValue(":city",flds[11].trimmed());    
    qry.bindValue(":st",flds[12].trimmed());      
    qry.bindValue(":grid",flds[13].trimmed());  
    qry.bindValue(":county",flds[14].trimmed()); 
    qry.bindValue(":continent",flds[15].trimmed());
    qry.bindValue(":latval",flds[16].trimmed()); 
    qry.bindValue(":lonval",flds[17].trimmed());
    qry.bindValue(":dxccval",flds[18].trimmed().toInt());
    qry.bindValue(":qslsent",flds[19].trimmed()); 
    qry.bindValue(":qslrcvd",flds[20].trimmed()); 
    qry.bindValue(":qslsdate",flds[21].trimmed());
    qry.bindValue(":qslrdate",flds[22].trimmed());
    qry.bindValue(":ctry",flds[23].trimmed());
    qry.bindValue(":rstsent",flds[24].trimmed());
    qry.bindValue(":rstrcvd",flds[25].trimmed());
    qry.bindValue(":sent_via",flds[26].trimmed());
    qry.bindValue(":rcvd_via",flds[27].trimmed());/
    qry.bindValue(":eqslrcvd",flds[28].trimmed());
    qry.bindValue(":eqslsent",flds[29].trimmed());
    qry.bindValue(":eqslrdate",flds[30].trimmed());
    qry.bindValue(":eqslsdate",flds[31].trimmed());
    qry.bindValue(":iotano",flds[32].trimmed());
    qry.bindValue(":itu",flds[33].trimmed().toInt());
    qry.bindValue(":iaru",flds[34].trimmed());
    qry.bindValue(":cq",flds[35].trimmed().toInt())
    qry.bindValue(":tenten",flds[36].trimmed().toInt());
    qry.bindValue(":arrlregion",flds[37].trimmed());
    qry.bindValue(":srxval",flds[38].trimmed());
    qry.bindValue(":stxval",flds[39].trimmed());
    qry.bindValue(":qslmsgstr",flds[40].trimmed());
    qry.bindValue(":bearingval",flds[41].trimmed().toInt()); 
    qry.bindValue(":distanceval",flds[42].trimmed().toInt());
    qry.bindValue(":notesstr",flds[43].trimmed());
    qry.bindValue(":cmtstr",flds[44].trimmed());
    if ( flds[45].toInt() <= 0)
        qry.bindValue(":logno",1);
    else
        qry.bindValue(":logno",flds[45].toInt());
    qry.bindValue(":idno",flds[0].trimmed().toInt());
    sqlOK = qry.exec();

    if ( sqlOK )
    {
        errNo = QSqlError::NoError;
        errStr = "";
    }
    else
    {
        errNo = qry.lastError().type();
        errStr = qry.lastError().text();
        qCritical() << "DBERROR ON ADD - " <<  Q_FUNC_INFO << ":" << __LINE__
                   << "\nErr: " << errNo
                   << "\nMsg: " << errStr
                   << "\nSQL:" << strDML
                   << "\nData:" << flds;
        strDML = "ROLLBACK";  // to protect DB
        qry.exec(strDML);
        return false;
    }
    return true;
}

Christian Ehrlicher

And you're sure that QString f is correct? Where do you read it from?

ad5xj

@Christian-Ehrlicher I must be missing something.

To get to this function one must create a QString with the field values separated by a '|'.

I think I stated that. If not I do apologize.

But it does seem logical, given the code , that the data is unpacked from the supplied string. It has to be a QString segment to be concatenated to the string supplied to this function.

Besides, the origin should not matter. Even if it came from some other source, the use of the QTranslator, QString, and UTF-8 character set at the system-level should not have this problem.

The letters with diacritical marks are in the UTF-8 character set. Not all fonts display them correctly, but I have tried quite a few different fonts and none display the French or Scandinavian characters correctly.

Christian Ehrlicher

@ad5xj said in Qt 5.14.1 QSqlModel and French Characters.:

But it does seem logical, given the code , that the data is unpacked from the supplied string
Besides, the origin should not matter.

It does matter where this string comes from - do you read it from a file or from a e.g. QLineEdit? If the encoding there is wrong then it will be put wrong in the database.

ad5xj

@Christian-Ehrlicher I am obviously not communicating well.

Regardless whether the data comes from a file or a QLineEntry it is assigned to a QString in order to be a segment of the QString f. In this case the offending data is part of data loaded from an external source in text format.

Are you saying that assignment will not change the data to UTF-8?

Christian Ehrlicher

@ad5xj said in Qt 5.14.1 QSqlModel and French Characters.:

Are you saying that assignment will not change the data to UTF-8?

If you convert it wrong then QString will be wrong, yes.

This is working fine for me:

int main(int argc, char **argv)
{
  QApplication app(argc, argv);

  QSqlDatabase db(QSqlDatabase::addDatabase("QSQLITE"));
  db.setDatabaseName(":memory:");
  db.open();
  QSqlQuery q;
  q.exec("CREATE TABLE tmp (name varchar);");
  q.exec("INSERT INTO tmp (name) VALUES ('Ren\u00e9');");
  q.exec("SELECT * FROM tmp;");
  q.next();
  QString str = q.value(0).toString();
  QLabel lbl(str);
  lbl.show();

  return app.exec();
}

ad5xj

@Christian-Ehrlicher Not sure how to answer your last reply.

The data is read into a QByteArray from a text file with a QIODeice as a QFile object opened as text, using the readAll() method. The resulting QByteArray is appended to a QString. That allows the data to be split into lines on the "\n" of each record.

Since the incoming data is delimited text, each line of text is parsed for each data field. The data in each field becomes the correct data type as detected by the parser.

So unless there is something being overlooked at the time the data is read from the input, I am not sure what else to tell you.

Christian Ehrlicher

@ad5xj said in Qt 5.14.1 QSqlModel and French Characters.:

The resulting QByteArray is appended to a QString.

Again: how and what's the encoding of the file? Please show us the code!

ad5xj

@Christian-Ehrlicher OK, but I don't think you will learn any more than I have already posted.

void ReadFile(QString fil)
   QByteArray allRecs;
   fil = "textfile.txt"
   QStringLIst fields;
    // get the file to parse
    QFile infile(fil);
    if ( !infile.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        QMessageBox mb;
        mb.setWindowTitle(QApplication::tr("Text Import Failure"));
        msg = __FILE__;
        msg += __FUNCTION__;
        msg += QString("%1 ").arg(__LINE__);
        msg += QApplication::tr("Cannot read file ");
        msg += QString("%1:").arg(fil);
        msg += QString("\n%2 ").arg(infile.errorString());
        mb.setText(msg);
        mb.exec();
        return;
    }

    data = "";
    lines.clear();         // init the lines var
    strLineSeg = "";
    // First read all the text file records into memory
    allRecs = infile.readAll();            // read everything into buffer file buffer
    infile.close();                        // close the input file
    data.append(allRecs);                       // change from required QByteArray to QString
    lines = data.split("\n",QString::KeepEmptyParts); // split file records into lines on line break
    fields.clear();
    for (int  p = pos; p < y; ++p )
    {
        linestr = lines[p].trimmed().toLocal8Bit();
        if ( linestr.length() > 0 )
        { // not a blank line
            // make a list of all fields in the line
            fields = linestr.split("<",QString::KeepEmptyParts);
            fields << linestr.trimmed().split("<",QString::SkipEmptyParts);
           cnt = fields.count();
            parse(fields);
        }
        fields.clear();
    }

void parse(QStringList flds)
{
    If (fldname == "Name")
    {
        QString datastr = flds[1];
        QString  name = datastr.trimmed();
        // All fields read now make the new record from the data
        QString strRec = "";
       strRec = name + "|";
. . .
        addNewQSO(strRec);
}

The rest of the database save code you have already.
BTW the database was created with the SQLite PRAGMA encoding = 'UTF-8';

ad5xj

@ad5xj UPDATE:
Some progress...I have been able to use various text editors to determine the incoming data is text encoded as Windows-1258. Using the QTextCodec to convert the QByteArray to QString as UTF-8 it fixed the problem.

This is only a short-term solution since I will not always know what the encoding is. So now, how do I detect the character encoding from the raw text file in order to use the right conversion codec?

A search of this forum is inconclusive.

jsulm

@ad5xj There is no complete solution for detecting the encoding. For example UTF-8 and Latin1 are identical for the first 127 characters. See https://forum.qt.io/topic/12319/how-to-automatically-detect-the-codec-text-file

ad5xj

With some research, I have come upon a command-line utility called UTRAC which does at least a partial job of text encoding detection. It is not a complete list and is not 100% effective but does beg the question; why QFile and QTextStream have not at least attempted this. If the UTRAC guys have come this far, it is at least feasible. If it requires a feature request for QT 5.15 then I will put one in.

jsulm

@ad5xj said in Qt 5.14.1 QSqlModel and French Characters.:

why QFile and QTextStream have not at least attempted this

Why should they (especially QFile)? QFile is simply a file handle with some operation for basic file operations like reading/writing. Most of the time you do not have to guess as you know what you're writing and reading. And also, as you already discovered, there is no 100% solution for this and doing this checking is time consuming (you need to read the whole file and check each character), so it is an expensive operation.