Padding using Type 4 Drivers

Hope someone can advise me (a humble Java Programmer) of a problem I
am having.
We have a char(8) field in a MS SQL Server 7 DB, some of the data is
only 7 chars though.
We have recently upgraded to MS SQL Srever 2000.
The problem is, the JDBC queries now return space padded.
so that "1234567" comes back as "12345678 ".
They didn't do this before.
I understand there is an ODBC configuration, but as we are using
type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
direct to the DB.
Does anybody know what configuration a SQL Server DB has which effects
the ANSI padding when using a type-4 JDBC driver?
I am clueless on this I can tell you!.
| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Padding using Type 4 Drivers
| Date: 16 Nov 2004 08:40:55 -0800
| Organization: http://groups.google.com
| Lines: 19
| Message-ID: <b47276f6.0411160840.6acfded0@.posting.google.com >
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100623255 12885 127.0.0.1 (16 Nov 2004
16:40:55 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 16 Nov 2004 16:40:55 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!newsfeed.icl.net!proxad.ne t!216.239.36.134.MISMATCH!
postnews.google.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6475
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hope someone can advise me (a humble Java Programmer) of a problem I
| am having.
|
| We have a char(8) field in a MS SQL Server 7 DB, some of the data is
| only 7 chars though.
| We have recently upgraded to MS SQL Srever 2000.
|
| The problem is, the JDBC queries now return space padded.
| so that "1234567" comes back as "12345678 ".
| They didn't do this before.
|
| I understand there is an ODBC configuration, but as we are using
| type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
| direct to the DB.
|
| Does anybody know what configuration a SQL Server DB has which effects
| the ANSI padding when using a type-4 JDBC driver?
|
| I am clueless on this I can tell you!.
|
Hello,
It is possible that your SQL Server 7 was using a different ANSI_PADDING
value than what you have set in SQL Server 2000.
Whenever you make a connection to SQL Server, you can specify the ANSI
settings that you require. If you do not specify these explicitly, then
your connection will receive a default set of ANSI settings based on the
configuration in SQL Server. You can use the Security Audit -> Audit Login
event in SQL Profiler to observe the ANSI settings used when your
connection is first made. Your Profiler output may look something like
this:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
These default settings are documented in the SQL Server Books Online under
the topic "SET"
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
can also run DBCC USEROPTIONS from within your connection to see this
information.
Are you using the Microsoft JDBC driver or a third-party driver?.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
> --
> Hello,
> It is possible that your SQL Server 7 was using a different ANSI_PADDING
> value than what you have set in SQL Server 2000.
> Whenever you make a connection to SQL Server, you can specify the ANSI
> settings that you require. If you do not specify these explicitly, then
> your connection will receive a default set of ANSI settings based on the
> configuration in SQL Server. You can use the Security Audit -> Audit Login
> event in SQL Profiler to observe the ANSI settings used when your
> connection is first made. Your Profiler output may look something like
> this:
> -- network protocol: TCP/IP
> set quoted_identifier on
> set implicit_transactions off
> set cursor_close_on_commit off
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> set language us_english
> set dateformat mdy
> set datefirst 7
> These default settings are documented in the SQL Server Books Online under
> the topic "SET"
> (http://msdn.microsoft.com/library/de...-us/tsqlref/ts
> _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
> can also run DBCC USEROPTIONS from within your connection to see this
> information.
> Are you using the Microsoft JDBC driver or a third-party driver?.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
Thanks very much for the information.
We are indeed using a third-party driver as we are not aware of a
Microsoft Type 4 driver which will connect to sql server 7.
We have performed the tests using a weblogic driver and jtds, both
having the same results. The DBA insist that the sql server 7 and sql
server 2000 are configured the same..... but I have my doubts!.
|||I ran the suggested DBCC OPTIONS command for the box that has NO padding.
This is what I get:
C:\temp\JDBC>java -cp .;jtds-0.9.jar ConnectInfo staging
Running against staging
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
oes that make any sense? I get no padding, but padding is set for the connection.
|||| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: Padding using Type 4 Drivers
| Date: 17 Nov 2004 01:38:32 -0800
| Organization: http://groups.google.com
| Lines: 54
| Message-ID: <b47276f6.0411170138.2d960a00@.posting.google.com >
| References: <b47276f6.0411160840.6acfded0@.posting.google.com >
<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100684312 31457 127.0.0.1 (17 Nov 2004
09:38:32 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 17 Nov 2004 09:38:32 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftn gxa06.phx.gbl!TK2MSFTNGP08
.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!newsfeed.yul.equant .net!newsrea
d.com!newsprint.newsread.com!postnews.google.com!n ot-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6480
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message
news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
| > --
| > Hello,
| >
| > It is possible that your SQL Server 7 was using a different
ANSI_PADDING
| > value than what you have set in SQL Server 2000.
| >
| > Whenever you make a connection to SQL Server, you can specify the ANSI
| > settings that you require. If you do not specify these explicitly,
then
| > your connection will receive a default set of ANSI settings based on
the
| > configuration in SQL Server. You can use the Security Audit -> Audit
Login
| > event in SQL Profiler to observe the ANSI settings used when your
| > connection is first made. Your Profiler output may look something like
| > this:
| >
| > -- network protocol: TCP/IP
| > set quoted_identifier on
| > set implicit_transactions off
| > set cursor_close_on_commit off
| > set ansi_warnings on
| > set ansi_padding on
| > set ansi_nulls on
| > set concat_null_yields_null on
| > set language us_english
| > set dateformat mdy
| > set datefirst 7
| >
| > These default settings are documented in the SQL Server Books Online
under
| > the topic "SET"
| >
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
| > _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements".
You
| > can also run DBCC USEROPTIONS from within your connection to see this
| > information.
| >
| > Are you using the Microsoft JDBC driver or a third-party driver?.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
|
| Thanks very much for the information.
|
| We are indeed using a third-party driver as we are not aware of a
| Microsoft Type 4 driver which will connect to sql server 7.
|
| We have performed the tests using a weblogic driver and jtds, both
| having the same results. The DBA insist that the sql server 7 and sql
| server 2000 are configured the same..... but I have my doubts!.
|
In an ealier post, you mentioned upgrading from SQL 7 to SQL 2000. Are you
seeing the same behavior with both database versions?
I did some more research for this question, and here is what I found. For
SQL Server 2000, the ANSI_PADDING setting is applied when the table is
created. If ANSI_PADDING was turned off when the table was created, then
the char(8) column would return the data with trailing spaces removed
(assuming the column allows nulls). If ANSI_PADDING was on when the table
was created, then the char(8) column would return the trailing space. This
behavior can be observed in Query Analyzer:
SET ANSI_PADDING
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_2uw7.asp
In another test, I used the Microsoft JDBC driver to query the table that
was created with ANSI_PADDING turned off. I still observe the trailing
spaces in this scenario. I'm not sure why this is the case, but the
behavior appears to be consistent with the other JDBC drivers you have
tested.
All things aside, you should consider using a variable-length data type for
your column definitions if you are concerned with trailing spaces in your
table.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.