QSqlQuery is not able to update geolocation LineString in DB

MrEinsA

Hi Guys,

i have a question related to geolocation in QSqlQuery:
Maybe you can help me ... i cannot find any topic related to this issue.

The SQL Error:

"UPDATE file SET geographical_location=? WHERE file_id=?;"
QSqlError("1416", "QMYSQL3: Unable to execute statement", "Cannot get geometry object from data you send to the GEOMETRY field")

What i did:

I have code like this (pseudo code):

QString strQ("UPDATE file SET geographical_location=:geo WHERE file_id=:fi;");
QSqlQuery q;
q.prepare(strQ);
QString geoLoc("ST_LineStringFromText('LineString(23.5502 4.71853,47.5502 22,2221)')");
q.bindValue(":fi", 1);
q.bindValue(":geo", geoLoc);
q.exec();

The error above is reported.

I cannot get why its not working ... in the mysql workbench it is running with this query.

Any ideas?

Thank guys!!!!

[edit: koahnig] code tags added

MrEinsA

Thanks guys ... finally i made it by myself.

The query was simply wrong ... it should look like this:

QString strQ("UPDATE file SET geographical_location=ST_LineStringFromText(:geo) WHERE file_id=:fi;");
QSqlQuery q;
q.prepare(strQ);
QString geoLoc("LineString(23.5502 4.71853,47.5502 22,2221"));
q.bindValue(":fi", 1);
q.bindValue(":geo", geoLoc);
q.exec();

The function ("ST_LineStringFromText") has to be called inside the query.

SGaist

Hi,

Did you try to print the created query ?

Does it match what you are using in workbench ?

By the way, which version of Qt are you using ? MySQL ? On what platform ?

MrEinsA

@SGaist

Thanks for your quick response.

Like above this is the output if i print the quer with: qDebug() << q.executedQuery();
"UPDATE file SET geographical_location=? WHERE file_id=?;"

I use mysql 5.7 and also Qt 5.7. Windows.
If i execute exactly the same query in the mysql workbench it is working.

SGaist

That's surprising, the executed query seems to miss the bonded parameters. Did you try using positional bindings ?

MrEinsA

@SGaist
Good morning, jep i did it and the error is exactly the same.

Very strange.

I add the geolocation as string could it be, that the simple quotes will be added and then the db reject it, because it want to have a geo (blob) object?

MrEinsA

Thanks guys ... finally i made it by myself.

The query was simply wrong ... it should look like this:

QString strQ("UPDATE file SET geographical_location=ST_LineStringFromText(:geo) WHERE file_id=:fi;");
QSqlQuery q;
q.prepare(strQ);
QString geoLoc("LineString(23.5502 4.71853,47.5502 22,2221"));
q.bindValue(":fi", 1);
q.bindValue(":geo", geoLoc);
q.exec();

The function ("ST_LineStringFromText") has to be called inside the query.