topic Re: SQL Query Case Sensitivity Issue in Discussions

SQL Query Case Sensitivity Issue

Arthur — Fri, 12 Jul 2024 10:12:51 GMT

I'm using JSL to retrieve data from a MySQL database. However, I've encountered an issue where the filtering conditions in my SQL query are case-insensitive by default. This means that the results returned are the same regardless of the case of the filter value.

Here are the two scripts that produce the same results:

Script 1:

New SQL Query(
Version( 130 ),
Connection(
"ODBC:DATABASE=my_database;DSN=my_dsn;PORT=3306;PWD=my_password;SERVER=localhost;UID=my_user;"
),
QueryName( "test_data" ),
Select(
Column( "Column1", "t1" ),
Column( "Column2", "t1" ),
Column( "Column3", "t1" )
),
From( Table( "test_data", Alias( "t1" ) ) ),
Where(
Not Or(
In List(
Column( "Column1", "t1" ),
{"A"},
UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
)
)
)
) << Run;

Script 2:

New SQL Query(
Version( 130 ),
Connection(
"ODBC:DATABASE=my_database;DSN=my_dsn;PORT=3306;PWD=my_password;SERVER=localhost;UID=my_user;"
),
QueryName( "test_data" ),
Select(
Column( "Column1", "t1" ),
Column( "Column2", "t1" ),
Column( "Column3", "t1" )
),
From( Table( "test_data", Alias( "t1" ) ) ),
Where(
Not Or(
In List(
Column( "Column1", "t1" ),
{"a"},
UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
)
)
)
) << Run;

How can I modify the New SQL Query function in my JSL scripts to make the filtering conditions case-sensitive?

Thank you for your help!

Re: SQL Query Case Sensitivity Issue

jthi — Fri, 12 Jul 2024 10:51:24 GMT

Someone from JMP most likely knows better, but have you what the SQL query looks like? I think you can see it by converting to Custom SQL

Re: SQL Query Case Sensitivity Issue

jthi — Fri, 12 Jul 2024 11:13:23 GMT

Also how is your database configured? If you run your query in some other application do you get the same results? I'm not familiar with MySQL but maybe those are by default case insensitive https://dev.mysql.com/doc/refman/8.4/en/case-sensitivity.html

Re: SQL Query Case Sensitivity Issue

Arthur — Sat, 13 Jul 2024 09:02:39 GMT

The SQL statement is similar to this:

SELECT t1.`Column1`, t1.`Column2`, t1.`Column3`
FROM test_data  t1 
WHERE  (  NOT  (  (  ( t1.`Column1` IN  ( 'A' )  )  )  )  ) ;

I know that adding the BINARY keyword in SQL can enforce case sensitivity, for example:

SELECT `Column1`, `Column2`, `Column3`
FROM `test_data` AS `t1`
WHERE BINARY `Column1` NOT IN ('A');

And using the open Database function, I can execute SQL statements directly. However, I am particularly interested in whether the New SQL Query function in JSL can achieve the same case-sensitive effect.

Thank you for your assistance!

Re: SQL Query Case Sensitivity Issue

jthi — Sun, 14 Jul 2024 15:45:19 GMT

I think you might have to add custom expression to handle situations like that

Re: SQL Query Case Sensitivity Issue

Arthur — Mon, 15 Jul 2024 09:17:47 GMT

Got it, that would be similar to referencing SQL statements in open Database function. Thanks!