|
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.
|
|
|
|
|
hi,
i have a problem while executing updates using the update script inside the variable, it gives the error "String or binary data would be truncated";
I realized that if reduce the number of fields to update, it no longer gives the error!
But I didn't want to divide the execution of the update into 2 variables!!! Can you help?
Thanks!
|
|
|
|
|
|
How is the @Sql1 variable defined ?
Ex: Declare @Sql1 Varchar(200)
|
|
|
|
|
After thinking about this a bit more than required;
Member 11533892 wrote: But I didn't want to divide the execution of the update into 2 variables!!!
You mean you don't want to execute two update-statements for the same record. Two variables cost nothing nowadays, so that doesn't make any sense. Two updates must be atomic, or you get concurrency issues. Could be simply solved by using a transaction.
You may find the error to be the design of the table, not the update-statement. I'm going to guess a lot of those fields can be "NULL" or revert to some preset default value.
..but, let me give you a solution from a VB6 programmer view; you can rename all columns into numbers. So if column one is named [Name], you replace that with [1]. You can keep a list in Excel to look up the column names. That way, the update-statement is shorted a lot, and a huge lots of them would fit in a VARCHAR(MAX) command.
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.
|
|
|
|
|
I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:
SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
FROM OrderPymts
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDER BY Orders.OrderId, PaymentDate
To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance|
1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 |
1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |
Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.
modified 20-Jan-23 11:34am.
|
|
|
|
|
Assuming Microsoft SQL Server, ROW_NUMBER is your friend. 
WITH ctePayments As
(
SELECT
OrderId,
PaymentDate,
Terms,
PrevBal,
Payment,
Balance,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
FROM
OrderPymts
)
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
INNER JOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
; ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]
Alternatively, you could use a correlated sub-query:
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
CROSS JOIN
(
SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
FROM OrderPymts
WHERE OrderPymts.OrderId = O.OrderId
ORDER BY PaymentDate DESC
) As P
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.
modified 20-Jan-23 18:43pm.
|
|
|
|
|
On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I needed it to show the last payment made on an invoice. But thanks for your advice.
|
|
|
|
|
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.