mssql case sensitivity

[Former Member]

mssql is case sensitive..

while selecting...i got problem...

SELECT * FROM YEMM_OUTMAIL INNER JOIN YEMM_OUTMAIL_ACK ON YEMM_OUTMAIL.EMAILID = YEMM_OUTMAIL_ACK.EMAILID WHERE YEMM_OUTMAIL.TIMEIN>=1177257600937 AND LOWER(YEMM_OUTMAIL_ACK.RECEIPIENT)='johnson@yahoo.com'

i can run in sql analyzer..but not in my coding...

what goes wrong

Intermediate-SQL syntax error: UPPER or LOWER function not allowed

#

so the collection return to node is blank...

i cannot see anything in my table

[Former Member · Accepted solution]

yzme,

Looks like you must use escaped function syntax with your JDBC driver (the error you see comes from driver but not database).

Check http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame11.html.

For your particular case it looks like you need:

SELECT * 
FROM 
  YEMM_OUTMAIL INNER JOIN YEMM_OUTMAIL_ACK 
    ON 
    YEMM_OUTMAIL.EMAILID = YEMM_OUTMAIL_ACK.EMAILID

WHERE 
  YEMM_OUTMAIL.TIMEIN>=1177257600937 AND 
  {fn LCASE(YEMM_OUTMAIL_ACK.RECEIPIENT)}
    ='johnson@yahoo.com'

Valery Silaev

SaM Solutions

http://www.sam-solutions.net

Message was edited by:

Valery Silaev

[Former Member · Accepted solution]

SQL syntax error: an illegal character "{" has been found in the statement text

#

[Former Member · Accepted solution]

yzme,

then try your original syntax but use LCASE instead of LOWER function.

Btw, Are you using OpenSQL driver with default datasource or you are using some vendor-specific driver?

Valery Silaev

SaM Solutions

http://www.sam-solutions.net

[Former Member · Accepted solution]

yup, i already change to {fn LCASE}

Microsoft SQL 2000 default during installation

the problem now is "{" "}"

Intermediate-SQL syntax error: UPPER or LOWER function not allowed

how do i check if i am using vendor specific or OpenSQL driver...

it is working in the sql analyzer....

but no in my coding...

pls advice...

Message was edited by:

yzme yzme

[Former Member · Accepted solution]

yzme,

It is definitely OpenSQL driver with its limitations.

Define separate DataSource / Driver in VisualAdministrator. Use this blog post as guideline -- https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/5953. [original link is broken] [original link is broken] [original link is broken] [original link is broken] You can download driver for MSSQL 2000 from microsoft site.

VS

[Former Member · Accepted solution]

at the moment i not going to change any setting of my db....

you think my db setting have its limitation...

and you told me i am using OpenSQL...

<b>i can run the statement in sql analyzer but no in my coding......</b>

<b> SQL syntax error: an illegal character "{" has been found in the statement text

</b>

[Former Member · Accepted solution]

yzme,

I'm not suggesting you to change your DB settings, I only suggest you to define additional combo of DataSource/Driver that points to the very same database, and use this new DataSource from application.

<i>i can run the statement in sql analyzer but no in my coding......</i>

Your statement runs successfully in analyzer because it is valid Native SQL (MS SQL). However, when you run it from Java code, your SQL is preprocessed by dumb OpenSQL driver, that decides to decline statement.

<i>SQL syntax error: an illegal character "{" has been found in the statement text</i>

OpenSQL driver is not JDBC-compliant while it rejects absolutely valid escaped function call syntax.

As a workaround, you may create view in you database that uses LOWER(email) for column definition. Then use this view in select statement instead of table (and do not use functions in select, obviously).

Valery Silaev

SaM Solutions

http://www.sam-solutions.net

[Former Member · Accepted solution]

1)view cannot parse in parameter in runtime...it is direct linking right....meaning..static select statement where i cant change the select...from..where conditions ....??

create view MyView as

select Column1, Column2 ...

from MyOtherDatabase.dbo.MyTable

after i create this view...i cannot parse in anything...in my where statement...it is just to linking tables together ...right...

2)the only way to do this is stored procedured if i want to pass in parameter in the runtime...?

3)what if to eliminate the case sensitive

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CI_AS' WHERE name = '<column name>' AND id = object_id('<table name>')

Message was edited by:

yzme yzme

[Former Member · Accepted solution]

yzme,

You can freely use parameters with SQL queries over view in a same way as wiyh tables:

CREATE VIEW MAIL_LOOKUP AS
SELECT 
  Col1, Col2, 
  LOWER(YEMM_OUTMAIL_ACK.RECEIPIENT) AS L_RECEIPIENT, 
  ..., 
  ColN
FROM 
  YEMM_OUTMAIL INNER JOIN YEMM_OUTMAIL_ACK 
    ON 
    YEMM_OUTMAIL.EMAILID = YEMM_OUTMAIL_ACK.EMAILID 

... and later

SELECT * FROM MAIL_LOOKUP
WHERE 
  YEMM_OUTMAIL.TIMEIN>=1177257600937 AND
  L_RECEIPIENT='johnson@yahoo.com'

VS

[Former Member · Accepted solution]

u mean like this

CREATE VIEW MAIL_LOOKUP(@RECEIPIENT,@SUBJECT, @MAILDATE) AS

SELECT

Col1, Col2,

LOWER(YEMM_OUTMAIL_ACK.RECEIPIENT) AS L_RECEIPIENT,

...,

ColN

FROM

YEMM_OUTMAIL INNER JOIN YEMM_OUTMAIL_ACK

ON

YEMM_OUTMAIL.EMAILID = YEMM_OUTMAIL_ACK.EMAILID

TO THE VIEWS .....I though only stored procedure can do this....

[Former Member]

Hai,

Execute the query using prepared statement

String sqlQuery=Select * from EMP Where UPPER(Id) = UPPER(?)

PreparedStatement readAl = objConnect.prepareStatement(sqlQuery);

readAl.setString("data");

readAl.execute();

Thanks & Regards,

Naga

[Former Member]

function not allowed....same problem whether it is a preparedStatement or not..

[Former Member]

yzme,

any success with view?

Also, I highly recommend you to create separate driver/dataSource. This is one-time only operation, on other hand OpenSQL will bug you during whole application development.

VS