I tried to search for an answer to my new question on the forum, but I could not find it.
I’m interested in the ability to specify a custom variable that does not match any column.
Is it possible to use variables in sql connectore in queries? In sql we use @ for this.
How to correctly set the syntax in sql connector?
Our query in mysql is working. But sql connector produces an error: an error in the query syntax in the next line of the variable declaration.
It doesn’t like multiline queries.
Also it requires a row limit.
What are you trying?
set @pass=?;
insert into user
set id_user=(select distinct concat(‘u’, (select (count(id_user)+1) from (select id_user from user as tmp ) as tmp1)) from user as tmp2),
FIO=?,
email=?,
password=sha1(@pass)
;
Suggested changes:
Remove newlines to make it all on one line.
Possibly multi statement (ie. scripts) aren’t supported, if so make it just one insert, without setting a variable first, and without semicolons.
If you really do need variables and multi statements, perhaps create a stored procedure.
Thank you again!
How to create a stored procedure in bubble?
In the database, not in Bubble, have a read of MySQL docs.
Good luck!
Thank you for all your reply.
We have a new question.
How to call a stored procedure via sql database connector?
Here is the query: call get_id_zakaz(id_user=?, creation_date=?);
Error name: query issue unknown column id_user in field list
On the mysql side everything works fine. Can you help us?
I will slightly rewrite the question.
How to pass the variable (parameter) of the calling procedure via sql database connector?
Using your example, the simplest way is with positional parameters:
call get_id_zakaz(?, ?)
Parameters
$1 Name: id_user Type: text test value: blah
$2 Name: creation_date Type: date test value: …
Not sure what you’d put for date, see if you get it to work with just a text parameter, and hardcode the date value on the query… then try adding in different date formats.
Thanks for the answer.
How to transfer data (date) in the format: 2017-01-31 00:31:31? It’s mysql format “datetime”.
Note that MySQL datetime does no converting between timezones, so it will store the date “as received”. Bubble will send the date in the timezone of its server, i.e. UTC.
Action query:
call get_id_zakaz(?, ?)
Parameter:
$1 Name: id_user Type: text test value: blah
$2 Name: creation_date, Type: date, Test value: 2017-01-31 00:31:31
Then in a workflow you can add an action Data > name_of_your_query, and put a dynamic date value in.
Good luck!
Your answer pushed us to one thought and we were able to solve our problem. Thank you so much.
But we have one more question 
Why can sql connector not see the data that is returned from the stored procedure? Neither the variable “out” nor the result of the selection (the select statement) made in the stored procedure.
I don’t know why it doesn’t see the result, perhaps a bug with the SQL Connector. Can you ask Emmanuel, or raise a bug report?
A possible workaround is to run the stored procedure, then do another query to retrieve the results from the tables or views.
OK, thanks. The report was sent. I will keep you informed.
We will try to solve this issue.
Emmanuel asked to post our question on the forum.
Our post:
"We have several problems with the plugin, which did not work out at the bubles forum.
The sql connector calls the stored procedure on mysql, the incoming parameter is passed, the test values in the plugin itself are processed correctly. The query is used as a value, but when calling the query in bubble (I add it via an external API, and I select the name of the required query) sql connector does not see the outgoing parameter from the procedure, that is, the query result. At the same time, on the mysql side, the stored procedure executes correctly and the result returns. Tell me, please, what could be the reason."
As I said before - Sql connector does not see the result of executing the stored procedure. The stored procedure is “call offer_id (?);”
Example: Id_offer = insert_offer(More…)
Inside, in theory, there should be: " ‘s id_offer_bub_proc"
We also posted an example application: https://bubble.io/page?type=page&name=example&id=example-sql&tab=tabs-2
All accesses have been given.
I attached screenshots.
We think that we managed to find a bug in the sql connector. When calling a stored procedure from the sql connector, it does not see the fields received in response as a result of execution. The procedure is called and correctly executed when initializing and entering test values.
But in the application it is impossible to access it via “get data from an external API”, because sql connector does not see the fields it receives. But if you first write in the query field with a “select” on the desired field. And then change the text of the request to call the stored procedure, everything works out correctly.
Look in the above application for an example.
In sql connector there are two requests: “insert_offer” and “ins1”, in which the query text is the same, calls the same stored procedure “offer_id”, but in “insert_offer” does not see the field “id_offer_prots”, and in “ins1” sees .
Can anyone verify that using stored procedures in MySQL via the SQL connector actually works? This is one of the few posts on the forum that discusses this, and after following along with the back and forth, I’d say it doesn’t end well. Something like this:
call sp_proc (?,?)
Before I invest in building something to find out, I was hoping to hear it works for others.
Before calling the procedure “call sp_proc”, you need to create a query “select” in the same query that is created in the sql connector, with fields that are returned by the stored procedure in msql. The main thing is that it was in one query in the sql connector
Then you need to initialize it one time, so that bubble sees these fields. Then delete the “select” and write the procedure call already. It’s like a bug.
So, paraphrasing what you say, you have to trick it with a SELECT statement first, and return the fields and data types into Bubble. For example:
SELECT my_date, my_number, my_text FROM my_table WHERE my_number > ?
Run it, let Bubble see the results, and then replace it with …
CALL my_proc (?) [Which returns fields like the above.]
Hopefully I’ve got that right. Seems like a pain. It would be nice to see this fixed, stored procedures are a very useful way to separate business logic and make it reusable.
