|
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command?
I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems.
The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables.
So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution?
EDIT:
I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away...
...the impossible takes slightly longer.
modified 30-Jun-24 16:00pm.
|
|
|
|
|
Richard Andrew x64 wrote: So even something as weird and wonderful as that has a use.
i think you are being far too generous & kind.
It's a terrible idea for numerous reasons. I know little about DBs & DBMS but there are a couple that seem glaringly wrong to me:
1) one of the big ideas of a SP is that it is precompiled. This build-string-query then exec would insure that wouldn't be true. Think about that. A SP is precompiled & knows the "execution path" but in this case that would never be true, so it makes entire no sense that this "dynamic" thing would be a SP.
2) built in query analyzer of SQL Server would not know what the execution would be & this probably causes performance issues & the inability to know if it is slow or not since the query is built on the fly
I'm going to assume that some dev with little experience got this "genius" idea for how to create dynamic queries and no one ever looked at it because "it works".
SQL Server is an amazing feat of true Engineering and will fix things for you so the dev is probably getting really lucky.
Plus hardware is probably handling this.
And probably if the thing really got serious traffic it would bog down to nothing.
Just another Lucky Dev -- they're 92% of all Devs anyways.
"That's not coding, that's typing." 
|
|
|
|
|
raddevus wrote: built in query analyzer of SQL Server would not know what the execution would be & this probably causes performance issues
Actually, the opposite is more likely to be true.
If you have a single query with lots of conditional filtering based on the parameters - eg: (@x Is Null Or T.X = @x) - you'll get one execution plan based on the first set of parameters used, which can be sub-optimal for a different set of parameters.
Having a different query for each set of applied filters can allow the query optimiser to select the "best" execution plan to satisfy that set of filters.
You may end up with some query execution plan cache bloat, and very complicated queries might take slightly longer for the first compilation. But you may still end up with better performance. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have done that very rarely, such as when the name of a table isn't known. For instance, writing a procedure which will di a TRUNCATE TABLE but fallback to DELETE if that fails.
Definitely not as a normal course of action.
Further, I have not trusted the input, but rather checked it against the sys.objects or similar table to be sure it is a reasonable value before proceeding.
|
|
|
|
|
They are used where I work. They are usually performing a reporting task. Building it dynamically allows changes to the actual select clause, as well as sorts, groups etc. They are a nightmare to debug...
|
|
|
|
|
Shane0103 wrote: They are a nightmare to debug...
|
|
|
|
|
Leaving aside the performance issues noted by @raddevus, this is a terrible security risk. While the legitimate code uses this e.g. to query the client table, what is stopping malicious code from querying the credit card table?
(Yes, I know that credit card Nos. should not be stored like that, but many databases do in order to provide a rolling subscription to their site.)
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.
|
|
|
|
|
Richard Andrew x64 wrote: submitting the queries with the EXEC command?
For SQL Server, they should at least be using sp_executesql[^], and passing the parameters as parameters rather than concatenating them into the string.
In some rare situations, it may be worth doing this - for example, if your procedure has a lot of optional filters, building a query that only specifies the ones being used will allow the DBMS to select the most appropriate execution plan for the query. If you put them all in the same query - eg: (@x Is Null Or T.X = @x) - then the execution plan will be selected based on the first set of filters provided, which may be sub-optimal for a different set of filters.
But passing the string to EXEC rather than sp_executesql means they're introducing a SQL Injection[^] vulnerability into the code, which far outweighs any performance benefits.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This type of crap used to be all the rage in the late 90s/early 00s. I'm pleased to say I haven't seen abominations like this in the last 20 years or so.
|
|
|
|
|
Well, it's kinda sorta DIY ORM isn't it?
Yeah I've seen that in a bunch of stuff. It'd be better to have that all live in sprocs but sometimes there are edicts harder to argue with (all logic must be code side, none in DB) than just to work around.
|
|
|
|
|
Yeah, seen it too.
In fact, it was the default for a project I worked on.
The idea was that you could filter on something like ten to twenty fields and depending on which fields were set, the string concatenation added fields to the WHERE-clause.
The alternative was something like WHERE (X = @X OR @X IS NULL) AND (Y = @Y OR @Y IS NULL) AND (Z = @Z OR @Z IS NULL) -- Etc.
Nowadays I'd use LINQ to build such a query.
|
|
|
|
|
The intellisense for C++ in VS Code - at least when it works, is nothing short of incredible.
template<size_t BitDepth>
using bgrx_pixel = pixel<
channel_traits<channel_name::B,(BitDepth/4)>,
channel_traits<channel_name::G,((BitDepth/4)+(BitDepth%4))>,
channel_traits<channel_name::R,(BitDepth/4)>,
channel_traits<channel_name::nop,(BitDepth/4)>
>;
using rgb18_pixel = pixel<
channel_traits<channel_name::R,6>,
channel_traits<channel_name::nop,2>,
channel_traits<channel_name::G,6>,
channel_traits<channel_name::nop,2>,
channel_traits<channel_name::B,6>,
channel_traits<channel_name::nop,2>
>;
What you're looking at is two arbitrarily defined pixels. One is N-bit pixel where 3/4 of the bits are used, and the second example is a 24-bit pixel where 18-bits are used.
That's not really important, but the channel names are, because consider this:
rgb18_pixel::is_color_model<
channel_name::R,
channel_name::G,
channel_name::B>::value
If you know C++ you can tell there's metaprogramming magic here. What I'm doing is querying a "list" of channel traits at compile time looking for ones with particular names.
The thing is, if you hover over value, the extension will resolve it to true in the tooltip that pops up - no easy feat.
More impressive even is this:
using color18_t = color<rgb18_pixel>;
auto px = color18_t::gray;
It will determine the actual numeric value for that color and display it when you hover over "gray"
(2155905024)
You think that's easy? No.
constexpr static const PixelType gray = convert<source_type,PixelType>(color<PixelType>::source_type(true,
0.501960784313725,
0.501960784313725,
0.501960784313725));
Notice it's running a constexpr function convert() to get the destination pixel format. This is a non-trivial function.
So one of two things is happening here.
Either the C++ extension for VS Code has a compliant C++ compiler front and middle built in (I suspect it does) or it is managing to link itself to existing compilers like GCC tightly enough to determine this output (which doesn't seem possible to me)
Either way, go Microsoft.
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
Totally agree.
And the Copilot has helped me greatly with boilerplate code.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I haven't messed with Copilot. I'm "AI" averse, and will probably remain so until they get better. I like Visual Studio's AI integration because it's explicit - you have to smash tab at each step and it shows you what it will do next. It's important because it's so often wrong.
I'm not sure if Copilot works like that or something else, but honestly, I can pretty much think in C++ at this point, so it's almost more effort to have to prod an LLM to give me the code I want. By the time I do I could have figured it out with going from C++ to English to English to C++.
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
Yes, that's the same way that Copilot works.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Interesting, thanks. I might tinker with it, if it's free.
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
I started playing with Copilot a couple days ago. I found the experience a bit freaky at first, kind of like the thing being inside your head, anticipating what you want to do. It was really good at first, but as the application I was working on evolved, getting into the more detailed items, it started writing code with mistakes in it.
I wanted to see what it could do, and started with letting it write the code as much as possible (you know, like the typical QA question at CodeProject.  ). I started with a simple FileSystemWatcher app and described that I wanted a WPF app that monitored a directory and its subdirectories and logged all the changes it saw. It generated a simple app with a FileSystemWatcher, but more like a Console app with no XAML UI. It wrote everything with no problems, working as expected. Impressive, but I wanted to see how far it could go.
Next, I told it to change the app to a XAML UI, using MVVM and a TreeView, and keep that updated with changes as they happened in the file system. It explained everything, walking me through all the code changes and what I needed to add/remove. It came up with the hierarchy model and the correct XAML bindings and everything! That was mind-blowing! I didn't expect it to figure out how to rewrite the code for an entirely new UI. Talk about freaky!
I was just evolving the app, one step at a time, describing what I wanted the app to do, and it was coming up with the right suggestions, and that's when the mistakes started. The first mistake was a minor issue with a XAML binding that it didn't wire up correctly, one-way instead of two-way. I pointed that out and it actually said I was correct and came up with the correct, simple fix. Wait, this thing can figure out its own mistakes?!
OK. Let's give it something more difficult to understand. At this point, the window was split into the TreeView on the left and a log of FileSystemWatcher events on the right. I got it to coloring the event messages, based on event types, like Changed, Created, Deleted. It put together a ListBox with a TextBlock for each item in the list, with the correct DataTriggers for each message type and the correct Foreground and Background property setters. I told it to change the ListBox to a ListView, and it correctly rewrote the XAML and kept all the formatting. I told it "that didn't look right to me and to back out that change," and it did exactly that, going back to the ListBox code. Mind blown!
Very impressive so far. Let's dig into a little more detail. The background of the TextBlock items in the ListBox only went so far as the length of the text, but I want it to fill the width of the available space in the ListBox. I described this, and it actually understood that, coming up with wrapping each TextBlock in a Border object and moving the DataTriggers for the Foreground and Background property setters to the Border tag for formatting! Whaaaaa?! Cool.
But here's the bigger mistake. The Border tag in XAML doesn't support a Foreground property, only a Background. It didn't immediately understand the context the Border was being used for and went for just moving the TextBlock.Style section to the Border.Style section without checking the property setters in their new context like we would.
That was interesting and gave a bit of insight into the limits of the context it's keeping track of. It won't generate its own "sub context(?)," like checking its work as it makes changes, in this case, checking the property setters for correctness in their new home. It can recover, however, if you know about and point out the mistake. I told it the Border element doesn't support a Foreground property and asked it "what it could do about that," in those exact terms. To my amazement, it told me I was right about Foreground property, and it fixed the problem by moving the Foreground Setters back to DataTriggers the TextBlock.Style section!
 I was floored.
My second experience with Copilot was a bit harsher. Keeping with the "typical CodeProject question poster" theme, I intentionally simplified my questions, leaving out all context as to what I wanted to do and in all my questions. Copilot did not like that AT ALL. It was constantly telling me to provide more context in my questions and commands, but it was coming up with possible intents asking me questions about what I was talking about and rephrasing my questions into terms and contexts it could understand, just like we do while answering questions here. It gives you links you can click on for your questions but rephrased in the context it thinks you're talking about, and it'll answer them and make suggestions.
I found that very interesting. It seems to get just as frustrated as we do with people here. It makes me wonder if it was trained not only on GitHub code, but on all of SO and CP.
Is it going to replace developers? No, not a chance. You still have to learn how to break your problems and questions down into smaller, organized chunks. You have to think about your problems and not only how to break them down into smaller chunks but do so in a manner you can accurately describe while providing proper context.
Like in my sig, asking questions is a skill, even when asking them of an AI, and this is something the noobs and non-coders struggle to learn.
|
|
|
|
|
I've been tinkering with these free AI tools for a while now, specifically ChatGPT, Copilot and codepal. The only one I've tried for generating code was codepal. Not very impressed: it only generates code snippets ('functions'), and after very few such short snippets it reaches the limit for free usage. For not code related questions, I found ChatGPT far better than Copilot. It used to be really bad just a few months ago, but it significantly improved. It reaches the limit for free usage pretty fast though, requiring a few hours of timeout. Copilot is just pathetic, in my experience, at this time. Both Copilot and ChatGPT give quite often wrong answers - they don't 'realize their own mistakes', but rather admit them when pointed out, leading to 'I apologize for the confusion/misunderstanding/frustration...'. Almost never 'for the wrong answer'! They're trained well 
What they're not trained to say is 'I don't know', which I'd prefer, instead the constantly wrong answers. I usually know the correct answer, or at least I know when the answer is wrong, but what happens if I don't? They're nice toys, but not really useful and trustworthy in my opinion.
Maybe I'm not very good at asking questions (they can understand), but I do it in a very clean manner and provide enough context, so I doubt that.
I would be myself impressed with any decent answers (nevermind code generation), because my expectations are pretty low, but so far I'm not.
Your Mileage definitely Varies!
|
|
|
|
|
float mpu6886::inv_sqrt(float x) {
float halfx = 0.5f * x;
float y = x;
#pragma GCC diagnostic ignored "-Wuninitialized"
#pragma GCC diagnostic ignored "-Wstrict-aliasing"
long i = *(long *)&y;
i = 0x5f3759df - (i >> 1);
y = *(float *)&i;
#pragma GCC diagnostic warning "-Wuninitialized"
#pragma GCC diagnostic warning "-Wstrict-aliasing"
y = y * (1.5f - (halfx * y * y));
return y;
}
Tell me how y is uninitialized? This isn't the first time I've encountered this. 
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
Ah, the fast inverse square root. Mind boggling code, I have not spent the time trying to work out how it works.
Without the code they commented then y would very likely have a terrible guesstimate and the function would be inaccurate, so y has not been 'initialised' with a good first guess. Weak definition of initialised, but some grain of logic there.
|
|
|
|
|
but i explicitly assign y to x. 
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
Ah, sorry, I completely misread your original post.
I would guess the error/warning is because you can not guarantee the sizes of long and float will be the same between C++ implementations. Just a stab in the dark though, and it seems like a strange error for that.
|
|
|
|
|
I would guess std::bit_cast in the line long i= ... should help.
|
|
|
|
|
no std available here
Check out my IoT graphics library here:
https://honeythecodewitch.com/gfx
And my IoT UI/User Experience library here:
https://honeythecodewitch.com/uix
|
|
|
|
|
If you split assignment via a long i; and float y, halfx; does it still happen?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
