Creating Custom SQL Queries

[Teodor]

You can use the Custom Query component in Server Connect in order to manually add a custom SQL query. You can define your own parameters, edit the schema and metadata and preview the results.

---

Creating Custom SQL Queries

You already know how to create a database connection in Wappler, so we won’t show this again.
In your server action with the database connection, right click the database connection step:

Open Database Actions and select Database Custom Query:

Click the Build Query button:

In the Custom Query dialog, you will see your database tables listed on the left. In the SQL Editor area you can write your own SQL Statements. Code autocomplete options are available, so it makes it easier to write your queries there:

You can also directly paste your statements, generated by any other software there and then click the Run button to preview the results:

The query results tab will automatically show the results:

Open the Schema tab, to edit the schema that will later be available in the Dynamic Data Picker on your pages. The schema is automatically populated with the results from your custom SQL query. You can edit the data type or remove any fields you don’t want to use:

You can add Parameters to use with your custom SQL Statements. Just open the Parameters tab:

Then click the Add New Parameter button:

Add a name for your parameter and assign a dynamic value for it:

Select your value using the dynamic data picker dialog:

You can also add some test value, to test your query with:

Click the OK button, when you are done:

Save your server action and you are done:

Now you can use your custom query results on the page, just as you do with any other normal query.

[nevil]

Very cool.

So how do I use a variable exactly? Is this correct?

[brad]

Set your variable using Globals > Get > Variable

[nevil]

So I cannot use any variables/data from the > Steps section?

[George]

Just double click on the expression field, a picker icon will appear on the right.

With the server connect data picker you can pick any expression from the previous steps.

[nevil]

Yes I know, but how can I insert this parameter into the custom SQL query? Like I described in my screenshot?

Because that returns NULL. Even though the setvalue has a value of 123.

[George]

Yes you inserted it correctly, just by name prefixed with :

Note that for the test to run you also need to provide a static test value ( column next to the expression)

[TomD]

I’ve just started looking at the new custom query feature. I think this will be a really useful feature which will remove some important limitations in Wappler.

Having said that, I’m not having much luck using parameters with custom queries. I tested with a very simple query, which works as expected in this example:

… but if use the parameter :P1 in any other part of the query, it fails. I think this is because the value is enclosed in single quotes automaticlly, which, depending on where it’s used, will result in a syntax error, no results or the wrong result.

How can I use parameters for the SELECT, FROM or WHERE values? (I also tried using parameters with ORDER BY - same issue.)

Clarification: the issue with the ORDER BY clause was using a parameter for DESC/ASC; it works for the column names.

[George]

Is the test run of the query failing Tom?

The test value needs to be enclosed in quotes if it is a string, so should be ‘%au%’

[TomD]

The example I gave (in the screenshot) works fine. In fact, if I enclose the test value in quotes, the query fails (I get no results).

As far as I can see, single quotes are being added automatically - so where these are needed, the query works; where they shouldn’t be used, the query fails. This makes the use of parameters very limited.

[George]

Parameters are handled by the underlying drivers indeed with automatic escaping.

You can place them anywhere in the query where you have a variable part.

Just use their name that is prefixed with :

[George]

That said, parameters are really for values only. You can’t create dynamic sql with them like having dynamic column names or sorting order based on parameters.

[TomD]

@George

You can place them anywhere in the query where you have a variable part.

In that case, shouldn't this work:

?

I can only get parameters to work in situations where they can be enclosed in single quotes - so I can't use them for dynamic table names or column names etc.. I hope I'm doing something wrong.

[George]

See my previous comment - you can’t have tables or column names as parameters.

This is how PDO prepared statements work with their parameters.

It is also saving you from a lot of sql injection attacks.

[TomD]

I was thinking I would be able to create completely custom/dynamic queries - but I see this was probably not a feasible expectation. Anyway, it’s a great new feature - thanks!

[George]

It is just for any parametrized sql query you would normally run on your database.

It can be super powerful indeed.

[TomD]

One thing I need to develop is a means of users creating their own queries (using a UI created in Wappler). I imagined I could store the queries in a database table and use the custom query feature to run the queries - but I’ll find another method.

However, I can think of lots of uses for the new feature as it is - super powerful, as you say.

[sitestreet]

I'm stuggling to implement a filter on a custom query and have found this thread.

However,

@George

The test value needs to be enclosed in quotes if it is a string, so should be ‘%au%’

I think is no longer the case. I've got test results showing when NOT putting in quotes.

BUT, I just can't get it to work.

In my WHERE clause I have

project1_registrations.registrations.regEmail LIKE :P2

and in my Parameters I have

Name
:P2

Expression
{{$_GET.filter}}

Test Value
&amy%

It gives me Query Results as expected but isn't working on the page. I'm fairly sure it's down to the % symbols not being there but I've tried putting them in just about every possible place and nothing is working.