Query results different from Management Studio versus Weblogic JDBC SQL Server Driver Type 4

• Summer90

  SSC-Dedicated

  Points: 32891

  More actions

  July 16, 2015 at 11:52 am

  #398754

  We have a SQL Server 2008R2 SP2 on Win2008R2. When we run the below query in Mgt Studio it returns results. When it is a prepared statement running via the application using the weblogic.jdbc.sqlserver.SQLServerDriver type 4 non-XA driver it doesn't return any results. The odd thing is when I search about this type 4 driver it doesn't say it supports SQL Server 2008 or R2 but I cannot find any newer driver..

  I have done some searching and I cannot find out anything about any JDBC 4 type driver issues with query results. Does anyone have any suggestions?

  select EmpID

  , XID

  from tablemane

  where XID like 'PSCAL' + '[0-9][0-9]'

  or XID like 'PSCAL' + 'c[1-9]'

  or XID like 'PSCAL' + '[0-9][0-9][0-9]'

  or XID like 'PSCAL' + '[0-9]c[1-9]'

  order by XID;

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 16, 2015 at 12:10 pm

  #1813097

  Markus (7/16/2015)

  ---

  We have a SQL Server 2008R2 SP2 on Win2008R2. When we run the below query in Mgt Studio it returns results. When it is a prepared statement running via the application using the weblogic.jdbc.sqlserver.SQLServerDriver type 4 non-XA driver it doesn't return any results. The odd thing is when I search about this type 4 driver it doesn't say it supports SQL Server 2008 or R2 but I cannot find any newer driver..

  I have done some searching and I cannot find out anything about any JDBC 4 type driver issues with query results. Does anyone have any suggestions?

  select EmpID

  , XID

  from tablemane

  where XID like 'PSCAL' + '[0-9][0-9]'

  or XID like 'PSCAL' + 'c[1-9]'

  or XID like 'PSCAL' + '[0-9][0-9][0-9]'

  or XID like 'PSCAL' + '[0-9]c[1-9]'

  order by XID;

  This:

  select

  EmpID,

  XID

  from

  tablemane

  where

  XID like 'PSCAL' + '[0-9][0-9]'

  or XID like 'PSCAL' + 'c[1-9]'

  or XID like 'PSCAL' + '[0-9][0-9][0-9]'

  or XID like 'PSCAL' + '[0-9]c[1-9]'

  order by

  XID;

  can be written like this:

  select

  EmpID,

  XID

  from

  tablemane

  where

  XID like 'PSCAL[0-9][0-9]'

  or XID like 'PSCAL[1-9]'

  or XID like 'PSCAL[0-9][0-9][0-9]'

  or XID like 'PSCAL[0-9]c[1-9]'

  order by

  XID;

  Try the second version from your application.

• Summer90

  SSC-Dedicated

  Points: 32891

  More actions

  July 17, 2015 at 6:45 am

  #1813262

  We figured out the issue and fixed it. Thanks for your response.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 17, 2015 at 9:00 am

  #1813324

  Markus (7/17/2015)

  ---

  We figured out the issue and fixed it. Thanks for your response.

  How did you fix the issue? Others may have the same problem and you solution may save them time solving their issue.

• Summer90

  SSC-Dedicated

  Points: 32891

  More actions

  July 20, 2015 at 5:19 am

  #1813618

  The query was being used in two different sql servers. One of the tables had the XID column as CHAR the other VARCHAR. It should have had the query with a % at the end of each statement. Self inflicted wound.