Hi,
I’m not getting an identity returned after inserting a record.
My ID field is a uuid (created from a custom formatter). It’s database field is user_id (in this instance). After insert I have a single query to retrieve the user just created by identity. The database insert is created fine.
How does Wappler know the identity column to return and is there something in the insert above that stops it from being able to retrieve it?
Thanks!
It is different in all databases, there is no standard for it. We still having problems with postgres to get the inserted id back. Could you tell us which Server Language (PHP, ASP or .NET) you are using and which Database Server.
I’m using PHP and the Database is MS SQL Server.
In PHP PDO::lastInsertId (https://www.php.net/manual/en/pdo.lastinsertid.php) is used, this normally works for each supported database. If somehow the build-in method doesn’t work, in MS SQL you can get the last identity with SELECT @@IDENTITY, you could create a custom query directly after the insert query to retrieve it.
How did you manage to create this record {{$_POST.user_id.uuid ()}} using a custom formatter? I checked all possible formatting options that were offered in the custom formatter, but there was no option with uuid(). If I add this function manually (without using the formatter), I get an error executing the server action:
How do I create a uuid for a database entry?
My hosting uses PHP and MySQL 5.6
Does this issue still exist? This my be a root cause for my problems using postres and currently returning no data to the page using id’s.
Hey, @Mr.Rubi
Post below should give you info to add a custom formatter which creates uuids in php. Just ignore the password hashing section which is now built in.
The issue for me was specific to MS Sql with UUIDs.
If I used MySQL it was fine, but MS Sql doesn’t seem to return the identity unless using a column specifically set as identity. This then limits database types you can use (and ruled out UUID). I ended up using an auto incrementing integer for the ID column alongside a unique identifier column for use in URLs.
Not out of the question you could get similar issues with Postgres, I’ve not used it, but I think it’s unlikely unless you’re using an unusual ID format.
Thank you very much for your solution @sbecks, I solved the task and learned a lot of new things for myself. 
Another interesting question is:
UUIDs generated in this way can be repeated if a large number of records are added to the database? If so, how did you get around it? Or was it not a matter of principle for you?
I have used a similar method and used an update step, after the insert to add a 8 character random string (I used SHA on the timestamp and then substring to reduce to 8 chars) combined with the id integer value making it impossible for that code to be repeated. e.g. fe5ce1eb163
Do I understand correctly that you will end up with an expression consisting of 44 characters (36 uuid characters + 8 characters of your expression)?
No. For me all I wanted was to make guessing reference ids in web parameters difficult. I couldn’t get the proper UUID mechanism working without lots of custom queries so opted for my own version.
I end up with the 8 characters (you could always use more/less characters) + the integer of the record. As the number of records grows, the length of the ‘UUID’ will increase as the length of the integer increases. Having 8 characters gives 2.8x10^12 possibilities making it possible someone could guess/brute it but very unlikely.
e.g. fe5ce1eb163 (fe5ce1eb = 8 char generated & 163 = record id)
You could, to make the record id even more obscure, sandwich the integer between two shorter random strings
e.g. fe5ce163eb3qr7 (fe5ce = 1st string & 163 = record id & eb3qr7 = 2nd string)
in this example there’s a total of 11 generated characters (~1.3x10^17 possibilities) and the record id
Hi @bpj, I am currently trying to achieve exactly what you mentioned here, a short unique identifier that can be passed through urls etc to avoid guessing. I understand I have to create a seperate field, but can you explained how you applied SHA on a timestamp and then added the id at the end? How is this achieve using Wappler interface/code?
Many thanks.
There is a UUID field available now in the picker which may be enough for you?
For the method mentioned:
You can use this value to update the DB record and/or click Output to send it client-side
