QSqlQuery::prepare() sqlite will not work with strings

[seasoned_geek]

I haven't ferreted out a stand alone example, but

Code: Select all

    QSqlQuery query( db );
    query.exec( cmd ); // yes, you should check for error

    cmd = "create table THEME_COLORS ("
          "THEME_NAME    TEXT NOT NULL,"
          "STYLE_NO      INT DEFAULT 0,"
          "FONT_NAME     TEXT DEFAULT '',"
          "POINT_SIZE    INT DEFAULT 10,"
          "POINT_FRACTIONS    INT DEFAULT 0,"
          "BOLD                INT DEFAULT 0,"
          "ITALIC        INT DEFAULT 0,"
          "UNDERLINE    INT DEFAULT 0,"
          "FORE_RED    INT    DEFAULT -1,"
          "FORE_BLUE    INT DEFAULT -1,"
          "FORE_GREEN    INT DEFAULT -1,"
          "FORE_ALPHA    INT DEFAULT -1,"
          "BACK_RED    INT DEFAULT -1,"
          "BACK_BLUE    INT DEFAULT -1,"
          "BACK_GREEN    INT DEFAULT -1,"
          "BACK_ALPHA    INT DEFAULT 255,"
          "EOL_FILLED    INT DEFAULT 0,"
          "DISPLAYED_CASE    INT DEFAULT 0,"
          "VISIBLE       INT DEFAULT 1,"
          "CHANGEABLE    INT DEFAULT 1,"
          "PRIMARY KEY (THEME_NAME, STYLE_NO),"
          "FOREIGN KEY(THEME_NAME) REFERENCES THEME_ATTRIBUTES(THEME_NAME)"
          ");";

    query.exec( cmd ); // yes, you should check for error

...

        // note: prepare for SQLite has issues with strings
        //
        cmd = "INSERT INTO THEME_COLORS( THEME_NAME, FONT_NAME, "
              "STYLE_NO, POINT_SIZE, FORE_RED, FORE_BLUE, FORE_GREEN, "
              "FORE_ALPHA, BACK_RED, BACK_BLUE, BACK_GREEN, BACK_ALPHA) "
              "VALUES( :theme_name, :font_name, :style_no, :point_size, "
              ":fore_red, :fore_blue, :fore_green, :fore_alpha, "
              ":back_red, :back_blue, :back_green, :back_alpha);";

        query.prepare( cmd );

        query.bindValue( ":theme_name", theme );
        query.bindValue( ":font_name", QString( "Monospace" ) );
        query.bindValue( ":style_no",   STYLE_DEFAULT );
        query.bindValue( ":point_size", 10 );

        fore = QColor( Qt::white );
        query.bindValue( ":fore_red",   fore.red() );
        query.bindValue( ":fore_blue",  fore.blue() );
        query.bindValue( ":fore_green", fore.green() );
        query.bindValue( ":fore_alpha", fore.alpha() );

        back = QColor( "#09223F" );
        query.bindValue( ":back_red",   back.red() );
        query.bindValue( ":back_blue",  back.blue() );
        query.bindValue( ":back_green", back.green() );
        query.bindValue( ":back_alpha", back.alpha() );

        if ( !query.exec() )
        {
            qDebug() << query.lastError().text();
            qDebug() << query.lastQuery();
        }

doesn't issue any errors but inserts garbage.
Yes, theme is a QString with a string value.

Code: Select all

sqlite> select * from theme_colors;
THEME_NAME|STYLE_NO|FONT_NAME|POINT_SIZE|POINT_FRACTIONS|BOLD|ITALIC|UNDERLINE|FORE_RED|FORE_BLUE|FORE_GREEN|FORE_ALPHA|BACK_RED|BACK_BLUE|BACK_GREEN|BACK_ALPHA|EOL_FILLED|DISPLAYED_CASE|VISIBLE|CHANGEABLE
�/r�|32|�/r�|10|0|0|0|0|255|255|255|255|9|63|34|255|0|0|1|1

If you cheat

Code: Select all

        // note: prepare for SQLite has issues with strings
        //
        cmd = "INSERT INTO THEME_COLORS( THEME_NAME, FONT_NAME, "
              "STYLE_NO, POINT_SIZE, FORE_RED, FORE_BLUE, FORE_GREEN, "
              "FORE_ALPHA, BACK_RED, BACK_BLUE, BACK_GREEN, BACK_ALPHA) "
              "VALUES( '%1', '%2', :style_no, :point_size, "
              ":fore_red, :fore_blue, :fore_green, :fore_alpha, "
              ":back_red, :back_blue, :back_green, :back_alpha);";

        cmd = QStringParser::formatArg( cmd, theme );
        cmd = QStringParser::formatArg( cmd, QString( "Monospace" ) );

        query.prepare( cmd );

        query.bindValue( ":style_no",   STYLE_DEFAULT );
        query.bindValue( ":point_size", 10 );

        fore = QColor( Qt::white );
        query.bindValue( ":fore_red",   fore.red() );
        query.bindValue( ":fore_blue",  fore.blue() );
        query.bindValue( ":fore_green", fore.green() );
        query.bindValue( ":fore_alpha", fore.alpha() );

        back = QColor( "#09223F" );
        query.bindValue( ":back_red",   back.red() );
        query.bindValue( ":back_blue",  back.blue() );
        query.bindValue( ":back_green", back.green() );
        query.bindValue( ":back_alpha", back.alpha() );

        if ( !query.exec() )
        {
            qDebug() << query.lastError().text();
            qDebug() << query.lastQuery();
        }

you can get some good data in the database.

Code: Select all

sqlite> .head on
sqlite> select * from theme_colors;
THEME_NAME|STYLE_NO|FONT_NAME|POINT_SIZE|POINT_FRACTIONS|BOLD|ITALIC|UNDERLINE|FORE_RED|FORE_BLUE|FORE_GREEN|FORE_ALPHA|BACK_RED|BACK_BLUE|BACK_GREEN|BACK_ALPHA|EOL_FILLED|DISPLAYED_CASE|VISIBLE|CHANGEABLE
COBALT|32|Monospace|10|0|0|0|0|255|255|255|255|9|63|34|255|0|0|1|1
COBALT|0|Monospace|10|0|0|0|0|255|255|255|255|9|63|34|255|0|0|1|1
COBALT|33|Monospace|-1|0|0|0|0|136|136|136|255|17|17|17|255|0|0|1|1

We shouldn't have to cheat though. The doc doesn't mention anything special for QString.

https://www.copperspice.com/docs/cs_api/class_qsqlquery.html#aa7e148b992375c1df7c80001c18731f1

[barbara]

Of course "cheating" should never be required and this is not the intent of the SQL driver. Your example is not something we have seen as of yet. We need to ensure this is a small repeatable sample. Most likely this is a utf-16 / utf-8 problem which will be easy to fix once we find it

Thanks for reporting.

Barbara

[seasoned_geek]

While it is being fixed, please be certain strings like this:

'!home!roland!tnas!a.txt.b00000'

can successfully be prepared. One is quite hosed when trying to use something like '%2' and QStringParser::formatArg(). SQLite does support using double quotes, but it violates the SQL "standard."

Properly prepared in the driver it shouldn't need parsing markers.

[barbara]

This looked a bit complicated to resolve but it turned out to be rather simple. We have pushed a correction for this Sqlite problem to the CopperSpice github repo. ( sha C6AC216 )

Barbara