So, I’m trying to move away from Dreamweaver and have a question in relation to MSSQL Store Procedures and advanced queries.
Can Wappler handle MSSQL Store Procedures? If it can, how do I perform these actions?
How do I perform advanced queries, especially when I need to add CASE as in the example below?
SELECT CASE tblHi5.intApprovedRejectStatus
WHEN 0
THEN 'Declined'
WHEN 1
THEN 'Approved'
WHEN 999
THEN 'Purged'
ELSE 'Awaiting decision'
END AS intApprovedRejectStatus
FROM tblHi5
INNER JOIN tblEmployees ON tblHi5.intEmployeeID = tblEmployees.intEmployeeID
INNER JOIN tblEmployees AS tblEmployees_1 ON tblHi5.intIssuerID = tblEmployees_1.intEmployeeID
WHERE(tblEmployees_1.intCompanyID = varintCompanyID);
not 100% sure but I think I‘ve opened already a smiliar topic based on mysql and as Stored Procedures arent same on MSSQL/MySQL and even their functions are DB related its not implemented.
I never used stored procedures, but what about triggering your cmd and handle all on DB side? It would also keep logic completely constrain
There are limitations when it comes to using advanced queries in Wappler. Though I hope this will be addressed at some point, there are alternative approaches which may overcome these limitations.
In this case I would using a calculated/generated column in MySQL (which I think is the same as a computed column in MSSQL). Sometimes you may need to use views. In other situations (which might be another option in your example), you can get the value you need in the front end - eg using a nested ternary operator (for your example).
Stored procedures are not supported. But as Tom explained, you can just return the results as “0”, “1” and “999” on the page and then format them as needed using the data formatter.
We don’t have plans to support stored procedures yet.
This is mainly because stored procedures can return all kinds of different data and there is no predefined output. So we can not show any data to pick from in Wappler’s data bindings pickers.
Also most of the functionality of stored procedures can be achieved with the regular query builder already. So there is a very little need to support them.
I am feeling rather distressed to have found this post…
I had assumed that Wappler would be able to support calls to stored procedures, and before starting my Wappler trial I have spent weeks creating them in MySQL. My database structure is very complex, often with a mass of database activity happening when one button is clicked… and I really need to create, test and manage it all using procedures within MySQL.
The key reasons for using stored procedures are:
Speed of execution
Speed and ease of debugging
The procedure based approach where no piece of code needs to be written more than once.
I am only looking to use stored procedures for data creation, so with inputs, but not necessarily to return values.
I am assuming that I will be able to call stored procedures, but I will need to write my own php to do that?
Does anyone have experience of doing this?
@George, it is sounding like even input-only stored procedures are no on the roadmap right now… is that correct?
Using Wappler you can do most anything…just comes down to what it natively supports in the UI and what needs to be done elsewhere – primary reason I love it.
On the SP, the call command goes in the query area rather than the name…let me know if you don’t get it sorted!
I am used to a whole concept in Bubble called “Privacy Settings” where you can set up some complex rules about which user can manipulate which fields from which data type. It feels very safe to have this all set up, and I am using the procedures to, amongst other things, replicate my security needs between different user types in this way.
I also want to keep my data management totally separate from my GUI, so that if, in the future, I decide to build another app another way (e.g. mobile), then all the access to my data is cleanly managed in MySQL, and I just have to build a new user interface that accesses that data through the same “gateway” I will have created.
Hi, just reaching out to you. - showed you are still current, which is fabulous after 2+ years!
At the beginning of my journey, I’m asking the same Qs as you did 2+ years ago.
One of my main queries is a SP so I needed to see if its possible.
So since you are experienced with back end SQL, 2 years later are you happy with the integration that Wappler provides? I use MSSQL.
Yes, I am super happy with Wappler. It does all I could ask for and am about to launch my huge business admin app in May after 2 years of Wappler development (and 2 years on Bubble before that! ).
It is quite an adjustment and steep learning curve, but definitely worth every minute.
I would NEVER want to be locked into Bubble again.
Hi @Antony! I have exactly the same project that we were implementing in Bubble for 2 years and now migrating to Wappler! Thanks for the encouragement! How is the project going? Within expectations or results above expectations! We opted for a NodeJS project with MSSQL, what do you think?