Skip to Content.
Sympa Menu

freetds - Re: [freetds] FreeTDS problems with converting SQL BigInt

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Feroze Daud <khichdo AT yahoo.com>
  • To: Igor Korot <ikorot01 AT gmail.com>, FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] FreeTDS problems with converting SQL BigInt
  • Date: Fri, 20 Sep 2013 16:05:32 -0700 (PDT)

hi! Guys,

Some more information.

1) I changed my program to read the value as a double, then cast it to
bigint. In this case it works.
2) I ran tcpdump and captured a network trace. I looked at it in wireshark,
and noticed that the server is sending the correct value...

0040  c8 31 04 00 10 00 00 49  01 00 81 01 00 00 00 21   .1.....I .......!
0050  00 6c 11 13 00 0d 4d 00  61 00 78 00 49 00 50 00   .l....M. a.x.I.P.
0060  50 00 51 00 75 00 65 00  75 00 65 00 49 00 44 00   P.Q.u.e. u.e.I.D.
0070  d1 05 01 4c 47 07 00 ff  01 00 c1 00 01 00 00 00   ...LG... ........

At offset 76, you can see the value 0x000747c which is exactly the value the
sproc is sending for MaxIPPQueueID ( decimal 477004 ).

I just started reading the MS-TDS spec, and am not an expert in it, so I dont
know what datatype the server is advertizing. But I am wondering why the
library is corrupting this value.

feroze.




________________________________
From: Igor Korot <ikorot01 AT gmail.com>
To: Feroze Daud <khichdo AT yahoo.com>; FreeTDS Development Group
<freetds AT lists.ibiblio.org>
Sent: Thursday, September 19, 2013 12:13 PM
Subject: Re: [freetds] FreeTDS problems with converting SQL BigInt



Hi, Feroze,
What is you Server version?

Thank you.




On Thu, Sep 19, 2013 at 11:29 AM, Feroze Daud <khichdo AT yahoo.com> wrote:

we are using TDS protocol version 8. Should we be using something else?
>
>
>here is our connection string...
>
>--
>
>Initializing processing with connection
>string:DRIVER=FreeTDS;Server=<snip>;UID=<snip>;PWD=<snip>;Database=<snip>;TDS_Version=8.0;Port=1433
>
>--
>
>feroze.
>
>
>
>________________________________
> From: Frediano Ziglio <freddy77 AT gmail.com>
>To: Feroze Daud <khichdo AT yahoo.com>; FreeTDS Development Group
><freetds AT lists.ibiblio.org>
>Sent: Thursday, September 19, 2013 11:09 AM
>
>Subject: Re: [freetds] FreeTDS problems with converting SQL BigInt
>
>
>2013/9/19 Feroze Daud <khichdo AT yahoo.com>:
>> So, I enabled logging for my application. This is what it shows me for the
>> column that is being corrupted.
>>
>> --
>> token.c:526:processing result tokens.  marker is  81(TDS7_RESULT)
>> mem.c:519:tds_free_all_results()
>> token.c:1472:processing TDS7 result. set current_results to tds->res_info
>> read.c:179:tds_get_string: reading 20 from wire to give 10 to client.
>> token.c:1414:tds7_get_data_info:
>>         colname = MaxQueueID (10 bytes)
>>         type = 108 (numeric)
>>         server's type = 108 (numeric)
>>         column_varint_size = 1
>>         column_size = 17 (17 on server)
>>
>> --
>>
>> Does this mean that the TDS stream that is being read by freetds is
>> advertizing the column as numeric?
>>
>
>No, it means the server is returning the number as numeric. Are you
>sure you are using proper protocol version?
>
>> Is there any way to have tdslog print out the value of the column that it
>> is reading from the socket?
>>
>
>If you can read binary yes, just turn network data dump on.
>
>> feroze.
>>
>>
>>
>
>Frediano
>
>>
>> ----- Original Message -----
>> From: James K. Lowden <jklowden AT freetds.org>
>> To: freetds AT lists.ibiblio.org
>> Cc: Feroze Daud <khichdo AT yahoo.com>; FreeTDS Development Group
>> <freetds AT lists.ibiblio.org>
>> Sent: Wednesday, September 18, 2013 6:32 PM
>> Subject: Re: [freetds] FreeTDS problems with converting SQL BigInt
>>
>> On Wed, 18 Sep 2013 13:03:50 -0700 (PDT)
>> Feroze Daud <khichdo AT yahoo.com> wrote:
>>
>>> Thanks. Unfortunately, I need to get the output parameter first, so I
>>> changed the sproc to return the value as a resultset, instead of an
>>> output parameter.
>>
>> As Frediano mentioned, the server sends the output parameter after any
>> ROW packets.  FreeTDS makes the data available to the application as
>> they arrive from the server.
>>
>>> This brings me to the original problem. I am getting garbage values
>>> out of call to getBigInteger.
>>
>> What does src/odbc/unittests/genparams show on your machine regarding
>> the BIGINT type?
>>
>>> Is there any way to enable Freetds logging for applications that dont
>>> go through the odbc driver manager?
>>
>> Cf. TDSDUMP in the user guide.
>>
>> HTH.
>>
>> --jkl
>>
>> P.S.  Top-posting reduces the value of the email archive by making it
>> harder to follow the thread.
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>_______________________________________________
>FreeTDS mailing list
>FreeTDS AT lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>From jklowden AT freetds.org Fri Sep 20 19:13:31 2013
Return-Path: <jklowden AT freetds.org>
X-Original-To: freetds AT lists.ibiblio.org
Delivered-To: freetds AT lists.ibiblio.org
Received: by lists.ibiblio.org (Postfix, from userid 20217)
id D693DE8D0A; Fri, 20 Sep 2013 19:13:31 -0400 (EDT)
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on
mailman1.ibiblio.org
X-Spam-Level:
X-Spam-Status: No, score=-1.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham
version=3.3.1
Received: from asbnvacz-mailrelay01.megapath.net
(asbnvacz-mailrelay01.megapath.net [207.145.128.243])
by lists.ibiblio.org (Postfix) with ESMTP id 63C06E8D02
for <freetds AT lists.ibiblio.org>; Fri, 20 Sep 2013 19:13:21 -0400 (EDT)
Received: from mail6.sea5.speakeasy.net (mail6.sea5.speakeasy.net
[69.17.117.50])
by asbnvacz-mailrelay01.megapath.net (Postfix) with ESMTP id
AB7AD1EE509C
for <freetds AT lists.ibiblio.org>; Fri, 20 Sep 2013 19:13:20 -0400 (EDT)
Received: (qmail 17958 invoked from network); 20 Sep 2013 23:13:20 -0000
Received: by simscan 1.4.0 ppid: 29242, pid: 13815, t: 0.3615s
scanners: clamav: 0.88.2/m:52/d:10739
Received: from unknown (HELO oak.schemamania.org) ([216.254.83.208])
(envelope-sender <jklowden AT freetds.org>)
by mail6.sea5.speakeasy.net (qmail-ldap-1.03) with SMTP
for <freetds AT lists.ibiblio.org>; 20 Sep 2013 23:13:19 -0000
Received: from oak.schemamania.org (localhost [IPv6:::1])
by oak.schemamania.org (Postfix) with SMTP id 326A030E0912
for <freetds AT lists.ibiblio.org>; Fri, 20 Sep 2013 19:13:19 -0400 (EDT)
Date: Fri, 20 Sep 2013 19:13:19 -0400
From: "James K. Lowden" <jklowden AT freetds.org>
To: freetds AT lists.ibiblio.org
Message-Id: <20130920191319.1a8c3e6f.jklowden AT freetds.org>
In-Reply-To:
<CAN=WvWtwBza6qtnMzt5SGwAoZtKZPn86OPFvv9iB5K6=7tmRzQ AT mail.gmail.com>
References:
<CAN=WvWtwBza6qtnMzt5SGwAoZtKZPn86OPFvv9iB5K6=7tmRzQ AT mail.gmail.com>
X-Mailer: Sylpheed 3.0.2 (GTK+ 2.20.1; x86_64--netbsd)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Subject: Re: [freetds] Again about rows count
X-BeenThere: freetds AT lists.ibiblio.org
X-Mailman-Version: 2.1.9
Precedence: list
Reply-To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
List-Id: FreeTDS Development Group <freetds.lists.ibiblio.org>
List-Unsubscribe: <http://lists.ibiblio.org/mailman/listinfo/freetds>,
<mailto:freetds-request AT lists.ibiblio.org?subject=unsubscribe>
List-Archive: <https://lists.ibiblio.org/sympa/arc/freetds>
List-Post: <mailto:freetds AT lists.ibiblio.org>
List-Help: <mailto:sympa AT lists.ibiblio.org?subject=HELP>
List-Subscribe: <http://lists.ibiblio.org/mailman/listinfo/freetds>,
<mailto:freetds-request AT lists.ibiblio.org?subject=subscribe>
X-List-Received-Date: Fri, 20 Sep 2013 23:13:31 -0000

On Fri, 20 Sep 2013 14:59:15 +0300
Velichko Yuriy <velichko.yuriy AT gmail.com> wrote:

> I need to figure out the number of records returned from SELECT query,
> before start fetching data [MSSQL Server].

>From the perspective of SQL Server, that's a little like asking for
tomorrow's winning lottery ticket today.

The server returns the rows as they are produced. Often the client
gets the first row before the server has even *created* the last one.
The only time delivery of the first row is deferred until production of
the final one is in the presence of ORDER BY. And sometimes not even
then, if the join algorithm happens to produce the rows in the order
required by the ORDER BY clause.

The "need" to know how many rows will be returned can be illusory. The
programmer naturally wants to know in advance how much to allocate in
the receiving receptical (GUI grid, C++ collection, whatever). The
answer is usually to start by taking just some, and getting more as
needed, or to use a growable collection, or to write the results to a
file temporarily, then allocate and read them back in.

But what if you want to display the "page count" or some such ("showing
rows 11-20 of 453")? This is an ancient need, not only of database
programming, and *requires* two passes. Consider for instance the
problem of putting a "page n of N" footer on any document. troff
cannot do that without arcane trickery even today, because it's a
single-pass system!

One solution, which is fast and sometimes good enough, is to first
SELECT COUNT(*), and then select the rows. That goes wrong though if a
referenced table is updated between the two selects.

Another solution is SELECT ... INTO #T. That immediately returns the
rowcount while isolating your results from subsequent updates. But
some libraries introduce pooling and other medation that interfere with
the use of temporary tables.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page