Case insensitive search with Oracle

Classic Servoy
1

In Oracle 10g R2 i can do case-insensitive querys by setting two variables :

  • NLS_COMP=LINGUISTIC
  • NLS_SORT=BINARY_CI

if i set these ( in database and/or environment ) i can do a case - insensitive query :

SQL> select * from testcase where k1 = ‘ABCDF’;

K1

AbCdF

SQL> select * from testcase where k1 like ‘ABC%’;

K1

AbCdF

When i start a default (Oracle) sqlplus session this works fine.
Servoy does not seem to take notice of (or overrules) these settings.

How can i make sure that Servoy makes use of these settings so i can use case insensitive search for Servoy running on Oracle ??

2

I don’t think Servoy overrides anything. It should be a matter of the JDBC driver.

3

You could also use the # in front of your search criteria, that should make a Servoy find being case insensitive.

4

Thanks for your reply.

I know how to use the # character, but i want to make as much use of the
Oracle database features as i can.
Also, when i can use this, the user (or a method) does not have to add the # sign.
This should be transparent to an application (it is in Sqlplus)

I like to split the database and application functions as much as possible.

Oracle will also use a more sensible/better performing execution plan

Maybe Servoy can answer this one ??

Regards,

Hans

5

Have a look at the statement fired at the database through the admin pages. If you’re not doing anything yet to modify the search, you’ll likely find a statement like "select X from Y where Z = ‘…’

This is send to the DB through JDBC. If Oracle’s JDBC driver then given back the results without looking at your environment settings, it’s not something Servoy can do about.

There might be an Oracle JDBC driver around that does support these environment variables properly.

Paul

6

Oke,

I tested a litle java program :

import java.sql.;
import oracle.jdbc.driver.;

class jdbctest
{
public static void main (String args )
throws SQLException
{

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection
(“jdbc:oracle:thin:@localhost:1521:xe”,“bis1”,“bis1”);

DatabaseMetaData meta = conn.getMetaData ();

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery
(“SELECT k1 from testcase where k1 like ‘ABC%’”);

System.out.println(“Class is SelectFromPer\n”);

System.out.println(“Found row:”);

while (rs.next()) {

String k1text = rs.getString(1);

System.out.print (" K1=" + k1text);
System.out.print(" \n");
}

stmt.close();
conn.close();
}
}

The case-insensitive search did not work with it .

I checked with Oracle and they said that the NLS parameters are not read when using thin jdbc.

So i tryed writing a database logon trigger for the user that connects from this test program and from Servoy :

create or replace trigger set_nls_onlogon
AFTER LOGON ON DATABASE
DECLARE
cmmd1 VARCHAR2(100);
cmmd2 VARCHAR2(100);
BEGIN
cmmd1:=‘ALTER SESSION SET NLS_SORT=BINARY_CI’;
cmmd2:=‘ALTER SESSION SET NLS_COMP=LINGUISTIC’;
if (user in (‘BIS1’,‘BERP’)) then
EXECUTE IMMEDIATE cmmd1;
EXECUTE IMMEDIATE cmmd2;
end if;
END set_nlslogon;

This works fine both with the test program and also when using Servoy !!!

This is a great way to do case insensitive searching in the Servoy-Oralcle environment.

Regards and thanks for the replys.

Hans Nieuwenhuis

7

Nice tip!

Tnx