UUID as primary key in lookup table (PostgreSQL)

PHPMaker General Discussion (PHPMaker)
1

I use uuid as the primary key for multiple tables. I’m attempting to configure these tables so that they can be accessed from other pages via the lookup functionality.Database Postgresql 9.6
PHPMaker 2023.8
PHP 8.1As an example I’ll use this two tables:


CREATE TABLE xxx.apropietario (
	apro_id uuid NOT NULL DEFAULT uuid_generate_v4(),
	apro_nombre varchar(45) NOT NULL,
	apro_mail varchar(45) NOT NULL,
	apro_telefono varchar(45) NOT NULL,
	CONSTRAINT pk_apropietario PRIMARY KEY (apro_id)
);

CREATE TABLE xxx.aauto (
	aaut_id uuid NOT NULL DEFAULT uuid_generate_v4(),
	apro_id uuid NOT NULL,
	CONSTRAINT pk_aauto PRIMARY KEY (aaut_id),
	CONSTRAINT fk_apropietario FOREIGN KEY (apro_id) REFERENCES rcq.apropietario(apro_id) MATCH FULL,
);

Next, I configured aauto table to use apropietario as Lookup Table with apro_id as the link field.When the AautoAdd page is rendered, the API/lookup function is called with the following arguments (credentials have been suppressed):

curl --request POST \
  --url http://192.168.10.9/api/lookup \
  --header 'Accept: application/json, text/javascript, */*; q=0.01' \
  --header 'Accept-Language: en-US,en;q=0.9,es;q=0.8' \
  --header 'Connection: keep-alive' \
  --header 'Content-Type: application/json' \
  --data '[{"page":"AautoAdd","field":"apro_id","ajax":"updateoption","language":"es","name":"x_apro_id","v0":147664}]'

It gives this error:

{
	"statusCode": 200,
	"error": {
		"class": "text-danger",
		"type": "Error",
		"description": "...\\vendor\\doctrine\\dbal\\src\\Driver\\API\\PostgreSQL\\ExceptionConverter.php(87): An exception occurred while executing a query: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type uuid: \"147664\"\nLINE 1: ...AS \"df4\" FROM xxx.apropietario WHERE \"apro_id\" IN ('147664')\n                                                              ^"
	}
}

As shown, SQL expects a UUID-like value, and the random value (147664) causes the query to fail. If I force this value to be UUID, everything works perfectly.Do you know a solution to this problem?

2

You need to post the Edit Tag and Lookup Table Settings for discussion/testing.

3

Attached you could find the aauto table setup


<Table TblSchema="rcq" TblName="aauto" TblType="TABLE" TblGen="1" TblGenList="controller=1,info=1,master=1,list=1,list|table=1,list|cards=1,list|class=1,add=1,add|class=1,view=1,view|class=1,edit=1,edit|class=1" TblList="1" TblView="1" TblEdit="1" TblAdd="1" TblIsMaster="1" TblCaption="aauto" TblUpdatable="1" TblSrchType="BASIC" TblBasicSearch="1" TblExtendedBasicSearch="1" TblSecurity="0,&quot;Default&quot;,0;1,&quot;Flota&quot;,108;2,&quot;Gerencia&quot;,109;3,&quot;Gestoria&quot;,1;4,&quot;Marketing&quot;,0;5,&quot;Mostrador&quot;,104;6,&quot;Reservas&quot;,0;7,&quot;Taller&quot;,109" TblLoaded="1" TblShowEmptyRows="0" TblUseGlobal="1" TblRecPerPage="20" TblRecPerPageList="10,20,50,ALL" TblGrpPerPage="3" TblGrpPerPageList="1,2,3,5,ALL" TblPagerStyle="2" TblAuditTrail="1" TblDetailShowCount="1" TblDetailAdd="1" TblDetailEdit="1" TblDetailView="1" TblUseButtonsForLinks="1" TblUseDropDownForAction="1" TblUseDropDownForListOptions="1" TblUseDropDownForExport="1">
      <Field FldName="aaut_id" FldType="72" FldTypeName="uuid" NativeDataType="2950" FldSupport="1" FldSort="0" FldSize="16" FldUniqueIdx="1" FldReq="1" FldIsPrimaryKey="1" FldAttribute="4" FldValidate="GUID" FldRequired="1" FldGenerate="1" FldAdd="1" FldAddOpt="1" FldSrchOpr="=" FldDefault="guidv4()" FldDbDefault="&quot;public.uuid_generate_v4()&quot;" FldCaption="aaut id" FldViewTag="FORMATTED TEXT" FldHtmlTag="HIDDEN" FldTagSize="38" FldTagMaxLength="38" FldSelectType="Values" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldQuoteS="'" FldQuoteE="'" FldColumnWrap="0" FldResizeQuality="75" />
      <Field FldName="apro_id" FldType="72" FldTypeName="uuid" NativeDataType="2950" FldSupport="1" FldSize="16" FldReq="1" FldAttribute="4" FldValidate="GUID" FldRequired="1" FldGenerate="1" FldList="1" FldExport="1" FldView="1" FldEdit="1" FldAdd="1" FldAddOpt="1" FldSearch="1" FldBasicSearch="1" FldSrchOpr="=" FldSrchOpr2="=" FldDefault="&quot;&quot;" FldCaption="apro id" FldViewTag="FORMATTED TEXT" FldHtmlTag="TEXT" FldTagSize="38" FldTagMaxLength="38" FldSelectType="Table" FldTagLnkTbl="apropietario" FldTagLnkFld="apro_id" FldTagLnkDisplay="apro_nombre" FldTagLnkDisp2="apro_mail" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldSelectAllowAdd="1" FldQuoteS="'" FldQuoteE="'" FldMultiUpdate="1" FldResizeQuality="75" />
</Table>

I’m also attaching apropietario table setup

<Table TblSchema="rcq" TblName="apropietario" TblType="TABLE" TblGen="1" TblGenList="controller=1,info=1,list=1,list|table=1,list|cards=1,list|class=1,add=1,add|class=1,addopt=1,addopt|class=1,view=1,view|class=1,edit=1,edit|class=1" TblList="1" TblView="1" TblEdit="1" TblAdd="1" TblAddOpt="1" TblCaption="apropietario" TblUpdatable="1" TblSrchType="BASIC" TblBasicSearch="1" TblExtendedBasicSearch="1" TblSecurity="0,&quot;Default&quot;,0;1,&quot;Flota&quot;,0;2,&quot;Gerencia&quot;,109;3,&quot;Gestoria&quot;,105;4,&quot;Marketing&quot;,0;5,&quot;Mostrador&quot;,0;6,&quot;Reservas&quot;,0;7,&quot;Taller&quot;,0" TblLoaded="1" TblShowEmptyRows="0" TblUseGlobal="1" TblRecPerPage="20" TblRecPerPageList="10,20,50,ALL" TblGrpPerPage="3" TblGrpPerPageList="1,2,3,5,ALL" TblPagerStyle="2" TblAuditTrail="1" TblUseButtonsForLinks="1" TblUseDropDownForAction="1" TblUseDropDownForListOptions="1" TblUseDropDownForExport="1">
      <Field FldName="apro_id" FldType="72" FldTypeName="uuid" NativeDataType="2950" FldSupport="1" FldSort="0" FldSize="16" FldUniqueIdx="1" FldReq="1" FldIsPrimaryKey="1" FldAttribute="4" FldValidate="GUID" FldRequired="1" FldGenerate="1" FldAdd="1" FldAddOpt="1" FldSrchOpr="=" FldDefault="guidv4()" FldDbDefault="&quot;public.uuid_generate_v4()&quot;" FldCaption="apro id" FldViewTag="FORMATTED TEXT" FldHtmlTag="HIDDEN" FldTagSize="38" FldTagMaxLength="38" FldSelectType="Values" FldTagLnkTbl="apropietario" FldTagLnkFld="apro_id" FldTagLnkDisplay="apro_nombre" FldTagLnkDisp2="mraz_id" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldSelectAllowAdd="1" FldQuoteS="'" FldQuoteE="'" FldColumnWrap="0" FldResizeQuality="75" />
      <Field FldName="apro_nombre" FldType="200" FldTypeName="varchar" NativeDataType="1043" FldSupport="1" FldSize="45" FldReq="1" FldAttribute="4" FldRequired="1" FldGenerate="1" FldList="1" FldExport="1" FldView="1" FldEdit="1" FldAdd="1" FldAddOpt="1" FldSearch="1" FldBasicSearch="1" FldSrchOpr="LIKE" FldCaption="apro nombre" FldViewTag="FORMATTED TEXT" FldHtmlTag="TEXT" FldTagSize="30" FldTagMaxLength="45" FldSelectType="Values" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldQuoteS="'" FldQuoteE="'" FldMultiUpdate="1" FldResizeQuality="75" />
      <Field FldName="apro_mail" FldType="200" FldTypeName="varchar" NativeDataType="1043" FldSupport="1" FldSize="45" FldReq="1" FldAttribute="4" FldValidate="EMAIL" FldRequired="1" FldGenerate="1" FldList="1" FldExport="1" FldView="1" FldEdit="1" FldAdd="1" FldAddOpt="1" FldSearch="1" FldBasicSearch="1" FldSrchOpr="LIKE" FldCaption="apro mail" FldViewTag="FORMATTED TEXT" FldHtmlTag="TEXT" FldTagSize="30" FldTagMaxLength="45" FldSelectType="Values" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldQuoteS="'" FldQuoteE="'" FldMultiUpdate="1" FldResizeQuality="75" />
      <Field FldName="apro_telefono" FldType="200" FldTypeName="varchar" NativeDataType="1043" FldSupport="1" FldSize="45" FldReq="1" FldAttribute="4" FldRequired="1" FldGenerate="1" FldList="1" FldExport="1" FldView="1" FldEdit="1" FldAdd="1" FldAddOpt="1" FldSearch="1" FldBasicSearch="1" FldSrchOpr="LIKE" FldCaption="apro telefono" FldViewTag="FORMATTED TEXT" FldHtmlTag="TEXT" FldTagSize="30" FldTagMaxLength="45" FldSelectType="Values" FldTagAPrefix="None" FldFmtType="None" FldIncLeadDigit="-2" FldUseParen="-2" FldGpDigits="-2" FldQuoteS="'" FldQuoteE="'" FldMultiUpdate="1" FldResizeQuality="75" />
</Table>
4

I’ve identified the location in ew.js where the initial random number is generated.

if (isAutoSuggest(obj) && self.htmlForm) // Auto-Suggest (init form or auto-fill)
        data["v0"] = ar[0] || random(); // Filter by the current value
      else if (obj.options && !obj.list && !isNativeSelectOne(obj) || // Not <selection-list> or native <select>
      isModalLookup(obj)) // Lookup
        data["v0"] = ar[0] ? obj.multiple ? ar.join(ew.MULTIPLE_OPTION_SEPARATOR) : ar[0] : random(); // Filter by the current value

Using a list of UUID fields in the database, I’ve created a workaround to force the “random number” to be UUID-like.