gmane.comp.db.tds.freetds

Question on TDS and compatibility on SQL 2012

Norman Chan — 2012-05-24T00:15:46

Hello,

I am new to FREETDS.  I'm working on as DBA Admin and owner of database on SQL 2000 sp3
We are currently using PHP version is 5.2.6  running on OSX Server  using FreeTDS<http://www.freetds.org/> (ver 0.82) to communicate with MS SQL Server 2000.


Can someone confirm my understanding of TDSfree?  It's an API that works with SQL Server... Underneath the hood -  it uses TDS specification to access the SQL Server database.   (Therefore it does not need to have SQL Server Native Client drives, etc installed on machine housing webserver/php )

Separately:
I wanted to ask the group if anyone has setup TDSfree with SQL 2012?

Thank you for any help and info




This message is the property of Kamehameha Schools and any attachments are confidential to the intended recipient at the e-mail address to which it has been addressed. If you are not the intended recipient, you may not copy, forward, disclose or use any part of this message or its attachments. If you received this transmission in error please notify the sender immediately by e-mail or contact Kamehameha Schools at 808 523 6200 and then delete this message from your system.

Re: git push #1

Craig A. Berry — 2012-05-24T00:12:44

On May 23, 2012, at 2:52 PM, James K. Lowden wrote:


Right, it was an illustration from the past but an indication of trouble that could happen if we keep using the ChangeLog (as James has been doing).  (and thanks for applying my patch)


You might start with:

% git checkout master
Already on 'master'
% git log branch-0_91..HEAD > myChangeLog
warning: refname 'branch-0_91' is ambiguous.

and see what you want in myChangeLog that's not there.  There are a *lot* of options to "git log".  

I believe the ambiguous ref warning is because we have a tag name that is the same as a branch name.  I don't think, going forward, that tags should really have "branch" in their names.

________________________________________
Craig A. Berry
mailto:craigberry< at >mac.com

"... getting out of a sonnet is much more
 difficult than getting in."
                 Brad Leithauser

Re: git push #1

James K. Lowden — 2012-05-23T19:52:17

On Wed, 23 May 2012 09:10:33 +0100
Frediano Ziglio <freddy77< at >gmail.com> wrote:


How do I prepare a ChangeLog that details every commit since the
previous release?  

I'd like to continue keeping ChangeLog in the distribution even if it's
not a file managed by the SCM.  For a release, I derive the news and
readme files from ChangeLog.  

--jkl

Re: git push #1

Frediano Ziglio — 2012-05-23T08:10:33

2012/5/22 Craig A. Berry <craigberry< at >mac.com>:

Yes, now we don't change ChangeLog every time, git log is enough, but
at the time you did the patch we was still in cvs -> git transition


Applied.

Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS< at >lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Re: git push #1

Frediano Ziglio — 2012-05-22T08:46:46

2012/5/21 James K. Lowden <jklowden< at >freetds.org>:

Quite strange. Try to fetch tags again with

git fetch --tags


Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS< at >lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Re: git push #1

Craig A. Berry — 2012-05-22T02:40:58

On May 19, 2012, at 7:57 AM, Frediano Ziglio wrote:

It's usually enough, unless someone else has pushed since your last push. In that case you'll want to do "git pull --rebase" first in order to avoid spurious empty merge commits. They don't actually do much harm but they do create stupid-looking singularities in the commit history.

I also think ChangeLog is now redundant and could be phased out. It even creates a genuine problem when trying to cherry-pick changes from master onto a maint branch when the ChangeLog was modified in the same commit as something else. Here's an example:

% git checkout Branch-0_91
Switched to branch 'Branch-0_91'
% git cherry-pick e6305b655e590c2
error: could not apply e6305b6... Build more of the newer tests we weren't building on VMS.
hint: after resolving the conflicts, mark the corrected paths
hint: with 'git add <paths>' or 'git rm <paths>'
hint: and commit the result with 'git commit'
% git status
# On branch Branch-0_91
# Changes to be committed:
#
#modified: vms/descrip_mms.template
#
# Unmerged paths:
# (use "git add/rm <file>..." as appropriate to mark resolution)
#
#both modified: ChangeLog
#

There is a merge conflict because ChangeLog is too different between the two branches to be merged even though the actual bits of code that I want merge just fine. Now, if I wanted *all* of the changes to a particular file in master, I could (instead of the above) do

% git checkout Branch-0_91
Switched to branch 'Branch-0_91'
% git checkout master vms/descrip_mms.template
% git status
# On branch Branch-0_91
# Changes to be committed:
# (use "git reset HEAD <file>..." to unstage)
#
#modified: vms/descrip_mms.template
#

Which apparently (I just learned this today) grabs the entire file currently in the master branch and pulls it into whatever branch I'm on. But unfortunately in this case, that brings changes to the file that should not be pulled into the maint branch as well as those that should.

So instead I have to do something like:

% git checkout master
% git diff e6305b655e590c2^ e6305b655e590c2 vms/descrip_mms.template > e6305b655e590c2.patch
% git checkout Branch-0_91
% git apply e6305b655e590c2.patch

And then commit as if it were an entirely new patch and I weren't working on two related branches of a repository in a modern version control system, which it isn't and I am. But since I have just gone through this exercise, there two more steps to preparing this change, one of which is:

% git format-patch HEAD~1
0001-VMS-build-help-from-e6305b655e590c2.patch

and the other of which is attaching that patch here and requesting that it be applied (with "git am" or "git apply-patch") to the 0.91 branch. Without this patch, 0.91 doesn't build on VMS. If someone would apply it I'd be grateful.

________________________________________
Craig A. Berry
mailto:craigberry< at >mac.com

"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser

_______________________________________________
FreeTDS mailing list
FreeTDS< at >lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Re: git push #1

James K. Lowden — 2012-05-21T18:04:28

On Sat, 19 May 2012 13:57:28 +0100
Frediano Ziglio <freddy77< at >gmail.com> wrote:


I'm still confused because "git branch" and "git describe" say
different things:

$ pwd
/var/releng/freetds/Branch-0_91

$ git branch
* Branch-0_91
  master

$ git describe --tags
branch-0_92-54-ga8789bc

$ cd ../master
$ pwd
/var/releng/freetds/master

$ git branch
* master

$ git describe --tags
branch-0_92-325-g9a1fdd5

Is that what we want?   

I use the script below to generate the version.  I expect "git describe
--tags" and "git branch" to both say something about "0_91" on the
release branch and "0_92" on master.  If that's not a good assumption,
I'll change it.  

--jkl

#! /bin/sh
set -e

VERSION=$(git describe --tags \
| sed -E 's/^[^-]*-//; s/-g[[:xdigit:]]+//')

BRANCH=$(git branch | awk '/\*/ {print $2}')

test "${BRANCH}"

if [ master = ${BRANCH} ]
then
    VERSION=$(echo $VERSION | awk -F'[_-]' '{print "dev."$1"_"$2"-"$3}')
fi
VERSION=$(echo $VERSION | sed 's/[_-]/./g')
echo $VERSION

New freetds repo... MARS are coming!

Frediano Ziglio — 2012-05-19T16:53:55

Hi,
   I created a new freetds repo at
https://gitorious.org/~freddy77/freetds/mars-freetds.

No, I'm not mad and it's not a fork. However I don't like to have my
personal branches on the official repo.

One of the main reason is to share mars branch (actually the only
additional branch).

Currently all tests passes even with mars code !!! But not all
enabling mars... and upper layers do not currently support it and
there are no specific tests! So... quite useless branch :)

Apart from odbc support (the only library which support it) blob1 test
fails with mars enabled due to the fact that if client does not limit
packets sending speed server drop connection. I think this is another
point to add to the James page on why do not use mars !! The other
test that fails is cursor4 due to different protocol for transaction
if mars is enabled (perhaps another small point to add to above page).

Currently another problem is error reporting. If server drop
connection all sessions should receive a network error.

Frediano

Re: FreeTDS SYBUNIQUE Bug / Patch

Willi Langenberger — 2012-05-19T15:27:57

According to Frediano Ziglio:

Thank you very much!

(Open Source is great...;-)


\wlang{}

Re: git push #1

Frediano Ziglio — 2012-05-19T12:57:28

2012/5/18 James K. Lowden <jklowden< at >freetds.org>:

Yes, confirmed !!

I think that "git push" is enough.


I backported the change of memset as a small security issue. Well spot!

I noted that you fixed the +1 version issue too. Good!
However I found a small issues, 0_91.46 should be 0.91.46.
Spec file is updated too. Good!


Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS< at >lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

git push #1

James K. Lowden — 2012-05-18T21:50:31

For anyone interested in the git repository, I just pushed my first
commit with

git push ssh://git< at >gitorious.org/freetds/freetds.git

It seemed work.  I'm still getting the hang of git.  

There are two changes:

1.  fix code that induces warnings in clang.  All innocuous.  
2.  remove txt2man.

All man pages are now in -mdoc format.  It was a few hours' work.  The
man pages now look a little better and we lose one dependency.  With a
little cleverness, we could have them in PDF format, too.  

The warnings were mostly about using // as a comment, something I'm
guilty of.  These became either /**/ or #if 0.  

The most significant changes are to md4.c and md5.c.  Each had a line 

memset(ctx, 0, sizeof(ctx));
now
memset(ctx, 0, sizeof(*ctx));

The line intends to clear memory before returning to prevent sensitive
data from remaining in memory.  It was clearing sizeof(void*) instead of
the whole structure.  

--jkl

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 type
                                  ^^^^^^^^^^^^^^^^^^^^^^^^
  01:19:57.072760 7027 (cs.c:66):cs_prretcode(0)
  01:19:57.072764 7027 (cs.c:702):cs_convert() returning  CS_FAIL
  01:19:57.072769 7027 (ct.c:1779):cs_convert-result = 1
  01:19:57.072774 7027 (ct.c:1781):error: converted only 0 bytes for type 40 


Where:
  client type 40: CS_UNIQUE_TYPE
  server type 36: SYBUNIQUE

Ie, while trying to "cs_convert(SYBUNIQUE -> SYBUNIQUE)" we come to:

freetds-0.91/src/ctlib/cs.c
...
convert()
{
  ...
  /* many times we are asked to convert a data type to itself */
  if (src_type == desttype) {
    tdsdump_log(TDS_DBG_FUNC, "cs_convert() srctype == desttype\n");
    switch (desttype) {
      case SYBLONGBINARY:
      case SYBBINARY:
      ...
      default:
      ^^^^^^^^
        tdsdump_log(TDS_DBG_FUNC, "error: unrecognized type\n");
        ret = CS_FAIL;
        break;
    }


So it seems, that the SYBUNIQUE case is missing...

Anyway, here is my patch:

--- freetds-0.91/src/ctlib/cs.c.ori   2010-10-05 10:36:36.000000000 +0200
+++ freetds-0.91/src/ctlib/cs.c         2012-05-16 02:09:42.000000000 +0200
< at >< at > -682,6 +682,7 < at >< at >
       case SYBDECIMAL:
           src_len = tds_numeric_bytes_per_prec[
             ((TDS_NUMERIC *) srcdata)->precision] + 2;
       case SYBBITN:
+      case SYBUNIQUE:
            memcpy(dest, srcdata, minlen);
           *resultlen = minlen;

At least, that seems to solve my problem ;-)


\wlang{}

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
assertion.  

Finding flaws in ODBC is like shooting fish in a barrel, and this is a
good example of a bad feature engendered, no doubt, by wrongheaded
demands. (I can hear the customer demanding, "but I need to know the
*real* column name!")  

SQL_DESC_BASE_COLUMN_NAME should never have appeared in
SQLColAttribute.  It is useless when not redundant.  The caller has no
rightful need of it, because the only time it "works" is to unveil the
renaming effect of "AS" in the SQL the application only just *sent* to
the server moments before!  Columns produces by scalar functions (e.g.
count(*)) have no "base" column name; columns produced by views,
functions, and stored procedure do, but in what sense are they "base"?
Does SQL_DESC_BASE_COLUMN_NAME reveal the name of the table used by a
view to produce a column?  If only.  

This would be a *great* feature if it actually worked, if every column
in every query could be attributed to its base table.  A server that
could do that could update views without restriction.  But that's a
massive job with unsolved theoretical problems.  

Instead we have a broken feature, something that was easy to create but
serves mostly to confuse.  Very few ODBC programmers understand what
SQL_DESC_BASE_COLUMN_NAME does, and none need it.  But a great many are
confused by it, and still more suffer at their hands if they choose
wrongly.  Which certainly seems to be the case here.  

--jkl

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 accepts no responsibility for loss or damage caused by software viruses.

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 name!

For example:

SELECT


   [EnquiryID] AS "The_Key"


   FROM [db].[dbo].[table]


Which works fine in my admin application (ie: NOT PHP), but if I run the same in my PHP environment, I get:

stdClass Object


(


    [PK_EnquiryID] => 1


)




You can see it should be [The_Key] => 1

It's reproducible - and I have access to two servers (one with FreeTDS 0.82, the other with 0.91) - both exhibit this behaviour.

Before I rewrite all my queries to separate ones, has anyone any idea how I can get round this, please? I'm tearing my hair out!

Cheers

Neil
---
www.hencam.co.uk<http://www.hencam.co.uk> - for when you really want to watch hens on the Internet...

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 accepts no responsibility for loss or damage caused by software viruses.

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"
   FROM [db].[dbo].[table]

Works fine in my admin application (ie: NOT PHP), but if I run the same in
my PHP environment, I get:

stdClass Object
(
    [PK_EnquiryID] => 1
)

You can see it should be [The_Key] => 1

Has anyone any idea how we can get round this, please? I'm tearing my hair
out!

Cheers

Neil
---
www.hencam.co.uk - for when you really want to watch hens on the Internet...

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.