Error in query (sqldb) view with return in json (mysql8)
lazarus / 16-Databases

----------------------------------------------------------------------
andersonscinfo · 2024-06-20 23:34

Hello everyone, I have a somewhat strange situation, I have a mysql8
database (which is on a hosting) I have several tables, and they all
work very well, I can connect, change, remove, etc., today I needed to
create a view in the database and this view returns a json, for some
reason sqldb does not allow searching this view, returning the
following error attached

Note: sorry for my English (google translate)

----------------------------------------------------------------------
af0815 · 2024-06-21 04:31

SQLdb need a "normal" formed dataset returned from server. Jason or
XML can only returned if the are packed in a normal column as string
or text or blob.

----------------------------------------------------------------------
Thaddy · 2024-06-21 05:37

TJSONDataset?
https://wiki.freepascal.org/fpjsondataset
That works with sqldb.

----------------------------------------------------------------------
af0815 · 2024-06-21 08:15

Thaddy, the query on the server produce this JSON instead of a normal
resultset. The JSON dataset can IMHO not send this query to the server
and work with the result. Your sample use local JSON.

----------------------------------------------------------------------
Thaddy · 2024-06-21 09:11

OP does not state manipulating the data, just displaying it. For that
it can be just the JSON dataset.

----------------------------------------------------------------------
Zvoni · 2024-06-21 10:30

If that View really returns a JSON, then it's per se a simple
String/Text first.
The Field should have ftMemo/ftString as Datatype in SQLdb
(TSQLQuery?). Just assign it to a TJSONXXX, and go from there.

OTOH, why not use the native MySQL-JSON-Functionalities?

----------------------------------------------------------------------
andersonscinfo · 2024-06-21 17:14

I don't know if I managed to explain it correctly, I'll try with
images, I have this view in MySQL that is attached, and basically I
need to do a select, the return can be in string.

Att.

----------------------------------------------------------------------
af0815 · 2024-06-22 07:16

This what I meant, this produce a incompatible resultset.

You must try to rewrite the query to return the JSON in a field. This
can handled by SqlDB. You have then a normal Text, Sting or Blobfield
and this can be handled as Thaddy's link to the sample show.