Recent

« previous next »
Pages: [1]

Author Topic: Error in query (sqldb) view with return in json (mysql8)  (Read 1653 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)
Logged

af0815

  • Hero Member
  • *****
  • Posts: 1364
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.
Logged
regards
Andreas

Thaddy

  • Hero Member
  • *****
  • Posts: 15638
  • 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.
Logged
If I smell bad code it usually is bad code and that includes my own code.

af0815

  • Hero Member
  • *****
  • Posts: 1364
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.
Logged
regards
Andreas

Thaddy

  • Hero Member
  • *****
  • Posts: 15638
  • 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.
Logged
If I smell bad code it usually is bad code and that includes my own code.

Zvoni

  • Hero Member
  • *****
  • Posts: 2690
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?
Logged
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.
Logged

af0815

  • Hero Member
  • *****
  • Posts: 1364
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.
Logged
regards
Andreas
Pages: [1]
« previous next »
 

TinyPortal © 2005-2018