Turk, Scott A | 13 Apr 18:39 2015

character set issue between sql server and oracle


We've set up freetds as a gateway between Sql server and Oracle.  When running a query on Oracle across a link
to a Sql Server database, we get extra null characters between each real character in the output.  This
occurs on columns set up as nchar on the Sql Server side.  Can anyone please provide assistance?

Here is the output from the dump command on the query (executed on Oracle across the link), the real output
should be "administrator".

Typ=1 Len=52: 0,97,0,0,0,100,0,0,0,109,0,0,0,105,0,0,0,110,0,0,0,105,0,0,0,115,0,0,0,116,0,0,0,114,0,0,0,97,0,0,0,116,0,0,0,111,0,0,0,114,0,0

Thanks!

Scott Turk
Missouri State University
SATurk <at> missouristate.edu<mailto:SATurk <at> missouristate.edu>
(417) 836-6195
Geoff Montee | 9 Apr 17:40 2015
Picon

Out-of-the-box "date format" values for "default" and "en_US" locales

In FreeTDS, the "default" and "en_US" locales have the following values of
"date format" defined in locales.conf:

[default]
    date format = %b %e %Y %I:%M:%S:%z%p

[en_US]
    date format = %b %e %Y %I:%M:%S:%z%p
    language = us_english
    charset = iso_1

Is there a reason that FreeTDS chose to separate the seconds and
milliseconds with a colon (':'), rather than a dot ('.')? A few users of
tds_fdw recently ran into issues with this:

https://github.com/GeoffMontee/tds_fdw/issues/13

Because of the ISO 8601 standard, I think most applications expect a dot
separator between seconds and milliseconds.

http://en.wikipedia.org/wiki/ISO_8601

These days, it may make sense to use ISO 8601 as the default "date format",
since it is pretty universal. If FreeTDS is going to have a new release
soon, maybe it would be worth changing the default date format?

Thanks,

Geoff Montee
(Continue reading)

Frediano Ziglio | 6 Apr 16:54 2015
Picon

Certification verification

Hi,
  this is my last stopover before release. I wrote a patch to start
doing certificate verification, see
https://github.com/freddy77/freetds/commit/557caf0e0af1c385d6cfd1602f593f9683ed6fc8.

Now the problem is when to check. If a mssql is installed without
certificate a dummy certificate (self signed with a
"Self_Signed_Fallback" common name) is generate every time the sql
instance is started (not during the install so you cannot cache the
certificate of a given server). So there is no way to check these
certificates, FreeTDS (and MSSQL driver too) has to accept by default
these certificates.
The patch add two options to freetds.conf, "ca file" and "crl file",
respectively the file containing the root authorities and the
certificates revocation list (list of certificates to not accept). So
the key of the patch is: if you specify CA list you want to check
certificate, if not accept any certificates sent. Now, the questions
are:
- should FreeTDS check the common name (with server name inside) of
the certificate?
- should I add an option to disable name verification?
- should I read system CA list so to check the global list installed
in the system? I don't know if this is useful, are mssql with proper
certificate using a public CA?

Regards,
  Frediano
LacaK | 31 Mar 12:26 2015
Picon

Assertin failed: unknown bindtype with unknown varlen

Hi,
I get this error "unknown bindtype with unknown varlen" when calling 
dbconvert() in dblib.
This is my situation:
I have table with VARBINARY column with value which is empty not null 
(dbdatlen() = 0)
Then I call dbconvert with srctype=SQLBINARY, srclen=0, 
desttype=SQLBINARY, destlen > 0 and I get above mentioned assertion.

Looking into dbconvert there is in the begining (note condition: srclen==0):

     if (src == NULL || srclen == 0) {
         int bind = dbbindtype(desttype);
         int size = tds_get_size_by_type(desttype);

         if (SYBCHAR == desttype) {
             if (destlen > 0) {
                 size = destlen;
                 bind = CHARBIND;
             } else {
                 size = 1;
                 bind = NTBSTRINGBIND;
             }
         }

         dbgetnull(dbproc, bind, size, dest);   // <--- HERE OCCURS 
ASSERTION
         return size;
     }

(Continue reading)

Frediano Ziglio | 23 Mar 19:55 2015
Picon

New release is getting ready

Hi,
   although there is no dates planned nor any release
candidate/beta/alpha/whatever my TODO list for the release is getting
shorter! As somebody would have noted I mostly updated documentation
to reflect changes.

The only big point left is SSL certificate check (there are no code at
all although I'll have a look at curl implementation and there are
explanation on how to test). I want this in the release as it is a no
back compatible change so is better to have in a release.

Any help or test is really appreciated.

Frediano
Daniel Fazekas | 21 Mar 06:33 2015
Picon

UTF-16 support

I'm testing the recently committed UTF-16 support.

I believe this line in src/tds/config.c's tds_config_login() is wrong:
connection->use_utf16 = login->use_utf16;

It only ends up turning off the UTF-16 support you asked for in the configuration files, which previously
got read into the "connection" structure, with a zero from the "login" structure. So the "use utf-16 = yes"
line from freetds.conf gets overwritten and ignored.
Simply removing the line works, or wrapping it like all the other boolean options:
if (login->use_utf16) {
	connection->use_utf16 = login->use_utf16;
}

Then everything appears to work fine with dblib and ctlib.
Jeffrey Shaw | 20 Mar 16:05 2015
Picon

dblib either truncates nvarchar, or makes NULLs empty strings

Hello,
I am having two problems. If I set the tds version to 7.1, empty strings
are selected as NULL.

If I set the tds version to something else, strings longer than 255
characters are truncated.

My guess is that in the case of tds version 7.1, types marked with * at
http://www.freetds.org/tds.html#types are using "size == 0" when checking
for null, rather than the correct "size == -1". "size == 0" is an empty
string, whereas "size == -1" is NULL.

It isn't clear to me where in the source this check is happening, so I'm
applying here for help.

I've tried CentOS 7's FreeTDS .91 and also freetds-dev-0.92.965.

Thanks,
Jeff
David Horman | 19 Mar 16:47 2015

Occasional "Query failed" with LAMP to MSSQL 2000 server on intensive queries

Hi,

Apologies in advance if this is a bit vague - I'm not expecting anyone
to be able to figure out what's going on from the following description,
I'm really looking more for advice on how to investigate further.

For the last few years I've been happily using FreeTDS to query an
ageing SQL Server 2000 server using PHP scripts running on a Centos 6
server. Recently, though, the amount of data I've been working with, and
the number of complex queries, has increased to the point where I
sometimes see long-running, intensive queries failing after
approximately (but this is not a firm measurement) a minute with the
following output:

PHP Warning:  mssql_query(): Query failed in [script_name] on line 131

Warning: mssql_query(): Query failed in [script_name] on line 131

Then, it seems, any further queries attempted shortly after in the same
script fail with, for example:

Warning: mssql_query(): Unable to set query in [script_name] on line 162

PHP Warning:  mssql_query(): Unable to set query in [script_name] on
line 162

This seems to occur more often if the server is particularly busy, such
that I have tried to carefully time my cronjobs not to overlap at night.
I've never seen a large query fail when I run it directly in SQL Server
Management Studio Express.
(Continue reading)

Ajmer Dhariwal | 17 Mar 13:40 2015

freebcp failing silently with format file that (tries to) remove double quotes

I have an input csv file that has every column quoted in double quotes ("").
I have simplified the problem down to a two row csv file.
There are 4 columns and two are empty.
I am using the format file to strip out the quotes by specifying the quotes
as separators and I just can't get it to work - it fails silently with no
output or errors
The format file works fine
I then decided to copy the format file to my Windows 7 desktop and tried
the exact same thing via bcp and it worked fine - the data hit the target
table with the quotes stripped. Obviously, they're using completely
different mechanisms but I would have expected the format files to behave
similarly.
In my own experimentation I can change the separator and make the
corresponding changes to the format file and it works fine but I was
wondering if anyone had tackled a similar issue because I cannot change the
quoting in some of the input files.

I'm escaping the quotes via \"

Format file and sample csv with dummy data are attached.
Data is being exported from the 4 col csv file into a simple two col table
consisting of nvarchar(30) and nvarchar(80) respectively (SQL Server 2012).

freebcp version $Id: freebcp.c,v 1.59 2011/03/13 21:32:49 jklowden Exp $

Keep up the good work by the way - freetds and freebcp are lifesavers!

Thanks,
Ajmer

(Continue reading)

Alistair Roberts | 17 Mar 02:51 2015
Picon

SQL Server Availability Groups

Hi,

Does FreeTDS support SQL 2014 Availability Groups?

We are looking at migrating from SQL 2008 to SQL 2014 and then use
Availability Groups for the databases.

Ideally we are looking at using a sole IP but we may need to go with the
multi-subnet approach for the AG listener.

Are there any compatibility issues with FreeTDS and SQL 2014?

Regards

Alistair Roberts
Velichko Yuriy | 13 Mar 15:09 2015
Picon

How to connect via windows authentication?

Hello!

I can't figure out how to connect to SQL Server via windows auth using
fretds library (dblib).

In examples is shown how to connect via the SQL authentication.

Is there example for windows auth.
Should I change conf file, or set specific info to the LOGINREC for
dbopen() ?

Gmane