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.

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob