(JTDS) java.sql.SQLException: Error converting data type nvarchar to numeric

Question

I'm having trouble reading from a view in a SQL Server 2005 database using JTDS and having a serious problem finding a solution.

The query is just selecting everything in the view:

SELECT * FROM encounters_view WHERE patient_id = ?

The exception is being thrown on this line:

rset = selectAllEncountersByPatientId.executeQuery();

The stacktrace is as follows:

java.sql.SQLException: Error converting data type nvarchar to numeric.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:796)
    at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:134)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:483)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
    at package_name.ClassName.method(Db_query_class.java:91)
    at package_name.ClassName.main(Main_class.java:36)

View Data Types:

id (unique(numeric(16,0)),not null)
provId (uniqueid(numeric(16,0)),not null)
patId (uniqueid(numeric(16,0)),not null)
mrn (varchar(20),not null)
visitId (uniqueid(numeric(16,0)),not null)
dttm (datetime, null)
apptType (name(varchar(255)),null)
apptStatus (name(varchar(255)),null)
refProvId (uniqueid(numeric(16,0)),not null)
pay (name(varchar(255)),not null)
zip (char(10),not null)

Any help would be greatly appreciated.

Could you post up your SQL statement and also the table definitions? (The datatypes of the columns is of interest). — Martin Wilson
– Martin Wilson, Commented Oct 12, 2012 at 16:11
I've added the view's datatypes. I can run the query fine in the database...but when I try to fill a result set with it, it bombs. — barrongineer
– barrongineer, Commented Oct 12, 2012 at 16:49

Martin Wilson · Accepted Answer · 2012-10-12 16:11:51Z

4

This is most likely being caused because you are joining on two columns of different data type (one nvarchar the other numeric) - your nvarchar probably contains numbers containing characters that won't convert to decimal (e.g. ',' or '.').

answered Oct 12, 2012 at 16:11

Martin Wilson

3,3861 gold badge26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

barrongineer Over a year ago

I figured out that this was being caused by special characters in the queries parameter. The testers were prefixing id's with a '!'.

Collectives™ on Stack Overflow

(JTDS) java.sql.SQLException: Error converting data type nvarchar to numeric

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related