Recent

Author Topic: Error in query (sqldb) view with return in json (mysql8)  (Read 1396 times)

andersonscinfo

  • Full Member
  • ***
  • Posts: 141
Error in query (sqldb) view with return in json (mysql8)
« on: June 21, 2024, 01:34:27 am »
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

  • Hero Member
  • *****
  • Posts: 1334
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #1 on: June 21, 2024, 06:31:59 am »
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.
regards
Andreas

Thaddy

  • Hero Member
  • *****
  • Posts: 15168
  • Censorship about opinions does not belong here.
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #2 on: June 21, 2024, 07:37:46 am »
TJSONDataset?
https://wiki.freepascal.org/fpjsondataset
That works with sqldb.
Of course the national anthem of the U.S.A. was written by Jimi Hendrix, didn't you know that?

af0815

  • Hero Member
  • *****
  • Posts: 1334
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #3 on: June 21, 2024, 10:15:33 am »
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.
regards
Andreas

Thaddy

  • Hero Member
  • *****
  • Posts: 15168
  • Censorship about opinions does not belong here.
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #4 on: June 21, 2024, 11:11:27 am »
OP does not state manipulating the data, just displaying it. For that it can be just the JSON dataset.
Of course the national anthem of the U.S.A. was written by Jimi Hendrix, didn't you know that?

Zvoni

  • Hero Member
  • *****
  • Posts: 2472
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #5 on: June 21, 2024, 12:30:49 pm »
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?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

andersonscinfo

  • Full Member
  • ***
  • Posts: 141
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #6 on: June 21, 2024, 07:14:40 pm »
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

  • Hero Member
  • *****
  • Posts: 1334
Re: Error in query (sqldb) view with return in json (mysql8)
« Reply #7 on: June 22, 2024, 09:16:06 am »
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.
regards
Andreas

 

TinyPortal © 2005-2018