aspose file tools*
The moose likes JDBC and the fly likes Data type mismatch need to convert string to a number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Data type mismatch need to convert string to a number" Watch "Data type mismatch need to convert string to a number" New topic
Author

Data type mismatch need to convert string to a number

k Ratliff
Greenhorn

Joined: Jan 05, 2012
Posts: 6

First, I want to apologize in advance if I am posting in the wrong area or if this topic has been address. This is my first time posting to a forum and I did search the forum. That said, here is my coding dilemma.

The field (custID) I am working with in the access database is a number. In my code, I retrieve the value from the custID field and then convert it to a String so I can assign the value to a textField. My problem is that I am trying to use the same custID value to retrieve values from a different table. I have attempted to get the vale from the textField and convert it back to a number so I can query the second table. I have been working on this code for three days now and I can't figure out why I continue to get the error data mismatch in criteria expression.

Here is my code I am working with. I am very new to Java. Any suggestions will be welcomed.



Thanks.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Hello,

use parameters (binds) in your query:


setInt( index, value ) sets the designated parameter to the given vallue. Index is a number of ? parameter in the query.

Use parametrized queries whenever possible, that usually give you better performance (but not always - there are seldom exception to this "rule of thumb").
For the database the query is like source code, the database must first parse it (compile the source code of the query and find the best execution plan).
In most modern databases parametrized query is parsed only once, then stored in the memory and reused on consecutive calls to PrepareStatement without parsing
(only with binding parameters to placeholders).
Hardcoded queries like "SELECT ... WHERE custId = 1", "SELECT ... WHERE custId = 2" etc. are completely different queries for the database,
and the database must parse each of them wasting cpu time and memory.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
and also - don't sue "SELECT *", name your fields explicitly in the select statement "SELECT field1, field2 ... FROM ...".
SELECT * tells to the database "give me the WHOLE row" (all fields). The database don't know that you just want only field2 and field4,
you told to it "select *", so it retrieves and sends you THE WHOLE ROW (even if the table has 3000 fields) from the disk and sends you over the wire.
There are also some optimizations that database can perform when you explicitly show fields you want to get,
for example if you execute query: SELECCT field1, field3 FROM... and there is a composite index on field1 + field3,
then database can retrieve these values directly from this index, not touching the table at all,
but if you say to it "SELECT *" - no way, it must read the table.
k Ratliff
Greenhorn

Joined: Jan 05, 2012
Posts: 6

Ireneusz Kordal wrote:Hello,

use parameters (binds) in your query:


setInt( index, value ) sets the designated parameter to the given vallue. Index is a number of ? parameter in the query.


Ok, so am I literally supposed to put a ? in my query or is it supposed to be replaced with a specific parameter. Because I changed my query to exactly what you typed and added the setInt method, but I got the following error: Syntax error in string query expression 'CustID = Pa_RaM000'. I guess I am not understanding what you mean by use a parameter in your query. Does it have something to do with the fact that I am using MS Access? I did change my queries to select the specific fields. Thank you for the suggestion.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

(sigh...) Yes, you're supposed to do what Ireneusz suggested. Only it would have helped if he hadn't made a small mistake and left an extra quote at the end of the SQL statement. Can I leave you to look for that by yourself?
k Ratliff
Greenhorn

Joined: Jan 05, 2012
Posts: 6

Thank you. It worked like a charm. I should have caught that single quote.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Data type mismatch need to convert string to a number