QUICK SEARCH: searching result is case sensitive {HELP}

Applications Grid / Reports
#1

Hi guys,

some how after update the SC now the Quick Search result showing the CASE SENSITIVE,
is there any way to fix this bug…

I’m using SC ver: 5.02.0040

thanks in advance
ahmer

#2

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Hello Ahmer,

I was unable to run into this error on my Server. It shouldn’t happen at all, as Scriptcase only queries your RDBMS (for instance, "SELECT * FROM myTable where someField like ‘%someValue%’). Make sure that your database collation is not case sensitive, and let me know if your collation is Case sensitive .

regards,
Bartho

#3

Re: QUICK SEARCH: searching result is case sensitive {HELP}

thanks bartho,

you are right, it is case-sensitive,
would you please guide me how to fix this…

thanks in advance
ahmer

#4

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Hello Ahmer,

Which RDBMS are you working with?

regards,
Bartho

#5

Re: QUICK SEARCH: searching result is case sensitive {HELP}

I’m using MySQL ver. 5.0.51a

#6

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Hello,

If you are using a DB Client (e.j. phpmyadmin) select your database and under the Settings / Operations (I’m not sure as my phpmyadmin is in portuguese) you can set a collation of your choice to the whole database.

If you aren’t, then you can alter the DB collation by the following SQL Statement:

ALTER DATABASE myDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci

Note that on this example I’m setting the collation to latin1_swedish_ci, you can set to any other collation.

regards,
Bernhard Bernsmann

#7

Re: QUICK SEARCH: searching result is case sensitive {HELP}

thanks bartho

I did change, but still getting result with case sensitive… :frowning:

#8

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Hello,

That is strange. Have you tried querying your table directly from your RDBMS? Is it case sensitive or insensitive?

regards,
Bernhard Bernsmann

#9

Re: QUICK SEARCH: searching result is case sensitive {HELP}

thanks bartho,

I tested as you said in phpmyadmin, and its same problem its still searching case sensitive.
please advice

regards
ahmer

#10

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Hello,

Could you try querying a upper case record using lower case query on phpMyAdmin?

regards,
Bernhard Bernsmann

#11

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Thanks Bartho

its saved in upper case, and its not searching in lower case, but it search is in upper case only…
please advice…

thanks
ahmer

#12

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Ahmer,

In that case your collation it still is CASE SENSITIVE. Make sure to select another collation that is Case Insensitive, try a few queries around your table to make sure that your DB is Case Insensitive.

regards,
Bernhard Bernsmann

#13

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Ahmer

Have you tried removing the Collation off the table that is now case sensitive ?

Paul

#14

Re: QUICK SEARCH: searching result is case sensitive {HELP}

I tried many time to change the collation but still, i need USA collation
what should I select on phpmyadmin
any idea
thanks in advance

#15

Re: QUICK SEARCH: searching result is case sensitive {HELP}

I’ve same problem. How ever from my phpmyadmin setup, is case insensitive.

#16

Re: QUICK SEARCH: searching result is case sensitive {HELP}

Try This :

http://www.scriptcase.net/forum_en_us/index.php?topic=6614.msg17114#msg17114

#17

Hate to resurrect an old thread (again), but i ran into the same issue and developed a workaround.

In my case, I wanted to be able to search by two text fields (sender and recipient) those two fields were lookups to my “users” table.
So the database values were integers,
In scriptcase the fields were auto-complete text and I was using a lookup in Scriptcase to fetch the names from a different database connection.
The lookup name fields in the database were set to case insensitive, and I was able to perform a case-insensitive search in phpMyadmin, so I knew the issue was with the programming in scriptcase.
I was able to search without any issues with case sensitivity in the advanced search, but quick search would only return values if they were all caps like they are in the Database.

So here is what I did as a workaround, and it seems to do the job.

Note: I’d recommend creating a new grid, only because the field settings in scriptcase for existing grid will get messed up once you start messing with the SQL and changing the selected fields. at the very least, I’d backup the application if you intend on modifying the existing app.

In the SQL section of the grid application:
SELECT
database1.table1.message_id,
database1.table1.recipient_id,
database1.table1.sender_id,
database1.table1.subject,
database1.table1.body,
database1.table1.message_sent_date,
database1.table1.message_read_date,
CONCAT(rec.last_name, ', ', rec.first_name) as rec_name,
CONCAT(sen.last_name, ', ', sen.first_name) as sen_name
FROM
database1.table1
LEFT JOIN database2.employee_info AS rec ON database1.table1.recipient_id = rec.id
LEFT JOIN database2.employee_info AS sen ON database1.table1.sender_id = sen.id

This created two additional columns in my grid with the values that I wanted to be able to search by.
It’s slightly more cumbersome in the original SQL code, but now I don’t have any field lookups on the columns in my grid.

I then hid the recipient_id and sender_id columns, and displayed the rec_name and sen_name fields in their place.
I Included the new rec_name and sen_name fields in the “search fields” and removed the recipient_id and sender_id fields, and like magic, the quick search now works using upper or lower case searches.

Obviously, if you are planning to use an application link to a form application from the grid application, you should just use the scriptcase field lookups in the form application. I only use this workaround for my grid application. What I did in my form application to get the autocomplete fields to work regardless of case, was to set the field to capitalize all letters which is good anyway to keep the database data consistent.

If anyone more knowledgeable see this as a bad idea, or has a better way to accomplish the same thing, please let me know. I’m all ears, but couldn’t find any solutions on the forums or in the manual, so this is what I came up with this workaround. Seems to work well, but I am always looking to learn more.