1. Scott Roberts
  2. Valentina Studio
  3. Friday, July 06 2018, 02:15 PM
  4.  Subscribe via email
How do I specify values for dynamic parameters in the SQL Editor of Valentina Studio? I would like to test a SQL statement such as:


SELECT field1 FROM table WHERE field2 = :1


I get error 0x7004. Using clause required for dynamic parameters.

How do I specify the using clause in SQL Editor?
Comment
There are no comments made yet.
Crescenzio Gallo Accepted Answer
Ok, it works fine now in MySql :-)

Many thanks,
Crescenzio
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Dear Crescenzio,
sorry did not see your question.

MySQL should use $1 form of binding params
OR SQL Standard ?

Can be a few forms:

? - SQL Standard
:n - all dbs except PostgreSQL
?n - SQLite
$n - PostgreSQL + MySQL + MSSQL
Comment
There are no comments made yet.
Crescenzio Gallo Accepted Answer
I have downloaded VStudioPro 8.6 and tried to use the new useful Parameters feature in the SQL Editor window. But unfortunately I always get from MySql a syntax error just around the parameter (see screenshot). My query looks like:

SELECT * FROM table WHERE field = :1;

and the value for parameter :1 has been filled in the right side list of parameter:value pairs.

Can someone suggest something to check?

Thanks,
Crescenzio
Attachments (1)
Comment
There are no comments made yet.
Scott Roberts Accepted Answer
Great! Thanks!
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Ruslan Zasukhin Accepted Answer
Hi Scott,

8.6.1 adds Find/Replace in the selected text, 8373 - resolved.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Ruslan Zasukhin Accepted Answer
This is about Find/Replace in the selected text only.

http://www.valentina-db.com/bt/view.php?id=8373
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Scott Roberts Accepted Answer
Hi Ruslan,

You're right. I had forgotten about the types. Currently, I do a copy/paste from the source of the procedure being called and then edit it to remove the types.

Often, the variables that I am passing have the same names as the parameters, except that they begin with 'v' instead of 'p'. It is usually little effort to replace the 'p's with 'v's, but that brings up another idea.

Would it be a simple change to modify the SQL Editor Find/Replace to add an option that text is replaced only within the currently selected text? That would have other uses beyond renaming parameters to procedures.

And, one more thought. Could this feature to show parameters be extended to include built-in functions? For built-in functions, it would be nice to have the parameters that show up in the tooltip to be inserted if the developer accessed the menu option/shortcut key. For example,


lpad()


would expand to


lpad( Str, Len, PadStr )
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Ruslan Zasukhin Accepted Answer
Hi Scott,

about idea 2) -- here more or less clear to me.

I only think that after replace it should be without types

call sp1
(
p_parm1, -- This is parameter 1
p_parm2 -- This is parameter 2
)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Scott Roberts Accepted Answer
I hesitated to post my idea here because I did not want to seem ungrateful (as if the feature were not good enough). :D

One of the ways that it saves time for me is when I copy a SQL statement to the SQL Editor in order to test it. In the past, I had to replace the placeholders with actual data in order to run the query. Once I had the query running correctly, I had to replace the data with the placeholders before copying it back to the source code. This feature allows me to leave the placeholders in the SQL statement. It also allows me to run a query multiple times with different parameter values more easily.

It turns out that I actually have two ideas related to this.

1. Expand the feature to recognize parameters in stored procedures.

For example, if sp1 is defined as:


create or replace procedure sp1
(
p_parm1 string, -- This is parameter 1
p_parm2 int -- This is parameter 2
)
...


then p_parm1 and p_parm2 would appear in the parameter panel.

There could be multiple calls to parameterized stored procedures(/functions) within a BEGIN...END block. If the Editor could distinguish between them and show the parameters for the call at the cursor location, that would be a nice feature.

2. Auto-fill of parameters in stored procedures

This would allow a procedure call to be typed as:


call sp1()


in the SQL Editor. A menu option/shortcut key would cause the statement to be replaced with


call sp1
(
p_parm1 string, -- This is parameter 1
p_parm2 int -- This is parameter 2
)


The Editor would need to recognize that the stored procedure has parameters and would replace the () with the text from the stored procedure itself. This would retain any formatting (tab, return) that the developer included so that the result would be according to the developer's preference. Also, this operation would apply only to calls followed by "()" that target a stored procedure with parameters.

There could also be a setting in Preferences to allow this to happen automatically whenever a call to a parameterized procedure is followed by ().
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Ruslan Zasukhin Accepted Answer
We also did think who and how will use this feature?

A developer which write code in Xojo / PHP / etc ...
want to use SQL queries with binding, so he wants test them as is in Valentina Studio ...

then ... what he wants also?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
Ruslan Zasukhin Accepted Answer
Hi Scott,

we are glad to hear you like it :)

Before sending to mantis, we can discuss it here, to better formulate it in Mantis later.

We also was going to ask developers:
- what another dream you may have around this parameters?
- for example, we think - maybe generate some code? e.g. for Xojo or PHP for this query ...
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
Scott Roberts Accepted Answer
Hi Ruslan,

I downloaded the release and this feature is great!

I have another idea related to it and will post that as a feature request on Mantis after the release appears on the official site.

Thanks,

Scott
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
Ruslan Zasukhin Accepted Answer
You already can download 8.6 build from here:
http://valentina-db.com/download/prev_releases/8.6

on official site it will arrive a little later after some testing
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Scott Roberts Accepted Answer
This is great, Ruslan! Thanks!
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Ruslan Zasukhin Accepted Answer
Hi Scott,

we have implemented this feature in 8.6 release. It is going to be shipped today or tomorrow (24-25 Sept)

Meanwhile, you may read Release Notes of 8.6 here:
http://valentina-db.com/docs/dokuwiki/v8/doku.php?id=valentina:releases:8.0:8.6
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 15
Scott Roberts Accepted Answer
I'll post this to Mantis. I posted here first because I thought it might be available and I could not figure out how to do it.

Thanks for your help.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 16
Ruslan Zasukhin Accepted Answer
It will be better if you place a feature request from your name into Mantis.

=========== Mantis Info =====================
It is better to report such issue(s) into Mantis - our bug tracker.
<http://valentina-db.com/bt>;</http:>

Why is it better? Described in these FAQs:
<http://www.valentina-db.com/dokuwiki/doku.php?id=valentina:faqs:vbugs>;

* Each separate issue report into separate issue report.

* If you have database or project then attach it compressed to the report.
you can put up to 250Kb archives.
* Provide in the report as many detailed info as you can: OS, version, locale, ….
* If possible, please provide exact steps how to reproduce problem.
=========================================
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 17
Ruslan Zasukhin Accepted Answer
Well, then we need put this as Feature Request into Mantis.

Today-tomorrow we going ship next hotfix build 8.3.5,
and the next week this feature can be implemented I think for new 8.4 build.

I think such params can be on the right side - as pairs by vertical
param_name : value
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 18
Scott Roberts Accepted Answer
I would like to be able to test a query that has parameters in the SQL Editor before using it in my application code.

It would be easier to make changes to the query in the SQL Editor rather than stepping through my application code with a debugger and trying to adjust it there.

Being able to create, modify, and test queries in the SQL Editor before adding them to my code allows me to focus on the query itself and prevents the possibility that something in my application code other than the query is affecting the results.

I do this with my other queries; I'm just not able to do this with a query that has parameters.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 19
Ruslan Zasukhin Accepted Answer
Hi Scott,

dynamic parameters is feature for ADK and CODE,
when you have some loops and pass that params.

in Valentina Studio's SQL Editor we do not see sense have such params. Do you?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 20
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories