PostgreSQL: uuid parameter
lazarus / 16-Databases

----------------------------------------------------------------------
SymbolicFrank · 2023-07-20 09:35

There is no variant type GUID AFAIK, but if I pass it like:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
q.ParamByName(n.Name).Value := GUIDToString(n.GUID)
then I get this error message:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i PostgreSQL:
ERROR: column <name> is of type uuid but expression is of type text
How can I pass it?

----------------------------------------------------------------------
Zvoni · 2023-07-20 09:52

TGuid? https://www.freepascal.org/docs-html/rtl/system/tguid.html
otherwise
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
q.ParamByName(n.Name).AsString:= GUIDToString(n.GUID)

----------------------------------------------------------------------
SymbolicFrank · 2023-07-20 10:03

Yes, "n.GUID" is a TGUID. "AsString" makes no difference. The
parameter has to be of type 'uuid", but that doesn't fit inside a
Variant. Or rather, it does, but as a string instead of a GUID,
because that is not a valid variant type.

This doesn't work either:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
q.ParamByName(n.Name).Value := System.Copy(GUIDToString(n.GUID), 2,
36)

----------------------------------------------------------------------
Zvoni · 2023-07-20 10:55

Quote from: SymbolicFrank on July 20, 2023, 12:03:44 pmYes, "n.GUID"
is a TGUID. "AsString" makes no difference. The parameter has to be of
type 'uuid", but that doesn't fit inside a Variant. Or rather, it
does, but as a string instead of a GUID, because that is not a valid
variant type.

This doesn't work either:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
q.ParamByName(n.Name).Value := System.Copy(GUIDToString(n.GUID), 2,
36)Have you tried converting it to an Array of Bytes and then
"AsBytes"?

----------------------------------------------------------------------
Zvoni · 2023-07-20 11:04

Wait a Sec: GUIDToString
https://www.freepascal.org/docs-html/rtl/sysutils/guidtostring.html
QuoteGUIDToString converts the GUID identifier in GUID to a string
representation in the form

{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}
incl. "{" "}"
Is it possible, that PostGres expects a GUID without {}?

OTOH: https://www.postgresql.org/docs/current/datatype-uuid.html
PG should accepts either format

EDIT: Found something: https://www.freepascal.org/docs-
html/ref/refsu18.html
Since "Value" of a TParam is a Variant
QuoteStructured types such as sets, records, arrays, files, objects
and classes are not assignment-compatible with a variant, as well as
pointers. Since TGuid is a record.....

EDIT2: hmmmmm...untested of course
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM SomeTable WHERE CAST(SomeUUIDFIeld AS CHAR(16))=:uuidParamCode:
Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
q.ParamByName(n.Name).AsString:= GUIDToString(n.GUID)

----------------------------------------------------------------------
korba812 · 2023-07-20 11:33

You can also try the uuid() function in the query:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM SomeTable WHERE SomeUUIDFIeld=uuid(:uuidParam) We need to
implement the TParam.AsGuid property and add support for such a
parameter in SQLDB.
https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TParam.AsG
uid

----------------------------------------------------------------------
SymbolicFrank · 2023-07-20 11:34

Yes, in the second example I remove the { and }.

I tried this:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE blah (id uuid, VALUE text);INSERT INTO blah (id, VALUE) VALUES
('ED394F38-8AD2-44FE-BF08-E33C19C6A3EE', 'ED394F38-8AD2-44FE-
BF08-E33C19C6A3EE');INSERT INTO blah (CAST(id AS text) AS idx, VALUE)
VALUES ('ED394F38-8AD2-44FE-BF08-E33C19C6A3E2', 'ED394F38-8AD2-44FE-
BF08-E33C19C6A3E2');
But that gives a (not unexpected) syntax error on the third line.

@korba812: I'll try that.

----------------------------------------------------------------------
SymbolicFrank · 2023-07-20 12:06

Quote from: korba812 on July 20, 2023, 01:33:50 pmYou can also try the
uuid() function in the query:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM SomeTable WHERE SomeUUIDFIeld=uuid(:uuidParam) We need to
implement the TParam.AsGuid property and add support for such a
parameter in SQLDB.
https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TParam.AsG
uid

PostgreSQL complains that the function "uuid(unknown)" doesn't exists.
This works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
cast(:uuidParam as uuid)
I do agree that support for "TParam.AsGuid " would be great.

----------------------------------------------------------------------
Zvoni · 2023-07-20 13:12

Quote from: SymbolicFrank on July 20, 2023, 02:06:47 pmQuote from:
korba812 on July 20, 2023, 01:33:50 pmYou can also try the uuid()
function in the query:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM SomeTable WHERE SomeUUIDFIeld=uuid(:uuidParam) We need to
implement the TParam.AsGuid property and add support for such a
parameter in SQLDB.
https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TParam.AsG
uid

PostgreSQL complains that the function "uuid(unknown)" doesn't exists.
This works:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
cast(:uuidParam as uuid)
I do agree that support for "TParam.AsGuid " would be great.
I'd be surprised if that works.
By using Parameters, the field "SomeUUIDField" you want to use the
Parameter for, returns ftGUID as DataType.
That's why you had that error message.
...or was that a Message from the PG-Server?
I'd rather go with my try castig the Field to CHAR/VARCHAR) and then
assign AsString

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
blah (CAST(id AS text) AS idx, VALUE) VALUES ('ED394F38-8AD2-44FE-
BF08-E33C19C6A3E2', 'ED394F38-8AD2-44FE-BF08-E33C19C6A3E2');Of course
that one fails
there is no Column "idx"
and i would be surprised if that is even legal INSERT INTO Syntax in
any DBMS