gmane.comp.db.tds.freetds

Re: FreeTDS SYBUNIQUE Bug / Patch

Frediano Ziglio — 2012-05-16T20:41:30

Applied!

Added support if srctype != desttype

Frediano

2012/5/16 Willi Langenberger <wlang< at >wu.ac.at>:
_______________________________________________
FreeTDS mailing list
FreeTDS< at >lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

FreeTDS SYBUNIQUE Bug / Patch

Willi Langenberger — 2012-05-16T10:52:57

Hi!


I think there is a bug in FreeTDS regarding SYBUNIQUE column type...

I'm accessing a MS SQLServer 2008 using freetds-0.91/ctlib. The server
table has "uniqueidentifier" columns (which correspond to SYBUNIQUE /
CS_UNIQUE_TYPE).

With "ct_bind" i set the datatype to CS_UNIQUE_TYPE. However,
"ct_fetch" doesnt return any data (copied=0) :-(

Using "tdsdump", i see the following lines in the tracefile:

  01:19:57.072719 7027 (ct.c:1736):_ct_bind_data(): column 25 is type 36 and has length 16
  01:19:57.072725 7027 (ct.c:1907):_ct_get_client_type(type 36, user 0, size 16)
  01:19:57.072730 7027 (cs.c:523):cs_convert(0x8d8d2e0, 0xbfc76fcc, 0xb74a1bc4, 0xbfc76f20, 0x8e85438, 0x8e85450)
  01:19:57.072735 7027 (ct.c:2018):_ct_get_server_type(40)
  01:19:57.072740 7027 (ct.c:2018):_ct_get_server_type(40)
  01:19:57.072745 7027 (cs.c:569):converting type 36 (16 bytes) to type = 36 (16 bytes)
  01:19:57.072750 7027 (cs.c:583):cs_convert() srctype == desttype
  01:19:57.072755 7027 (cs.c:697):error: unrecognized t

Re: MSSQL Server 2005 - select [field] AS [name] does not work?

James K. Lowden — 2012-05-15T18:49:53

On Tue, 15 May 2012 15:21:29 +0000
Neil Whitaker <NWhitaker< at >acornstairlifts.com> wrote:

..
which

Yes, that's what I meant.  The ODBC API provides access to both names.
If you choose the wrong one, you get the wrong one.  ;-)

http://msdn.microsoft.com/en-us/library/ms713558(v=vs.85)

Compare in the ODBC API function SQLColAttribute the FieldIdentifiers
SQL_DESC_BASE_COLUMN_NAME and SQL_DESC_LABEL.  

It might be that someone misunderstood the version numbers in the table
of FieldIdentifiers linked to above.  The table appears under the
heading "Backwards Compatibility" but actually applies to  all
versions.  SQL_DESC_LABEL was supported (as SQL_COLUMN_LABEL, it seems)
under ODBC 2.0, and SQL_DESC_BASE_COLUMN_NAME first appeared in ODBC
3.0.  SQL_DESC_LABEL is part of the 3.0 specification.  It is not
superseded and continues to be the right one to use.  In fact, the
presence of SQL_DESC_BASE_COLUMN_NAME in the standard is a mistake.  

As long as I've waded in this far, I might as well defend that
assertio

Buffer overflow in FreeTDS code

Frediano Ziglio — 2012-05-15T17:14:30

Hi,
  some days ago I discovered (and fixed) a small buffer overflow in
both 0.91 and development code.

Change at https://gitorious.org/freetds/freetds/commit/e5fe918627ab00e99faadd2c2ee0022f0be7dc98.

It's a small read overflow (probably leading only to a crash) but
still a buffer overflow.

Frediano

Re: MSSQL Server 2005 - select [field] AS [name] does not work?

Neil Whitaker — 2012-05-15T15:21:29

Hi,

It's a bit of a strange one as you can't very easily search for it ('AS' isn't the best keyword to try and find!), I tried it using isql, and it does work:

SQL> select TOP 10 [EnquiryID] AS [Pri_Key] FROM [db].[dbo].[table];
+------------+
| Pri_Key    |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
| 10         |
+------------+
SQLRowCount returns 10
10 rows fetched

So does this support your suggestion?  It's worked with FreeTDS which shows it's getting the right data, but when used with PHP it's munging the wrong field names?

Cheers

Neil
If you are not the intended recipient, please notify the sender immediately by replying to the e-mail, and then delete it without making copies or using it in any way. Although any attachments to the message will have been checked for viruses before transmission, you are urged to carry out your own virus check before opening attachments, since Acorn Stairlifts

Re: MSSQL Server 2005 - select [field] AS [name] does not work?

James K. Lowden — 2012-05-15T14:27:10

On Tue, 15 May 2012 10:56:42 +0000
Neil Whitaker <NWhitaker< at >acornstairlifts.com> wrote:


If you get the correct answer to the same query with isql or bsqlodbc,
you might want to report the problem to the PHP project.  IIRC this has
been reported before and owes to PHP using the wrong property for the
column name.  

--jkl

MSSQL Server 2005 - select [field] AS [name] does notwork?

Neil Whitaker — 2012-05-15T10:56:42

Hi guys,

I have a unix server which has PHP 5.2.13 installed, and it has access to two SQL servers (one is running MSSQL SERVER 2000 and the other MSSQL 2005).

It uses FreeTDS - details:
*         Version: freetds v0.91
*         freetds.conf directory: /usr/local/etc
*         MS db-lib source compatibility: no
*         Sybase binary compatibility: no
*         Thread safety: yes
*         iconv library: yes
*         TDS version: 7.1
*         iODBC: no
*         unixodbc: yes
*         SSPI "trusted" logins: no
*         Kerberos: no

And I'm using unixODBC-2.3.1

The MSSQL 2000 server is fine, I can run all SQL statements and use mssql_num_rows and mssql_fetch_assoc much as you would with MySQL.

However, the MSSQL 2005 server won't work with mssql_num_rows or mssql_fetch_assoc - you have to use odbc_fetch_row.

That's not really an issue, I assume it's just due to the different server versions.

However, I have a huge issue with the MSSQL 2005 server connection: I cannot select a field AS another nam

MSSQL Server 2005 - select [field] AS [name] does notwork?

2012-05-15T10:50:16

I have a unix server which has PHP 5.2.13 installed, and it has access to
two SQL servers (one is running MSSQL SERVER 2000 and the other MSSQL 2005).

It uses FreeTDS - details:

   - Version: freetds v0.91
   - freetds.conf directory: /usr/local/etc
   - MS db-lib source compatibility: no
   - Sybase binary compatibility: no
   - Thread safety: yes
   - iconv library: yes
   - TDS version: 7.1
   - iODBC: no
   - unixodbc: yes
   -
   - SSPI "trusted" logins: no
   - Kerberos: no

And I'm using unixODBC-2.3.1

The MSSQL 2000 server is fine, I can run all SQL statements and use
mssql_num_rows and mssql_fetch_assoc much as you would with MySQL.

However, the MSSQL 2005 server won't work with mssql_num_rows or
mssql_fetch_assoc - you have to use odbc_fetch_row.

That's not really an issue, I assume it's just due to the different server
installs.

However, I have a huge issue with the MSSQL 2005 server connection: I
cannot select a field AS another name!

For example:

SELECT
   [EnquiryID] AS "The_Key"
   FRO

FreeTDS, PHP accessing xml field type

Antonio Pitrone — 2012-05-10T06:04:22

hello, 
i'm using freeTDS to access a Microsoft SQL Server 2005. we installed the last stable version (0.91) of freetds in a x64 red hat linux distribution from rpm. after installing we recompiled php and Mssql module , the freetds configuration file is this 


Compile-time settings (established with the "configure" script) 
Version: freetds v0.91 
freetds.conf directory: /usr/local/etc 
MS db-lib source compatibility: no 
Sybase binary compatibility: no 
Thread safety: yes 
iconv library: yes 
TDS version: 7.2 
iODBC: no 
unixodbc: yes 
SSPI "trusted" logins: no 
Kerberos: no 


i can connect to ms sql server db and retrieve data from any field exept from xml field type. when my query contain an xml filed type an error occur " query error ".
anybody can help me to sort it out? 
hope to receive soon as possible an help from the community; we really need to access that kind of field for our project related to the dissemination of Italian 2011 agriculture census data. 

Antonio Pitrone

Check for SQL server port mismatch Re: tsql -S works but tsql -H times out

Morizono, Hiroki — 2012-05-09T19:48:46

Thanks Eric--

Someone had turned on dynamic ports on the SQL server and didn't set it back so instead of having the server listening at 1433 like I thought. 

tsql -LH 123.123.123.123 showed the portnumber to be up in the 49000 range.

tsql -H 123.123.123.123\\instancename -p portnumber -U 

Things worked when instead of using 1433, I set it to the right number.
my freetds.conf 
only had

[shortname]
  host = 123.123.123.123
  instance = instance
  tds version = 8.0

and didn't have a port =  
in it. 

Am guessing that 
tsql -S shortname -U sa 
was talking to the SQL Server Browser which sent things to the correct dynamic port


For what it's worth
TDSVER=7.2 tsql -H 123.123.123.123\\instancename -p portnum -U username
on SQL Server 2008 Express 
will connect but first throws up the following error 
Error 100 (severity 11):
              unrecognized msgno
1>

Changing to
TDSVER=8.0 
fixes that.

Re: Support for AIX 7.1

James K. Lowden — 2012-05-09T16:04:09

On Wed, 09 May 2012 11:13:25 +0200
Sebastien FLAESCH <sf< at >4js.com> wrote:


According to 

http://publib.boulder.ibm.com/infocenter/comphelp/v8v101/topic/com.ibm.xlcpp8a.doc/proguide/ref/compile_library.htm#compiling_shared_aix

you need to add -qmkshrobj to CFLAGS:  "Create the shared library from
the desired object files, using the -qmkshrobj compiler option".  

It might be that the correct command is generated by a later version of
autoconf or libtool. You could regenerate the configure script
using a later version.  If that helps, please let us know, and I'll
update the version I'm using for the nightly snapshots.  

HTH.  

--jkl

Re: Nested Select Statements

James K. Lowden — 2012-05-09T13:54:47

On Mon, 7 May 2012 23:18:21 -0400
Jeremy Livingston <jeremyjlivingston< at >gmail.com> wrote:

Kudos for isolating the issue and stating it clearly.  You had to peer
through quite a few layers to get to the crux of the matter.  

...

Yes, but it's not an implementation detail; Microsoft's driver behaves
the same way.  As the FAQ answer says, the TDS *protocol* requires all
results be processed before the server will accept another query.  


You may find http://www.freetds.org/mars.html helpful to understand
the misunderstanding of how tcp/ip and database server connections are
designed and used.  

Other than that, you might want to inform the Doctrine folks that their
software is out of spec.  ODBC doesn't require drivers to provide the
behavior their software evidently needs.  

HTH.  

--jkl

Re: Support for AIX 7.1

Sebastien FLAESCH — 2012-05-09T12:51:36

Is this expected on AIX 7.1:

$ ./config.guess
rs6000-ibm-aix

?

Seb

On 05/09/2012 11:13 AM, Sebastien FLAESCH wrote:

Re: Support for AIX 7.1

Sebastien FLAESCH — 2012-05-09T09:13:25

Hello,

Sorry for the mistake, I have now tested with CFLAGS set before running configure.

That worked.

A collegue told me that it was also sufficient to do following:

1) export OBJECT_MODE=64

2) Patch ./src/tds/mem.c with:

#if HAVE_SYS_SOCKET_H
#include <sys/socket.h>
#endif /* HAVE_SYS_SOCKET_H */

3) CC=xlc CFLAGS="-q64" ./configure ...

...

However, in both cases, when looking at the lib directory, it appears that it
does not produce shared libs, only static libs are created:

$ dump -X64  -H libtdsodbc.a
(no output)
$

Any idea?

Seb

On 05/04/2012 07:33 PM, James K. Lowden wrote:

Re: tsql -S works but tsql -H times out

Haszlakiewicz, Eric — 2012-05-08T22:34:30

Maybe try doing this first:
export TDSVER=8.0

I don't know if there's an environment variable to specify the instance.

eric

Misinterpereting instance as part of server

Dave Schultz — 2012-05-08T18:43:29

I have a FreeTDS/odbc source that appears to be trying to resolve the server and instance of "servername.domain\db1" as a server instead of a server called "servername.domain"
and an instance "db1"



I know this should work because I have two linux servers using the same exact odbc source.
Server A works.
Server B doesn't.

SERVER A:
-------------------------------------------------------
A Redhat server.
Using a data source in odbc.ini, I can connect to a particular MS SQL server and instance with the following server name in the source definition:

Server = Servername.domain\db1

Some info from that server:

$ tsql -C
Compile-time settings (established with the "configure" script):
                           Version: freetds v0.64
    MS db-lib source compatibility: yes
       Sybase binary compatibility: unknown
                     Thread safety: yes
                     iconv library: yes
                       TDS version: 4.2
                             iODBC: no
                          unixodbc:

tsql -S works but tsql -H times out

Morizono, Hiroki — 2012-05-08T13:58:10

Have freetds 0.91 compiled on a CentOS box
and trying to connect to SQL Server 2008 
in freetds.conf have
[shortname]
  host=123.123.123.123
  instance = tester
  tds version = 8.0

tsql -S shortname -U user   will connect right away
however

tsql -H 123.123.123.123 -U user
just ticks away until I get a timeout error

Is it because I'm not passing the instance when I use -H? --is there a way to do that? 

What I really want to do is talk to the DB server using an ant build script, but it was breaking. 

Any help appreciated,

Nested Select Statements

Jeremy Livingston — 2012-05-08T03:18:21

Hello,

I am connecting to a Microsoft SQL Server 2008 database using unixODBC and
FreeTDS on PHP. Everything was going well until the ORM library that I'm
using (Doctrine) attempted to perform a select statement while cycling
through the result set of another query. I received the error:
"SQLSTATE[24000]: Invalid cursor state: 0 [FreeTDS][SQL Server]Invalid
cursor state (SQLExecute[0] at ../pdo_odbc/odbc_stmt.c:254)"

After doing some research, it appears that this behavior is by-design in
FreeTDS (http://www.freetds.org/faq.html#pending). Is this error that I'm
getting because of this implementation detail? If so, are there any plans
to change this behavior? If not, does anyone have any recommendations for a
driver I could use with PHP that would allow this behavior?

Under normal circumstances, I would simply adjust the code myself. However,
since I'm using someone else's library, I don't exactly have this luxury.

Any suggestions are appreciated.
Thank you!

Re: headers for odbc functions

James K. Lowden — 2012-05-07T23:30:02

On Mon, 7 May 2012 20:30:27 +0000
"Haszlakiewicz, Eric" <EHASZLA< at >transunion.com> wrote:


To be precise, FreeTDS *does* implement the ODBC functions.  It doesn't
include the declarations (the header files) because it shares them with
other ODBC drivers.  

I'm open to suggestions.  From where I sit, it's a packaging problem.


FreeTDS compiles an ODBC driver in the context of a set of declarations
and symbolic constants that are controlled externally.  It requires the
header files in the same way you'd need Firebird headers to build a
Firebird plug-in or (I guess) CUPS headers to build a CUPS driver.  

The problem with including those headers in the FreeTDS distibution,
apart from the work involved in doing that and keeping them updated, is
that it wouldn't really help.  Instead, the naive user lands a little
deeper in the weeds: compilation might go nicely using the included
headers, but what if they don't match what's installed?  How will the
installation process integrate the driver into the existing ODB

Re: headers for odbc functions

Haszlakiewicz, Eric — 2012-05-07T20:30:27

Oh, ok.  I missed the mention about "ODBC Preparation" on the how-to-build page, and I didn't notice the --with-unixodbc option to configure.  Now that I installed unixODBC and configured FreeTDS with that I'm able to compile my program.
It's a bit confusing since the FreeTDS user guide claims it implements the ODBC functions, but it's not usable w/o the other package.

Thanks for the help.

eric

Re: SQL_NO_TOTAL error on text larger than 4096 bytes

Vagif Verdi — 2012-05-07T18:20:45

But previous version (0.82) does support large text fields without forcing 
users manually check for sql_no_total. Why remove useful and working 
functionality ? Is there a way to put it back ? Or provide a small example how 
to handle it ? I could then use the example to fix my haskell bindings to 
support sql_no_total.


On Monday, May 07, 2012 07:01:03 PM Frediano Ziglio wrote:
ha scritto: