how to insert a QJsonobject into a mysql field

cawlfj

I have a mysql table contains json field , and i define JSonobject and JSsonArray
QString jjj="sdfd";

        QJsonObject JSSS;

        JSSS.insert("TypeID","IMO");

        JSSS.insert("Number","9992873");

        qDebug()<<JSSS;

        queryCdbN.prepare(" insert into ttt (test,imo) values(:test,:imo)");

        queryCdbN.bindValue(":test",jjj);

        queryCdbN.bindValue(":imo",JSSS);

        queryCdbN.exec();



        queryCdbN.prepare(" insert into ttt (test,imo) values(:test,JSON_OBJECT(:imo)");

        queryCdbN.bindValue(":test",jjj);

        queryCdbN.bindValue(":imo",JSSS);

        queryCdbN.exec();

i get the output as below:
QJsonObject({"Number":"9992873","TypeID":"IMO"})

QSqlError("1064", "QMYSQL: Unable to execute query", "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")

seems the QJsonObject is empty?

how to bindValue and insert into mysql json field?

eyllanesc

@cawlfj try with: queryCdbN.bindValue(":imo", QJsonDocument(JSSS).toJson());

cawlfj

@eyllanesc said in how to insert a QJsonobject into a mysql field:

QJsonDocument(JSSS).toJson()

@eyllanesc change to queryCdbN.bindValue(":imo", QJsonDocument(JSSS).toJson());
i got
QSqlError("3144", "QMYSQL3: Unable to execute statement", "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")

cawlfj

i updated mysql connector to 8.023

QString jjj="sdfd";
QJsonObject JSSS;
JSSS.insert("TypeID","IMO");
JSSS.insert("Number","9992873");
qDebug()<<JSSS;
queryCdbN.prepare(" insert into ttt (test,imo) values(:test,:imo)");
queryCdbN.bindValue(":test",jjj);
queryCdbN.bindValue(":imo",JSSS);
queryCdbN.exec();
qDebug()<<queryCdbN.lastQuery();
qDebug()<<queryCdbN.lastError();

will get

QJsonObject({"Number":"9992873","TypeID":"IMO"})
" insert into ttt (test,imo) values(:test,:imo)"
QSqlError("3140", "QMYSQL3: Unable to execute statement", "Invalid JSON text: "The document is empty." at position 0 in value for column 'ttt.imo'.")

Christian Ehrlicher

What column type is imo?

cawlfj

@Christian-Ehrlicher Json

cawlfj

@eyllanesc

queryCdbN.prepare(" insert into ttt (test,imo) values(:test,CONVERT(:imo USING UTF8MB4) )");

Now it is ok

Many thanks

Christian Ehrlicher

@cawlfj said in how to insert a QJsonobject into a mysql field:

Json

So it's basically a string - QString::fromUtf8(QJsonDocument(JSSS).toJson())