cancel
Showing results for 
Search instead for 
Did you mean: 

mssql case sensitivity

Former Member
0 Kudos

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

View Entire Topic
Former Member
0 Kudos

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
0 Kudos

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

#

Former Member
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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....