Hi,
I am unsure why the MySQL query below, whilst works and insert into DB without any errors, doesn’t output the two values when testing in Server Action? the vrfyCode_code and vrfyCode_key insert in DB, but don’t show their values in the Server Action so I can use then in the App connect side.
INSERT INTO users_codes
(vrfyCode_usr_id, vrfyCode_code, vrfyCode_key, vrfyCode_created)
VALUES (:P1, LPAD(FLOOR(RAND() * 999999.99), 4, '0'), (SELECT UUID()), :P3)
The output for a custom query doesn’t return the same as native insert/update (identity/affected). You would have to add a query afterwards to select the relevant information for use on other steps or on client-side.
I’ve never tried but you might be able to add a semi colon to the end of your insert statement and then a select statement in the custom query. Not sure if it can handle 2 statements in one query.
Hi @bpj, I tried adding another query afterwards to select the info. still not working.
Any other ideas.
Whats the way around using output from custom queries as parameters?
What to you get in the Network Tab of the browser Dev tools?
Otherwise, use a single query afterwards to select the new item
So what have you tried exactly?
hi @Teodor,
I added another step, a single query, to get the output from the previous customer query step.
Everything inserts properly in the DB without problems, its just that I can’t use the customer values as a parameter.
In the single query
use the condition
vrfyCode_usr_id = usrID
for sorting:
vrfyCode_id DESC (to ensure the latest is returned)
It changes the condition to ‘0’ value.
All I am trying to do is to be able to use the output values from a custom query, I don’t really understand this workaround. It inserts fine the DB.
An insert statement doesn’t generate any output - so nothing is coming out of the custom query
You could try:
INSERT INTO users_codes
(vrfyCode_usr_id, vrfyCode_code, vrfyCode_key, vrfyCode_created)
VALUES (:P1, LPAD(FLOOR(RAND() * 999999.99), 4, '0'), (SELECT UUID()), :P3);
SELECT vrfyCode_usr_id, vrfyCode_code, vrfyCode_key, vrfyCode_created FROM users_codes
WHERE vrfyCode_usr_id = :P1
ORDER BY vfyCode_id DESC
The little play button at the top allows you to run the query to test it and it automatically generates the schema from the output. Use the parameters tab to set test values for the user id and create date
Good point @bpj - but with other queries, I can get out what was inserted. For example, I can get the IDENTITY or I can then create another single query to get out what was inserted in the Custom query.
I tried the INSERT and SELECT query you kindly provided, and received an error.
OK You might not be able to run two statements in one custom query
You can do this - just your single query in your screen shot is wrong.
Did you try selecting the user id using the lightning bolt data picker?
Yes I did use the lightning bold data picker, it puts the
zero’ by itself.
I just want to be able to use the data that was inserted by the custom query. I have set the Schema in the custom query so they can appear for the data bind.
As I said an INSERT does not return data so setting the schema does nothing - it just tells the UI that there should be fields to pick from. You usually don’t need to add the schema manually, just use the play button to run a test query and it will produce it for you. When you said you used the lightning bolt, did you point it to the same value as the parameter :P1.
(I presume this was the Set Value usrID step in your API file) - in the data picker, you can switch to code view and type usrID to assign it f it still puts 0 in
Ok I will try to remove the ‘0’ manually.
Can I ask, a normal INSERT does give us an ‘Identity’ we can use in other queries. Can we also do the same for a custom query and at least get an ‘Identity’?
The trouble with custom queries is they can be any type of statement not all of which generate an identity. Some complex statements could involve a mix of SELECT/INSERT/UPDATE etc… so I suspect it’s just not possible
Understood @bpj. So I took off some unnecessary queries for simplicity.
So what do I do after the custom query?
I just want to be able to extract what it inserted and be able to use it later as a data bind in the App connect.
How do I link what of inserted and use that with another query.
