aspose file tools*
The moose likes JDBC and the fly likes Losing Leading Zero on Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Losing Leading Zero on Insert" Watch "Losing Leading Zero on Insert" New topic
Author

Losing Leading Zero on Insert

Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
I'm trying to store telephone numbers in SQL Server Express 2012 as nvarchar(15) ... which my reading suggests should be simple enough. It was easy to test my code from the browser into the data structure where the "numbers" (as String) are temporarily stored as they come in, and then back again to be displayed in the browser. No problem there ... leading zeros are displayed. Next step in the process is to send the data structure to load the data into the data base. When I look at the data in SQL Server Management Studio ... no leading zeros.

My SQL (via JDBC) is simple enough:



As indicated, the SQL works ... i.e. I get the data into the table. The only problem is that telephone numbers like 085774013 are in the table as 85774013 ... i.e. with no leading zero.

I can't use padding solutions for display because telephone numbers here where I am can be of different length.


Correlation does not prove causality.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

My guess is that the string is being inadvertently converted to a number either on the way into the database, or possibly even on the way from the database.

You need to inspect every step on these paths. If you want some help, you'll need to TellTheDetails - show all relevant parts of your code.
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
Martin Vajsar wrote:My guess is that the string is being inadvertently converted to a number either on the way into the database, or possibly even on the way from the database.

You need to inspect every step on these paths. If you want some help, you'll need to TellTheDetails - show all relevant parts of your code.


I don't know what other details there are to tell. I do an insert from a String in Java, where I know that there is a leading zero (as per test description above), using a simple insert statement into the database (shown above). The String is inserted to nvarchar(15), not null. I then use SQL Server Management Studio to look at the data in the table. I don't know how to look at what happens between the executeUpdate() call in my Java code and the result in the table. I have no code between those two points.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The details to tell:

1) the insert statement, all of it. If you're using a PreparedStatement (and you should), show us the calls to set the parameters too.

2) show us the definition of the table as it exists in the database. I don't know SQL server well, but it should be something like exec sp_columns MyTable. Copy the output of this statement here.

3) the data you're storing in the table are entered by a user in an application, right? I'd suggest writing these data to a log file, to be able to independently inspect them.
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
Seems like more than you need ... but here goes. This is the first two pieces and I'll work on getting the database def.





Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Here is the culprit:

The phone is a String, but you haven't enclosed it in the quotes. It is therefore converted to number for execution of the statement, and then to VARCHAR for storage. This is why you should avoid implicit conversions. Using PreparedStatement would help you to pass the parameters to the database using the desired type (because you need to use proper setXxx method, in this case setString instead of setInt).

Also, it is a good practice to name the columns you're inserting into.

Edit: if the phone number contained non-numeric characters, you'd get an error while executing the statement. That would make the problem obvious, of course.
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
Martin Vajsar wrote:Here is the culprit:

The phone is a String, but you haven't enclosed it in the quotes. It is therefore converted to number for execution of the statement, and then to VARCHAR for storage. This is why you should avoid implicit conversions. Using PreparedStatement would help you to pass the parameters to the database using the desired type (because you need to use proper setXxx method, in this case setString instead of setInt).

Also, it is a good practice to name the columns you're inserting into.

Edit: if the phone number contained non-numeric characters, you'd get an error while executing the statement. That would make the problem obvious, of course.


Thanks. I'd been through that once but forgot to do it when I ran into the leading zero problem and switched phone from int to string ... just plain forgot to look at that.
Also, good call on naming columns being inserted into ... been thinking about that but hadn't gotten around to it yet .... just have a habit of keeping the code light. Not always the best way to go, but often good.
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
Might as well offer some additional discussion, since I'm thinking about not naming columns being inserted into. It seems to me that a straight insert of data must be faster than named column inserts. I figure this must be true because one is checking each item of data against column names and the other is not. I don't know how big the difference is, but I like speed.

Now, the argument in favor of naming columns has to do with maintenance. If you change things, it's easier to keep track of it if your insert code matches column names. Naming columns means you don't have to worry about changing the order of entry because you've changed the table.

My thoughts: In parts of the application I'm thinking about here, all of the mucking around to get every item of data for a full table entry is done using a Java data structure (an object for the purpose). So, if I have to change things in the logic of the application to deal with collecting the data, it's already being done in a way that allows entry to be done in any order. When entering data into a modified database table, I only have to modify the insert statement ... which I'd have to do whether the columns are named or not. Then, one test (it's just wrong not to have at least one) and it's over!

I think naming columns is a good general rule of thumb. But doesn't this approach seem to make it redundant?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

You're actually paying big performance penalty with your current code by not using PreparedStatement. PreparedStatements can usually be reused even across database sessions, and they prevent converting each parameter to string (and back) for each execution. Also, they protect you from SQL injection attacks, which is not directly related to performance, but is even more important. See our FAQ page on PreparedStatement.

Now, every statement to be executed needs to be parsed. Parsing up columns that are part of the INSERT statement is just a bit of what database does with the statement; it must for example check that parameters that were passed to it are compatible with the datatype anyway. I believe that any performance gains from not naming column list (if they exist at all) are actually not measurable - if you use PreparedStatement that is called often, you actually take that penalty only once per many executions of the statement. If the statement is not called often, any performance improvements in it are not to be felt in the application.
Roger F. Gay
Ranch Hand

Joined: Feb 16, 2007
Posts: 399
OK, I'm a little lost. Prepared Statements across sessions? How does that differ from your answer here.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The sharing of statements across sessions mentioned in this thread concerns the database side. When the database parses a statement, it caches it. When another statement comes in, the DB first looks whether it already has the same exact statement in the cache (the SQL text of the statement is the key). If it has, it skips the rest of the parse and uses the cached version. So even if the cached statement was created in one session, via the cache it can be reused in another session. (Sometimes this might not be true: select * from table means different things in two sessions if these sessions are held by two different users, each of which would hold its own copy of the table. But the DB handles these situations and uses the cached statement where possible.)

The situation in the client is different: when you prepare a statement, the PreparedStatement eventually ensures the statement is parsed and holds a "handle" to the parsed statement. When you execute it for the second and subsequent time, the statement parsing (including the search in the database) is completely skipped and the statement is directly executed using the handle. The handle is local to the connection, though - you don't want to be able to use a statement that was created on a connection that was meanwhile closed. However, when the statement is prepared or executed for the first time, the full parse in the database is skipped if identical statement is already cached. Moreover, the connection sometimes may keep its own, local-side cache of prepared statements (complete with the "handles"), as I've also already mentioned.

(My experience comes from Oracle, but I believe this is more or less the same in MS SQL Server. Also please let me know if there is still something unclear.)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Losing Leading Zero on Insert