|
|
|
date: Mon, 28 Apr 2008 13:06:40 -0700 (PDT),
group: microsoft.public.sqlserver.jdbcdriver
back
Re: Getting zero terminated string through (both jTDS & MS) jdbc
driver
Hi. Please write a tiny standalone program that demonstrates the problem, eg:
import java.sql.*;
import java.util.*;
public class foo
{
public static void main(String argv[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
props.put("user", "sa");
props.put("password", "secret");
c = d.connect("jdbc:sqlserver://localhost:1433", props );
System.out.println("Driver version is " + c.getMetaData().getDriverVersion() );
System.out.println("DBMS version is " + c.getMetaData().getDatabaseProductVersion() )
;
Statement s = c.createStatement();
s.executeUpdate("CREATE TABLE #foo(bar varchar(25))");
s.executeUpdate("insert #foo values('a')");
s.executeUpdate("insert #foo values('bbbbbbbb')");
s.executeUpdate("insert #foo values('ccccccccccccccccc')");
s.executeUpdate("insert #foo values('dddddddddddddddddddd')");
ResultSet rs = s.executeQuery("select * from #foo");
while (rs.next()) System.out.println( ... whatever you find odd... );
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
dirk_bahle@gmx.de wrote:
> Hi,
>
> I am having a curious problem that I verified now with both the
> MS JDBC Driver 1.2 and jTDS (both show same strange behaviour).
> The Strangeness is that it looks like I am receiving 0 terminated
> strings through the jTDS/MS JDBC driver, except the driver
> does not realize this. This means, a variable length column, eg.:
> varchar(15) is always 15 characters in length and the correct length
> is zero terminated.
>
> I am working with a varchar column and when I use the getString()
> Method of the ResultSet Object I receive Strings with binary zeros.
> I also verified this behaviour with getCharacterStream() function.
>
> The problem shows as follows:
>
> String in Table Received String (In Order of Query)
> ---------------------------------------------------------
> dirk dirk^^^^^^^^^^^
> db db^k^^^^^^^^^^^
> hello hello^^^^^^^^^^
> x x^llo^^^^^^^^^^
> ---------------------------------------------------------
>
> '^' => I use this character to escape binary zero
>
> To me this looks like a fixed length buffer is used and
> the getString() method always returns the full size, even
> though the datatype is varchar(15). I can verify this problem
> only in connection with Microsoft SQL Server 2000. I have
> tested with other Microsoft/Sybase Servers and can not
> re-create this behaviour(?).
>
> Does any one know any setting or config I could try?
> Is this a known bug or is there a workaround? Please
> tell me if you need config infos I'll see if I can get it for
> posting here...
>
> Thanks a lot for any helpful hints,
> Cheers Dirk
date: Mon, 28 Apr 2008 16:22:40 -0700
author: Joe Weinstein
Re: Getting zero terminated string through (both jTDS & MS) jdbc driver
Dirk,
In your post, you said that you only see this with SQL Server 2000. What service pack of SQL Server are you running?
Evan
--------------------
>From: "dirk_bahle@gmx.de"
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Subject: Re: Getting zero terminated string through (both jTDS & MS) jdbc
> driver
>Date: Tue, 29 Apr 2008 11:01:15 -0700 (PDT)
>Organization: http://groups.google.com
>Lines: 18
>Message-ID:
>References:
>
>NNTP-Posting-Host: 80.135.120.80
>Mime-Version: 1.0
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 7bit
>X-Trace: posting.google.com 1209492075 21495 127.0.0.1 (29 Apr 2008 18:01:15 GMT)
>X-Complaints-To: groups-abuse@google.com
>NNTP-Posting-Date: Tue, 29 Apr 2008 18:01:15 +0000 (UTC)
>Complaints-To: groups-abuse@google.com
>Injection-Info: 34g2000hsf.googlegroups.com; posting-host=80.135.120.80;
> posting-account=ZAptOQoAAAAGJ74r-gMGDSAqa8pjvAKm
>User-Agent: G2/1.0
>X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-DE; rv:1.8.1.1)
> Gecko/20061204 Firefox/2.0.0.1,gzip(gfe),gzip(gfe)
>Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS02.phx.gbl!TK2MSFTFEEDS01.phx.gbl!msrtrans!msrn-in!
newshub.sdsu.edu!postnews.google.com!34g2000hsf.googlegroups.com!not-for-mail
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:503
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>
>Hi Joe,
>
>thanks for your suggestion. I wrote a simplified program and verified
>the problem. That was I was able
>to see that the database actually stored the '\0' values in a varchar
>field. I used a port sniffer to make
>sure the database actually sends the '\0' 's in the varchar field.
>
>I was not able to recognize this earlier because the Microsoft Visual
>Studio does not show additional
>binary zeros, if they occur in a varchar field. Now I wonder if this
>is a bug in the Studio or if there is a
>setting/option to "get what you stored" in a varchar field(?).
>
>Anyways, as far as I am concerned my case is resolved, thanks again
>for you suggestion.
>
>Cheers Dirk
>
Evan T. Basalik
This posting is provided AS IS with no warranties, and confers no rights.
date: Tue, 29 Apr 2008 20:19:07 GMT
author: (Evan T. Basalik (MSFT))
Re: Getting zero terminated string through (both jTDS & MS) jdbc driver
Dirk,
Unfortunately, this kind of tool does not currently exist. We have talked about it internally a couple of times, but it has never come to fruition. If you end up
writing something along these lines, please let me know!
Evan
--------------------
>From: "dirk_bahle@gmx.de"
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Subject: Re: Getting zero terminated string through (both jTDS & MS) jdbc
> driver
>Date: Mon, 5 May 2008 03:52:43 -0700 (PDT)
>Organization: http://groups.google.com
>Lines: 21
>Message-ID:
>References:
>
>
>NNTP-Posting-Host: 193.16.163.243
>Mime-Version: 1.0
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 7bit
>X-Trace: posting.google.com 1209984763 10031 127.0.0.1 (5 May 2008 10:52:43 GMT)
>X-Complaints-To: groups-abuse@google.com
>NNTP-Posting-Date: Mon, 5 May 2008 10:52:43 +0000 (UTC)
>Complaints-To: groups-abuse@google.com
>Injection-Info: a70g2000hsh.googlegroups.com; posting-host=193.16.163.243;
> posting-account=ZAptOQoAAAAGJ74r-gMGDSAqa8pjvAKm
>User-Agent: G2/1.0
>X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14)
> Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
>X-HTTP-Via: 1.0 cache.netrtl.com:8080 (squid/2.5.STABLE5)
>Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!msrtrans!msrn-in!newshub.sdsu.edu!postnews.google.com!
a70g2000hsh.googlegroups.com!not-for-mail
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:508
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>
>Hi Evan,
>
>my problem case was running on SP2, Build 3200. We have looked deeper
>into it and it looks like
>the binary zeros '\0' were introduced on a different Server (other
>brand than Microsoft) and then got
>transmitted to MS SQL Server 2000. This went all unnoticed, because
>the Visual Studio did not show
>any '\0' in the varchar output.
>
>In this way the JDBC driver actually helped us finding a (possible)
>problem in the processing chain. I am
>wondering if there is a regression style test in which one could run
>an OLE DB Provider and a JDBC Driver
>'against' each other to see, if and where, they return different
>results. A summary from such a test would be
>interesting for anyone trying to migrate from C# to Java or vice
>versa. Is there such a thing (as a complete
>comparison between the actual JDBC and OLE DB Driver)?
>
>Cheers Dirk
>
Evan T. Basalik
This posting is provided AS IS with no warranties, and confers no rights.
date: Tue, 20 May 2008 01:46:32 GMT
author: (Evan T. Basalik (MSFT))
|
|
