|
Off topic - I would assume that "Proj_Stage" would be in another table and should have a sequence no in that table, this would eliminate the case statement in the where clause.
If it is input text then you have another problem altogether!
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm stuck with the existing database to make it backwards capable, with older construction projects. Every record has a ...
project number
project stage
version number
This table keeps track of the versions of the construction project. As typical, customers that have construction done always change their mind during the construction process, and don't understand once you pull a permit from the city, you can't change the project. But they change it anyways and then you have to get a new permit.
I didn't get any documentation, nor useful comments and had to reverse engineer the old version to make this new version of the software. And it was very broken and needed fixing, thus below ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Even if you cannot change the existing tables in the database, you should be able to create new entities.
I'd still create another table with 2 columns in it as suggested by @Mycroft Holmes[^] as suggested above[^] (cols: Proj_Stage and SortSequence), indexed by Proj_Stage. Then
LEFT JOIN ProjStageSortSequence AS ps ON proj_vers.Proj_Stage = ps.Proj_Stage and change the ORDER BY to be
ORDER BY ps.SortSequence, vers_id . You would also need to change Proj_Stage in the SELECT to proj_vers.Proj_Stage. These changes will save loads of CONVERT() operations and should (not tested) run faster as SQL SERVER is very good at optimizing joins with low nos of rows.
This leaves the existing table unaltered and would be more efficient that the query it already has. It would not be as efficient or as normalised as using the sort sequence in the existing table; but it is a stepwise improvement.
|
|
|
|
|
Took a few minutes for me to absorb, but I get it now, and that's an enhancement that would be backwards compatible. I would be able to apply this new table to every other table that uses the project stages, which is pretty much about 15 more tables.
I'll dabble with this tonight at home in my new home office. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your Awesome! 
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Your welcome. Been there done that as they say.
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Presumably no one is ever going to use version number 1.1 or even 1.0
|
|
|
|
|
Just whole integers, maybe up to 20 if the customer gets real bad, or excited about their swimming pool while it's being built.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions.
What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures.
This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum.
Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well.
I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient.
What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon.
something like this
select * from people
where if @fNameActive then fname like @fNameParam
and if @dobActive then dob = @dobParam
Otherwise name and dob should not be evaluated.
I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case.
Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well.
Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner?
I can't be the only developer to run up against this wall. But it's so complicated that it's difficult to formulate an effective www search.
Could I have coded this? Just saw something similar on Stack Overflow.
SELECT * FROM people
WHERE
( @fNameActive = 1 AND fname like @fNameParam )
AND
( @dobActive = 1 AND dob = @dobParam)
It looks to me like the only way to get it to work is by omitting the column. I actually tried this on MySQL and it failed. If one of the () were false because of my simulated "activate test" I just coded 1=0 it returned no rows.
How about...
SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)
Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
I ask this question because I'm curious. I have no problem using parameterized concats on my home projects.
|
|
|
|
|
Was there ever a feature added to support what you were thinking of doing? No.
What you do in a case like this is either write your own syntax parser and query language to break down what is typed into a search box, or boxes, and build the query yourself, or use a dedicated indexing and search engine, like Lucene, to do it for you.
In my latest web app, I went with the home-built route. I have a single search box where you type your query. It understands a specific, home brewed, query language. That query statement gets tokenized and broken down into an "abstract syntax tree", or AST. That tree is then sent to another module that walks the tree, interprets it, and builds an SQL SELECT WHERE clause from it, complete with parenthesis, AND/OR operators. One important part you have to remember is building proper indexes on the database to support the queries to make them more performant.
|
|
|
|
|
Brian L Hughes wrote: Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
That sounds like the EU. We are having a tiny problem with X, let's ban EVERYTHING that resembles it. You can make those rules, but I will not ever bend software to fit some idiot idea.
You query stale data from a snapshot, from a readonly DB, as is best practice, using a locked down account. WTF is going "Stored Procedures" going to do in terms of added safety??
Brian L Hughes wrote: It looks to me like the only way to get it to work is by omitting the column.
In SQL Server it's called "free text search".
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Brian L Hughes wrote: I had an app that users could query tables. The app form contained many text boxes
That is pretty indefinite.
Especially as it says 'tables' plural.
Also it is not clear how the users build there statements. For example is it only 'and' or does it allow 'or' clauses also.
-------------------------------------------------------
There are in fact many tables and each table only has a few columns. Also there are no joins.
But 'many' actually means not all that many. So perhaps 10 tables and 3 columns each.
Then if it was me I would generate (not manually code) the variations to produce the procs. This would happen during the build not at run time. There would be a process to determine whether changes were made so that it would not require updating the procs every release. For example there would be a separate build that only runs when something is known to have changed. Such as adding a new table.
-------------------------------------------------------
But lets presume you had one table with a lot of columns.
First the UI should limit the number of clauses that can be created. That should be true regardless of any other solution. Nothing in a computer should be unlimited and in a case like this at some point one reaches diminishing returns.
Solutions.
1. Any process should have a process for exceptions. If it does not then the process itself is flawed (I spent 15 years in process control groups, principal process author and sometimes sole process controller along with 5 years in security process.) So either use the exception process or insist that one is added. Process control exceptions should of course be documented. Probably more so than any other process control step. So that would need to be done also.
2. Pass an array to the proc. The proc builds dynamic SQL and then executes it. There will be a limit to the size of the array. This of course is just a variation of your solution but moving where the code executes.
3. Create the procs dynamically. I would go ballistic on this solution if there was more than about 30 procs (per table). Even with generation at some point this becomes a maintenance problem.
|
|
|
|
|
Brian L Hughes wrote: SELECT * FROM people
WHERE
( @nameactive = 1 AND name like @nameparam )
OR
( @dobActive = 1 AND dob = @dobParam)
Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
So, you want to only have Georges born on the 30th Feb? If so, why no use De Morgan's laws - Wikipedia[^] and reverse your tests e.g.
SELECT * FROM people
...
WHERE (@nameactive = 0 OR name like @nameparam)
AND (@dobactive = 0 OR dob = @dobparam)
This should just treat unwanted criteria as true and just AND wanted criteria.
|
|
|
|
|
Where I either don't want this column included or the column matches param, then AND them across the where clause and presto!
I tested it on mysql and it worked!
It's kind of funny that I couldn't come up with a solution for this after years of sql coding. I will admit that sometimes I can't figure out multiple combinations of AND and OR tags in code.
Is the sql engine is smart enough not to include any actual column testing at runtime if the "include this column" param is 0?
|
|
|
|
|
The phrase you are looking for is 'short circuiting'.
https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated[^] quotes an SQL standard that says
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...].
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
I have not found any specific answer for MySql but IIRC MySql gives you a choice of engines so it might depend on the engine.
The answer for MS SQL SERVER (according to Understanding T-SQL Expression Short-Circuiting – SQLServerCentral[^]) is that short circuiting does happen (but that is specific to that one environment).
It is easy to test. If you have a WHERE clause like
WHERE NULL = NULL OR 1/0 = 1 then it will deliver TRUE if short circuiting is implemented and throw an error (trying to divide by zero) if not implemented.
|
|
|
|
|
jsc42 wrote: I have not found any specific answer for MySql
Interesting. I couldn't either.
I know it happens in C++ so I attempted to find that expression ("short circuit") in the standard and as far as I can tell it does not exist. I used the actual book to look it up. So for that (C++) it is expressed like the following
"The operators && and || will not evaluate their second argument unless doing so is necessary"
For C it is expressed as the following
"Expressions connected by && or || are evaluated left to right, and evaluation stops as soon as the truth of falsehood of the result is known"
Then I looked for that terminology in MySQL docs (8) and found nothing on the page that defines AND and OR that would suggest that.
I also attempted the same for PL/SQL and also found nothing.
|
|
|
|
|
Recently, I migrated from version 5.7.40-0 to version 8.0.31 of MySQL, but I faced the following error message on the columns that are time.
Error :
Code: 1292 SQL State: 22007 --- Incorrect datetime value: '2023-06-31 23:59:00' for column 'date' at row 1
MySQL Query :
SELECT
DATE(`Date`) AS `Date`,
COUNT(`Date`) AS `Record count`
FROM tbl_Pw
WHERE `Date` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`Date`)
ORDER BY `Date`;
How can I fix the problem?
|
|
|
|
|
|
thanks for your quick answer
date is field name not data type and its data type is DATETIME
SELECT
DATE(`MyDateColumn`) AS `Date`,
COUNT(`MyDateColumn`) AS `Record count`
FROM tbl_Pw
WHERE `MyDateColumn` BETWEEN '2023-01-01 00:00:00' AND '2023-03-31 23:59:00'
GROUP BY DATE(`MyDateColumn`)
ORDER BY `MyDateColumn`;
|
|
|
|
|
My apologies, I misread the question. The actual problem is as shown in the error message:
Incorrect datetime value: '2023-06-31 23:59:00'
The month of June (06) contains only 30 days, not 31. You will need to manually correct that field.
|
|
|
|
|
So summing up the other posts and from the OP.
Basically it appears that your database itself has invalid data.
How could that happen? Something pushed it in there before it was validated.
Or perhaps there is some problem with timezones and/or an actual bug in the database itself.
You might be able to fix a timezone problem but otherwise you would need to update the row to put a correct value in there. If it was me I would expect that there is other invalid data in that table and perhaps other tables with timestamps also. You would need to uniquely identify each row then craft an update statement to force that column to a correct (manually determined) value.
|
|
|
|
|
I got this off SO, wrt "Code: 1292 SQL State: 22007":
"Change the system timezone to the one that does not use DST ..."
Who knows why, right? When everything you always wanted to know about anything was everywhere. Exactly as it is, I mean.
|
|
|
|
|
Is it possible to insert default data in a table that was created by Entity Framework?
Here's my DBContext
public class SqlDataContext : DbContext
{
private string _connectionString = "";
public DbSet<UserEntity> Users { get; set; }
public SqlDataContext()
{
_connectionString = @"Server=MAROIS_KEVIN_1\SQLEXPRESS;Database=Test1;Trusted_Connection=true;Encrypt=false;";
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(_connectionString, options => options.EnableRetryOnFailure());
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<UserEntity>(entity =>
{
entity.ToTable("Users", "dbo");
entity.HasKey(e => e.UserId)
.HasName("PrimaryKey_UserId");
});
}
}
I would like to insert a default user into the User's table each time I create the database.
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
Awesome, thanks!
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
:text=MySQL%20retrieves%20and%20displays%20DATE,%3Amm%3Ass%20'%20format.