Add Mapped Sql Field as Column in Parameters Tab

Posts   
 
    
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 08-Jan-2019 21:24:56   

In the Real Time Viewer There are a series of tabs at the bottom, the first (Sql) shows the sql with the parameters as @p1 etc. The second (Parameters) shows the @p1 and the value among other things. It would be nice to add a column that showed the sql column the parameter was being applied against from the where clause, like @p1 | 15 | 15 | CustomerId

I have queries with an in list and other constraints, it is hard to see from the parameters tabs what matches with what.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Jan-2019 10:34:34   

That's not really possible, unless the SQL query is parsed and analyzed, which for name=value pairs is perhaps 'doable' but for more complex situations it's quickly becoming very hard. The parameters tab is mainly used to see an overview of parameter details, so you know which parameter to look for.

Instead, on the 'sql' tab, there's also a Parameters section, which should make it easier to see which value a parameter had. Could you elaborate a bit why that panel isn't sufficient enough?

Frans Bouma | Lead developer LLBLGen Pro
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 09-Jan-2019 13:49:41   

From the sql tab I can see


.... where 
customerid in (@p1,@p2,.....,@p999) 
and
(InvoiceTypeId = @p1000
or
InvoiceTypeId = @p1001
)
and
....
and
.... @p1051

in the parameter tab I can see that


@p1027 = 15

What I would like to be able to see is that @p1027 is applied against column (field) invoiceStatusId without a lot of toggling back and forth between the tabs

There is a process that inlines the parameter values into the sql when copying to the clipboard if the results of that where available/visible automatically as each query is selected it would meet my desire.

the formatting could be improved a little by placing each parameter on it's own line, but as is, is fine. ..... 77 /* @p24 / 222656 / @p25 */

Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 09-Jan-2019 17:34:27   

Hi Frans,

Maybe you could (also) add another button 'copy to clipboard with replaced parameters' or so that would replace all @p.... with the real values. I know that this wouldnt create good sql for types other then numerics but it is then much easier to read what 'final' sql would be created , almost then wink

BTW, thanks for the new version! more responsive.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jan-2019 09:34:17   

arschr wrote:

From the sql tab I can see


.... where 
customerid in (@p1,@p2,.....,@p999) 
and
(InvoiceTypeId = @p1000
or
InvoiceTypeId = @p1001
)
and
....
and
.... @p1051

in the parameter tab I can see that


@p1027 = 15

What I would like to be able to see is that @p1027 is applied against column (field) invoiceStatusId without a lot of toggling back and forth between the tabs

There is a process that inlines the parameter values into the sql when copying to the clipboard if the results of that where available/visible automatically as each query is selected it would meet my desire.

the formatting could be improved a little by placing each parameter on it's own line, but as is, is fine. ..... 77 /* @p24 / 222656 / @p25 */

ah so you want to toggle between inlined / real query in the sql query pane? Otherwise I have no idea what you want, as on the SQL pane, you have a parameters section (on the right) where you can see the parameter value. Admitted with a lot of parameters finding the right one can be cumbersome, so having them inlined as when you copy them, is a way to work around that. Is that what you're after? simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jan-2019 09:35:41   

Puser wrote:

Hi Frans,

Maybe you could (also) add another button 'copy to clipboard with replaced parameters' or so that would replace all @p.... with the real values. I know that this wouldnt create good sql for types other then numerics but it is then much easier to read what 'final' sql would be created , almost then wink .

The checkbox below the SQL pane is for that. It's checked by default, so if it's checked, and you click copy to clipboard, it copies the sql query to clipboard with each parameter replaced with the value (if possible of course. Large binary values aren't possible)) it has. Not sure what you mean otherwise?

Frans Bouma | Lead developer LLBLGen Pro
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 10-Jan-2019 13:35:39   

Is that what you're after?

That would do it for me.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Jan-2019 20:08:26   

The checkbox below the SQL pane is for that. It's checked by default, so if it's checked, and you click copy to clipboard, it copies the sql query to clipboard with each parameter replaced with the value it has

Have you tried it?

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 11-Jan-2019 01:11:17   

Have you tried it?

Yes, as you move from sql statement to sql statement it's very clumsy to have to copy to the clipboard and paste into notepad or ssms in order to see the correlation between parameter values and what they relate to in the sql statement.

At the high levels of a query the relationship is often clear, but as you get into deep prefetch paths with integer relationships, it would be nice to be able to see what the parameter value relates to.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Jan-2019 06:30:48   

arschr wrote:

it would be nice to be able to see what the parameter value relates to.

Please elaborate more with an example: how you see it right now, and how you would like to see it. Remember that some params are not directly related to one fields, but with an expression, even with a derived table.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jan-2019 09:03:33   

arschr wrote:

Have you tried it?

Yes, as you move from sql statement to sql statement it's very clumsy to have to copy to the clipboard and paste into notepad or ssms in order to see the correlation between parameter values and what they relate to in the sql statement.

At the high levels of a query the relationship is often clear, but as you get into deep prefetch paths with integer relationships, it would be nice to be able to see what the parameter value relates to.

Yeah I think I have a good understanding of what's required. There's some confusion in this thread as Puser mixed a different point into this thread, so let's not muddle the water with 2 different things wink

The thing Arschr wants is simply to view the SQL query text inside the ORM profiler with the parameter values in-lined as they are when you copy the SQL query to the clipboard. This is easy to do. A shame tho that you report it after we've released RTM a few days ago...

The thing Puser wants is IMHO already built-in, so I leave it at that without any further info simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2019 12:20:11   

Well, there's a slight problem. The SQL parser we're using keels over when there are /* comment */ comments in the query disappointed , so color coding/beautifying is disabled.

I checked if the sql parser is still around, it is, but it's so expensive ($2000+ for our case frowning ) so that's not going to happen (no word if it supports this too, so we're not going to do that).

I can add it, but beautifying the query is then switched off. I've added it as a checkbox to the query pane, which is by default switched off (so normal behavior), you can check it, and it remembers it for that session and all queries viewed after that have the parameters inlined (and beautification/color coding off). There's some basic beautification, but it's not as good as the sql parser does.

I have to admit, I hate it that it has to switch off the beautification/color coding, gives a buggy impression (well, there's a bug somewhere, but not in code we can change, the parser simply crashes, as it does in some cases).

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2019 13:19:42   

Postponed till a later version. The parsing isn't going to go well, and flipping back to unformatted SQL looks terrible. For the people who know what's going on, it might not matter, but for a person who looks at it will think it's a bug.

We have to switch to a better SQL formatter (which aren't that common in the .NET space, we use a fallback one for where the sql parser keels over but it's far from ideal, sadly) and also move the control to the text editor we use in the llblgen pro designer instead of the rich textbox we use now (the color coding is done by the sql parser).

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2019 17:17:02   

Re-opened, we found a solution simple_smile Stay tuned.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2019 17:40:59   

Please download the ORM Profiler from My account -> Downloads, it's v2.0.1, which now has additional options for inlining parameters instead of just a checkbox. The value is remembered across queries in the same application tab (the vertical ones), and defaults to the one which is equal to the checkbox value in earlier versions as that's still the best one in our opinion in most cases.

The parser problems magically went away when we reimplemented it, apparently we made some mistake, but the code is the same (but likely not wink ). Oh well simple_smile

Frans Bouma | Lead developer LLBLGen Pro