[freetds] Difference between tsql and libsybdb

[Alberto Pulvirenti]

Dear all,

after some time I tried to find a solution to this problem, I preferred to
send a request directly to this mailing list, since I suppose that experts
about freeTDS will be looking at it and find a solution maybe quite easily.

I have implemented a C++ class which makes use of the libsybdb to connect
to a SQL Server 2008 database and allow for some standard queries.
I had to use this class inside a network which made me available a SQL
Server DB in an instance that applies a Logon trigger.
Now, what happens is the following. If I try to access this instance using
the "tsql" executable which comes together to the freeTDS source package (I
am using 0.91, the latest stable release I found in the site), then I
manage to login correctly, I get the prompt and I can successfully execute
queries on the DB which is allowed for me to access.
Instead, if I try to log into the same DB on the same instance using my
class, it fails because it is kicked out by the logon trigger.
What I do is just creating a LOGINREC object and set it with what is needed
to access the DB (user, password, port, host, etc,), which is nothing more
and nothing less than what I put in the command line when I call tsql.

I called the admins of that SQL Server instance and the only help I got was
'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

Verify that SET options are correct for use with indexed views and/or

indexes on computed columns and/or filtered indexes and/or query
notifications

and/or XML data type methods and/or spatial index operations

the point is that I don't understand if this is a critical failure point
and, if it is, what should I add to the connection to avoid this failure.
What I do is similar to what I have always seen in the examples about how
to set up a connection:

// allocate login params
LOGINREC *login; if ((login = dblogin()) == FAIL)
__LOG_ERROR_RETURN(CROWD_ERR_DB_FAILURE, log_preamble() << "failed to
initialize login object") // setup login params DBSETLUSER(login,
user.c_str()); DBSETLPWD(login, pwd.c_str()); DBSETLAPP(login,
"CrowdForce"); DBSETLCHARSET(login, "utf8"); DBSETLDBNAME(login,
db_name.c_str()); if (gethostname(hostname, max_len) == 0)
DBSETLHOST(login, hostname); else __LOG_ERROR_RETURN(CROWD_ERR_DB_FAILURE,
log_preamble() << "failed to retrieve hostname") // connect to DB engine
and free login object m_connection = dbopen(login, db_conn.c_str());
dbloginfree(login); if (m_connection == NULL)
__LOG_ERROR_RETURN(CROWD_ERR_DB_FAILURE, log_preamble() << "failed to open
connection")

[James K. Lowden]

On Mon, 24 Sep 2012 12:33:05 +0200

Post by Alberto Pulvirenti
I have implemented a C++ class which makes use of the libsybdb to
connect to a SQL Server 2008 database and allow for some standard
queries.

You may wish to look at sourceforge.net/projects/dbstreams/. :-)

Post by Alberto Pulvirenti
if I try to log into the same DB on the same instance using my
class, it fails because it is kicked out by the logon trigger.
What I do is just creating a LOGINREC object and set it with what is
needed to access the DB (user, password, port, host, etc,), which is
nothing more and nothing less than what I put in the command line
when I call tsql.
I called the admins of that SQL Server instance and the only help I
'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

Verify that SET options are correct for use with indexed views and/or

indexes on computed columns and/or filtered indexes and/or query
notifications

and/or XML data type methods and/or spatial index operations

Look at the login process for bsqldb. Also perhaps try the source code
from the Tenderfoot section of the UG. It sounds like your server may
require you to call dboption() before dbopen().

HTH.

--jkl

[Alberto Pulvirenti]

Hi James,

2012/9/24 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Mon, 24 Sep 2012 12:33:05 +0200

Post by Alberto Pulvirenti
I have implemented a C++ class which makes use of the libsybdb to
connect to a SQL Server 2008 database and allow for some standard
queries.

You may wish to look at sourceforge.net/projects/dbstreams/. :-)

Post by Alberto Pulvirenti
if I try to log into the same DB on the same instance using my
class, it fails because it is kicked out by the logon trigger.
What I do is just creating a LOGINREC object and set it with what is
needed to access the DB (user, password, port, host, etc,), which is
nothing more and nothing less than what I put in the command line
when I call tsql.
I called the admins of that SQL Server instance and the only help I
'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

Verify that SET options are correct for use with indexed views and/or

indexes on computed columns and/or filtered indexes and/or query
notifications

and/or XML data type methods and/or spatial index operations

Look at the login process for bsqldb. Also perhaps try the source code
from the Tenderfoot section of the UG. It sounds like your server may
require you to call dboption() before dbopen().

Thanks for this suggestion, but since I am noob in this respect, can you
explain me how I can look at this?
And what's the UG (and its tenderfood section)?

I have thought that maybe I could need dboption()'s but I didn't manage to
find a help about what options to set, how and what is their meaning.

Looking slightly more in detail, I found that the login packet sent by my
application to that server is an XML block (since I am not the owner of
the credentials I hide them):

<EVENT_INSTANCE><EventType>LOGON</EventType><PostTime>2012-09-20T15:43:17.147</PostTime><SPID>385</SPID><ServerName>[...]</ServerName><LoginName>[...]</LoginName><LoginType>SQL
Login</LoginType><SID>[...]</SID><ClientHost>192.168.235.238</ClientHost><IsPooled>0</IsPooled></EVENT_INSTANCE>

...and it could be possible that the server does not like or is not well
configured to accept messages like them, but I didn't ever tell my
implementation to communicate in XML, and then I don't know if there are
other possibilities (and which one is used by "tsql" executable), and how I
can eventually switch among them.

Thanks a lot and best regards.

Alberto

Post by James K. Lowden
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Reinaldo A. Fagundes]

Hi,

I have a software working well with the unixodbc + freeTDS on MSSQL at i
lost network conections
the time out of queries generate exception and when the network come back I
can't reconect to DB
ever time I have this mensage

[FreeTDS][SQL Server]Communication link failure
uid/pass at MSSQL1

but if I close and reopen application come back to work, but I can?t make
it, I need the app remake
FreeTDS conection work, I still using OTL library

Anybody have idea to help me.

tks

[James K. Lowden]

On Mon, 24 Sep 2012 15:03:59 -0300

Post by Reinaldo A. Fagundes
I have a software working well with the unixodbc + freeTDS on MSSQL
at i lost network conections
the time out of queries generate exception and when the network come
back I can't reconect to DB

If the TCP connection is broken, it cannot be re-established.

If your application is timing out -- and the TCP connection is *not*
broken -- it's important to send a cancellation packet and to read all
replies until the cancellation is acknowledged. In ODBC that's done
with SQLCancel.

AFAIK if you get HYT00 or HYT01 from SQLFetch, you still must call
SQLCancel. If it returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the
connection remains valid and can be used.

HTH.

--jkl

[James K. Lowden]

On Mon, 24 Sep 2012 16:49:22 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Look at the login process for bsqldb. Also perhaps try the source
code from the Tenderfoot section of the UG. It sounds like your
server may require you to call dboption() before dbopen().

Thanks for this suggestion, but since I am noob in this respect, can
you explain me how I can look at this?
And what's the UG (and its tenderfood section)?

The source code for bsqldb is in src/apps/bsqldb.c in your FreeTDS
source tree. The UG is in the doc directory and was installed when you
ran "make install". (Where depends on how it was configured.) The
Tenderfoot guide to DB-Library is part of the UG. The UG for the
current release is online at
http://www.freetds.org/userguide/samplecode.htm.

These resources are intended to make your db-lib programming easier.

Post by Alberto Pulvirenti
I have thought that maybe I could need dboption()'s but I didn't
manage to find a help about what options to set, how and what is
their meaning.

A complete db-lib reference manual proved to be more work than your
friendly maintainer could do. The incomplete one can still help,
though, if only because it's a complete list of the implemented
functions. If you follow the Documentation link on the website,
you'll find the reference manual.

Once you know the function name -- in this case dbsetopt(), not
dboption(), sorry -- then I normally search the web for the name.
Microsoft's and Sybase's manuals usually show up in the first few
hits.

Post by Alberto Pulvirenti
Looking slightly more in detail, I found that the login packet sent
by my application to that server is an XML block (since I am not the
<EVENT_INSTANCE><EventType>LOGON</EventType><PostTime>2012-09-20T15:43:17.147</PostTime><SPID>385</SPID><ServerName>
[...]</ServerName><LoginName>[...]</LoginName><LoginType>SQL
Login</LoginType><SID>[...]
</SID><ClientHost>192.168.235.238</ClientHost><IsPooled>0</IsPooled></EVENT_INSTANCE>

You will find the layout of the login packet also among the documents
on the website. The login packet is not XML, thank heaven. You
application does not send a login packet at all if it uses db-lib.
The library sends the packet (correctly formed) when you call
dbopen().

HTH.

--jkl

[Alberto Pulvirenti]

Hello,

thanks for all help, I am looking at the points you suggested, but I
noticed that I forgot to specify the problem occurred.
Once I asked to the DB admin to report me the log of the failed operation
in this logon trigger which kicks me out, the result is this:

SELECT failed because the following SET options have incorrect
settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.
Verify that SET options are correct for use with indexed views and/or
indexes on computed columns and/or filtered indexes and/or query
notifications and/or XML data type methods and/or spatial index
operations.



but the problem is that when I try to access the same server using tsql (I
mean the executable provided by this package, not the protocol), without
telling it anything more than the host, user, password and port, it logs
into the same server without any problem.
Since it speaks also about XML, that drove me to the idea that there must
be something sent in XML which this server doesn't like, while tsql (yet
the program, I mean) does not.

Thanks, best regards

Alberto

2012/9/25 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Mon, 24 Sep 2012 16:49:22 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Look at the login process for bsqldb. Also perhaps try the source
code from the Tenderfoot section of the UG. It sounds like your
server may require you to call dboption() before dbopen().

Thanks for this suggestion, but since I am noob in this respect, can
you explain me how I can look at this?
And what's the UG (and its tenderfood section)?

The source code for bsqldb is in src/apps/bsqldb.c in your FreeTDS
source tree. The UG is in the doc directory and was installed when you
ran "make install". (Where depends on how it was configured.) The
Tenderfoot guide to DB-Library is part of the UG. The UG for the
current release is online at
http://www.freetds.org/userguide/samplecode.htm.
These resources are intended to make your db-lib programming easier.

Post by Alberto Pulvirenti
I have thought that maybe I could need dboption()'s but I didn't
manage to find a help about what options to set, how and what is
their meaning.

A complete db-lib reference manual proved to be more work than your
friendly maintainer could do. The incomplete one can still help,
though, if only because it's a complete list of the implemented
functions. If you follow the Documentation link on the website,
you'll find the reference manual.
Once you know the function name -- in this case dbsetopt(), not
dboption(), sorry -- then I normally search the web for the name.
Microsoft's and Sybase's manuals usually show up in the first few
hits.

Post by Alberto Pulvirenti
Looking slightly more in detail, I found that the login packet sent
by my application to that server is an XML block (since I am not the

<EVENT_INSTANCE><EventType>LOGON</EventType><PostTime>2012-09-20T15:43:17.147</PostTime><SPID>385</SPID><ServerName>

Post by Alberto Pulvirenti
[...]</ServerName><LoginName>[...]</LoginName><LoginType>SQL
Login</LoginType><SID>[...]

</SID><ClientHost>192.168.235.238</ClientHost><IsPooled>0</IsPooled></EVENT_INSTANCE>
You will find the layout of the login packet also among the documents
on the website. The login packet is not XML, thank heaven. You
application does not send a login packet at all if it uses db-lib.
The library sends the packet (correctly formed) when you call
dbopen().
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Alberto Pulvirenti]

Sorry for bothering else you: How do I set such options before doing the
dbopen()? It seems to require an already initialized DBPROCESS object...

2012/9/25 Alberto Pulvirenti <alberto.pulvirenti at crowdengineering.com>

Post by Alberto Pulvirenti
Hello,
thanks for all help, I am looking at the points you suggested, but I
noticed that I forgot to specify the problem occurred.
Once I asked to the DB admin to report me the log of the failed operation
SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
but the problem is that when I try to access the same server using tsql (I
mean the executable provided by this package, not the protocol), without
telling it anything more than the host, user, password and port, it logs
into the same server without any problem.
Since it speaks also about XML, that drove me to the idea that there must
be something sent in XML which this server doesn't like, while tsql (yet
the program, I mean) does not.
Thanks, best regards
Alberto
2012/9/25 James K. Lowden <jklowden at freetds.org>
On Mon, 24 Sep 2012 16:49:22 +0200

Post by James K. Lowden

Post by Alberto Pulvirenti

Post by James K. Lowden
Look at the login process for bsqldb. Also perhaps try the source
code from the Tenderfoot section of the UG. It sounds like your
server may require you to call dboption() before dbopen().

Thanks for this suggestion, but since I am noob in this respect, can
you explain me how I can look at this?
And what's the UG (and its tenderfood section)?

The source code for bsqldb is in src/apps/bsqldb.c in your FreeTDS
source tree. The UG is in the doc directory and was installed when you
ran "make install". (Where depends on how it was configured.) The
Tenderfoot guide to DB-Library is part of the UG. The UG for the
current release is online at
http://www.freetds.org/userguide/samplecode.htm.
These resources are intended to make your db-lib programming easier.

Post by Alberto Pulvirenti
I have thought that maybe I could need dboption()'s but I didn't
manage to find a help about what options to set, how and what is
their meaning.

A complete db-lib reference manual proved to be more work than your
friendly maintainer could do. The incomplete one can still help,
though, if only because it's a complete list of the implemented
functions. If you follow the Documentation link on the website,
you'll find the reference manual.
Once you know the function name -- in this case dbsetopt(), not
dboption(), sorry -- then I normally search the web for the name.
Microsoft's and Sybase's manuals usually show up in the first few
hits.

Post by Alberto Pulvirenti
Looking slightly more in detail, I found that the login packet sent
by my application to that server is an XML block (since I am not the

<EVENT_INSTANCE><EventType>LOGON</EventType><PostTime>2012-09-20T15:43:17.147</PostTime><SPID>385</SPID><ServerName>

Post by Alberto Pulvirenti
[...]</ServerName><LoginName>[...]</LoginName><LoginType>SQL
Login</LoginType><SID>[...]

</SID><ClientHost>192.168.235.238</ClientHost><IsPooled>0</IsPooled></EVENT_INSTANCE>
You will find the layout of the login packet also among the documents
on the website. The login packet is not XML, thank heaven. You
application does not send a login packet at all if it uses db-lib.
The library sends the packet (correctly formed) when you call
dbopen().
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Tue, 25 Sep 2012 11:12:29 +0200

Post by Alberto Pulvirenti
Once I asked to the DB admin to report me the log of the failed
operation in this logon trigger which kicks me out, the result is
SELECT failed because the following SET options have incorrect
settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

That's not a "logon trigger" message. It doesn't come from a
trigger and it's not triggered by logging in.

Read the message again. It says SELECT failed. You weren't "kicked
out" as far as the server was concerned; your connection was still OK.
It only refused to process your query. Why? Because your SET options
were incorrect for the type of query you issued.

Post by Alberto Pulvirenti
Verify that SET options are correct for use with indexed views and/or
indexes on computed columns and/or filtered indexes and/or query
notifications and/or XML data type methods and/or spatial index
operations.

...

Post by Alberto Pulvirenti
Since it speaks also about XML, that drove me to the idea that there
must be something sent in XML which this server doesn't like, while
tsql (yet the program, I mean) does not.

T-SQL has an "AS XML" clause in its select syntax. That's what the
message refers to. If you read up on it in BOL, you'll find certain
session properties have to be set for the server to process the query.
Similarly if you try to create a an indexed view, you'll find about 13
prerequisites.

Session properties aren't set by dbopen(). They're set via
T-SQL's SET command. Read up on that, and on @@options. (Nowadays
there's a function to query session properties individually, not just
as a bit mask.)

HTH.

--jkl

[Alberto Pulvirenti]

Hi, thanks for the reply, my points are inline.

2012/9/25 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Tue, 25 Sep 2012 11:12:29 +0200

Post by Alberto Pulvirenti
Once I asked to the DB admin to report me the log of the failed
operation in this logon trigger which kicks me out, the result is
SELECT failed because the following SET options have incorrect
settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

That's not a "logon trigger" message. It doesn't come from a
trigger and it's not triggered by logging in.
Read the message again. It says SELECT failed. You weren't "kicked
out" as far as the server was concerned; your connection was still OK.
It only refused to process your query. Why? Because your SET options
were incorrect for the type of query you issued.

Yes, this I understood.
The point is still the same, anyway: if I connect using the shell command:

./tsql -H <host> -p <port> -U <user> -P <pwd> -D <db>

then I manage to connect to the DB, execute queries and so on.

Instead, when I use a program which does a pool of DBSETL...stuff... and
then calls dbopen(), this fails (the message reported by logon trigger is
not given to me, it was a favor by the instance helpdesk who sent me this
as the error message which caused the logon trigger procedure to fail).

The only item which I can modify before calling dbopen() is the LOGINREC
object. I have taken a look also using wireshark, but it is hard to see
exactly which bytes should be perfectly equal and which ones don't.

BTW: of course I have only one $HOME/.freetds.conf file which I use for
both (and apparently both them see it, since I pass the server name in the
same way to both: that is, I pass the same string to the '-H' argument of
tsql command, and to the argument #2 of dbopen()

Post by James K. Lowden

Post by Alberto Pulvirenti
Verify that SET options are correct for use with indexed views and/or
indexes on computed columns and/or filtered indexes and/or query
notifications and/or XML data type methods and/or spatial index
operations.

...

Post by Alberto Pulvirenti
Since it speaks also about XML, that drove me to the idea that there
must be something sent in XML which this server doesn't like, while
tsql (yet the program, I mean) does not.

T-SQL has an "AS XML" clause in its select syntax. That's what the
message refers to. If you read up on it in BOL, you'll find certain
session properties have to be set for the server to process the query.
Similarly if you try to create a an indexed view, you'll find about 13
prerequisites.
Session properties aren't set by dbopen(). They're set via
there's a function to query session properties individually, not just
as a bit mask.)

This last paragraph I didn't fully understand.
In any case, I suppose that I can issue such a SET command only once the
DBPROCESS is properly initialized after dbopen().
Or not?

Thanks

Alberto

Post by James K. Lowden
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Tue, 25 Sep 2012 18:15:38 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Session properties aren't set by dbopen(). They're set via
there's a function to query session properties individually, not
just as a bit mask.)

This last paragraph I didn't fully understand.

What may not be clear is that the problem you're seeing doesn't
manifest itself when you first open the connection. It shows up when
you issue that particular query. A simple query like

SELECT 1 as 'one'

will not fail. But one that requires e.g. CONCAT_NULL_YIELDS_NULL
will.

CONCAT_NULL_YIELDS_NULL is a session property. Session properties in a
T-SQL session are set using the T-SQL SET command, cf.
http://msdn.microsoft.com/en-us/library/ms190356.aspx. Among the many
arcane things affected by session properties are the ability to create
indexed views and the ability to *use* an existing index of an indexed
view, cf. "Requirements for the CREATE INDEX Statement" at
http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.

Post by Alberto Pulvirenti
In any case, I suppose that I can issue such a SET command only once
the DBPROCESS is properly initialized after dbopen().

English is a little ambiguous here. You can issue any number of SET
commands for the duration of the session, just as any query, after
dbopen() and before dbclose().

HTH.

--jkl

[Alberto Pulvirenti]

Hello,

2012/9/27 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Tue, 25 Sep 2012 18:15:38 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Session properties aren't set by dbopen(). They're set via
there's a function to query session properties individually, not
just as a bit mask.)

This last paragraph I didn't fully understand.

What may not be clear is that the problem you're seeing doesn't
manifest itself when you first open the connection. It shows up when
you issue that particular query. A simple query like
SELECT 1 as 'one'
will not fail. But one that requires e.g. CONCAT_NULL_YIELDS_NULL
will.

This is perfectly clear. Then, something which is sent by my dbopen()
invocation maybe triggers some query that relies on this.
I am just wanrdering what can this be, which is not done by the executable
"tsql".

Post by James K. Lowden
CONCAT_NULL_YIELDS_NULL is a session property. Session properties in a
T-SQL session are set using the T-SQL SET command, cf.
http://msdn.microsoft.com/en-us/library/ms190356.aspx. Among the many
arcane things affected by session properties are the ability to create
indexed views and the ability to *use* an existing index of an indexed
view, cf. "Requirements for the CREATE INDEX Statement" at
http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.

Post by Alberto Pulvirenti
In any case, I suppose that I can issue such a SET command only once
the DBPROCESS is properly initialized after dbopen().

English is a little ambiguous here. You can issue any number of SET
commands for the duration of the session, just as any query, after
dbopen() and before dbclose().

I am very sorry since maybe I don't manage to express myself clearly enough.
Anyway, that's the point. My application fails when I call dbopen().
Then, whatever needed to solve this problem I need to apply BEFORE calling
dbopen(). That's what is driving me crazy.

Thanks,

Alberto

Post by James K. Lowden
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Alberto Pulvirenti]

Dear all,

I have some additional info about this problem, so I prefer to give all
details again, hoping in some help from anyone.

So, the problem is the following.
I have developed an executable which tries to log into a SQL Server 2008
instance using dblib provided by freeTDS.
The problem is that I don't manage to log into this server because it has a
logon trigger procedure which fails when I try to access it.

The problem, in this logon trigger is that it has set to OFF the following
three variables:

set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off


and the admin of this instance told me that he will NEVER switch them to
ON. Then I must find a way to access this instance having these setting as
they are currently.

I managed to reproduce the problem with the following procedure in my local
instance of SQL Server 2008 Express. It is enough to add these instructions:

CREATE TRIGGER [filter_test]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @data XML
declare @data01 varchar(100)

SET @data = EVENTDATA();
-->set @data01= @data.value('(/EVENT_INSTANCE/ClientHost)[1]',
'varchar(100)');
END;

I found that the line indicated with the arrow '-->' is the one that causes
the problem. In fact, if I re-define the trigger commenting this line, the
logon trigger is executed properly and I manage to login to the instance,
while if IT don't comment this line, it fails. Then, there must be
something that goes in the XML provided by EVENTDATA() when it is executed
after the login through the db-lib that has something which must be set
properly.

As all of you know, when one compiles the freetds-0.91 library, besides
having the library libsydb.so (which I use), the compilation provides an
executable which is called "tsql".
I tried to access the same DB instance using this executable (then, now and
in the following, when I say "tsql" I am referring to the executable, NOT
the protocol).
Well, if I log into this SQL Server instance using tsql with the suitable
parameters, then the login succeeds perfectly.
Then I assume that I should set something in the login packet in order for
it to be properly configured to allow the logon trigger to process it
correctly when loggin in. Something which surely is properly set in "tsql"
but not when setting up the LOGINREC object which is needed by the dbopen()
function which one invokes when using dblib.

BTW: I also tried to access this SQL Server instance using the other
executable "bsqldb" which is provided in the library and I saw that it
FAILS exactly like my application, then it must have to do with some
non-standard settings which must be put in the login.
I hope that it is not anything which is not managed/feasible using this
library.

Thanks for all help and best regards

Alberto


2012/9/27 Alberto Pulvirenti <alberto.pulvirenti at crowdengineering.com>

Post by Alberto Pulvirenti
Hello,
2012/9/27 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Tue, 25 Sep 2012 18:15:38 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Session properties aren't set by dbopen(). They're set via
there's a function to query session properties individually, not
just as a bit mask.)

This last paragraph I didn't fully understand.

What may not be clear is that the problem you're seeing doesn't
manifest itself when you first open the connection. It shows up when
you issue that particular query. A simple query like
SELECT 1 as 'one'
will not fail. But one that requires e.g. CONCAT_NULL_YIELDS_NULL
will.

This is perfectly clear. Then, something which is sent by my dbopen()
invocation maybe triggers some query that relies on this.
I am just wanrdering what can this be, which is not done by the executable
"tsql".

Post by James K. Lowden
CONCAT_NULL_YIELDS_NULL is a session property. Session properties in a
T-SQL session are set using the T-SQL SET command, cf.
http://msdn.microsoft.com/en-us/library/ms190356.aspx. Among the many
arcane things affected by session properties are the ability to create
indexed views and the ability to *use* an existing index of an indexed
view, cf. "Requirements for the CREATE INDEX Statement" at
http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.

Post by Alberto Pulvirenti
In any case, I suppose that I can issue such a SET command only once
the DBPROCESS is properly initialized after dbopen().

English is a little ambiguous here. You can issue any number of SET
commands for the duration of the session, just as any query, after
dbopen() and before dbclose().

I am very sorry since maybe I don't manage to express myself clearly enough.
Anyway, that's the point. My application fails when I call dbopen().
Then, whatever needed to solve this problem I need to apply BEFORE calling
dbopen(). That's what is driving me crazy.
Thanks,
Alberto

Post by James K. Lowden
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Frediano Ziglio]

Just a bet, if you use any odbc application will work!

Probably it's the "odbc" flags used during connection.

Frediano Ziglio

Post by Alberto Pulvirenti
Dear all,
I have some additional info about this problem, so I prefer to give all
details again, hoping in some help from anyone.
So, the problem is the following.
I have developed an executable which tries to log into a SQL Server 2008
instance using dblib provided by freeTDS.
The problem is that I don't manage to log into this server because it has a
logon trigger procedure which fails when I try to access it.
The problem, in this logon trigger is that it has set to OFF the following
set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off
and the admin of this instance told me that he will NEVER switch them to
ON. Then I must find a way to access this instance having these setting as
they are currently.
I managed to reproduce the problem with the following procedure in my local
CREATE TRIGGER [filter_test]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
'varchar(100)');
END;
I found that the line indicated with the arrow '-->' is the one that causes
the problem. In fact, if I re-define the trigger commenting this line, the
logon trigger is executed properly and I manage to login to the instance,
while if IT don't comment this line, it fails. Then, there must be
something that goes in the XML provided by EVENTDATA() when it is executed
after the login through the db-lib that has something which must be set
properly.
As all of you know, when one compiles the freetds-0.91 library, besides
having the library libsydb.so (which I use), the compilation provides an
executable which is called "tsql".
I tried to access the same DB instance using this executable (then, now and
in the following, when I say "tsql" I am referring to the executable, NOT
the protocol).
Well, if I log into this SQL Server instance using tsql with the suitable
parameters, then the login succeeds perfectly.
Then I assume that I should set something in the login packet in order for
it to be properly configured to allow the logon trigger to process it
correctly when loggin in. Something which surely is properly set in "tsql"
but not when setting up the LOGINREC object which is needed by the dbopen()
function which one invokes when using dblib.
BTW: I also tried to access this SQL Server instance using the other
executable "bsqldb" which is provided in the library and I saw that it
FAILS exactly like my application, then it must have to do with some
non-standard settings which must be put in the login.
I hope that it is not anything which is not managed/feasible using this
library.
Thanks for all help and best regards
Alberto
2012/9/27 Alberto Pulvirenti <alberto.pulvirenti at crowdengineering.com>

Post by Alberto Pulvirenti
Hello,
2012/9/27 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Tue, 25 Sep 2012 18:15:38 +0200

Post by Alberto Pulvirenti

Post by James K. Lowden
Session properties aren't set by dbopen(). They're set via
there's a function to query session properties individually, not
just as a bit mask.)

This last paragraph I didn't fully understand.

What may not be clear is that the problem you're seeing doesn't
manifest itself when you first open the connection. It shows up when
you issue that particular query. A simple query like
SELECT 1 as 'one'
will not fail. But one that requires e.g. CONCAT_NULL_YIELDS_NULL
will.

This is perfectly clear. Then, something which is sent by my dbopen()
invocation maybe triggers some query that relies on this.
I am just wanrdering what can this be, which is not done by the executable
"tsql".

Post by James K. Lowden
CONCAT_NULL_YIELDS_NULL is a session property. Session properties in a
T-SQL session are set using the T-SQL SET command, cf.
http://msdn.microsoft.com/en-us/library/ms190356.aspx. Among the many
arcane things affected by session properties are the ability to create
indexed views and the ability to *use* an existing index of an indexed
view, cf. "Requirements for the CREATE INDEX Statement" at
http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.

Post by Alberto Pulvirenti
In any case, I suppose that I can issue such a SET command only once
the DBPROCESS is properly initialized after dbopen().

English is a little ambiguous here. You can issue any number of SET
commands for the duration of the session, just as any query, after
dbopen() and before dbclose().

I am very sorry since maybe I don't manage to express myself clearly enough.
Anyway, that's the point. My application fails when I call dbopen().
Then, whatever needed to solve this problem I need to apply BEFORE calling
dbopen(). That's what is driving me crazy.
Thanks,
Alberto

Post by James K. Lowden
HTH.
--jkl
_______________________________________________
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

[James K. Lowden]

On Fri, 28 Sep 2012 18:11:57 +0200

Post by Alberto Pulvirenti
The problem, in this logon trigger is that it has set to OFF the
set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off
and the admin of this instance told me that he will NEVER switch them
to ON. Then I must find a way to access this instance having these
setting as they are currently.

http://msdn.microsoft.com/en-us/library/bb326598.aspx

I see now. I never used logon triggers, and was only dimly aware of
their existence. Now I am slightly lessly dimly aware of them.

It's too bad your DBA has chosen not to use his power for good.
Instead of ensuring that every logon has particular session properties
he deeems necessary (by issuing the very statements you found), he's
instead prevented logons by clients that don't.

Sigh. But you have the power, because you have the compiler. Look in
src/dblib/dblib.c for the phrase "we're not an ODBC driver". Delete
that line, recompile, and try to connect. If that works, we know
what the problem is. Other than your DBA, of course.

In option_flag2 in the TDS 7 login packet is a bit that signals to the
server that the client is an ODBC driver. It's turned off in db-lib
because that's how db-lib always worked. Nothing prevents the FreeTDS
db-lib from turning that bit on, or from making it an option. I think
you will find the bit presents no problem at all. (Curious, though,
isn't it, that it's not the vendor but your own organization that
imposes this effort on you?)

If you would like to make this a run-time option for FreeTDS db-lib,
I'm sure others would find it beneficial, too.

HTH.

--jkl

[Alberto Pulvirenti]

Dear James,

I still want to test a little bit more, but it seems you found the problem,
indeed.
About the DBA, I agree with you but that's the life.

How could it become a runtime option?

A useful option could be to set it as a boolean data member in the
DBLIBCONTEXT object, and add it as an option in the dbinit() function,
setting as default its current behaviour.

Thanks

Best and thankful regards

Alberto

2012/9/29 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Fri, 28 Sep 2012 18:11:57 +0200

Post by Alberto Pulvirenti
The problem, in this logon trigger is that it has set to OFF the
set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off
and the admin of this instance told me that he will NEVER switch them
to ON. Then I must find a way to access this instance having these
setting as they are currently.

http://msdn.microsoft.com/en-us/library/bb326598.aspx
I see now. I never used logon triggers, and was only dimly aware of
their existence. Now I am slightly lessly dimly aware of them.
It's too bad your DBA has chosen not to use his power for good.
Instead of ensuring that every logon has particular session properties
he deeems necessary (by issuing the very statements you found), he's
instead prevented logons by clients that don't.
Sigh. But you have the power, because you have the compiler. Look in
src/dblib/dblib.c for the phrase "we're not an ODBC driver". Delete
that line, recompile, and try to connect. If that works, we know
what the problem is. Other than your DBA, of course.
In option_flag2 in the TDS 7 login packet is a bit that signals to the
server that the client is an ODBC driver. It's turned off in db-lib
because that's how db-lib always worked. Nothing prevents the FreeTDS
db-lib from turning that bit on, or from making it an option. I think
you will find the bit presents no problem at all. (Curious, though,
isn't it, that it's not the vendor but your own organization that
imposes this effort on you?)
If you would like to make this a run-time option for FreeTDS db-lib,
I'm sure others would find it beneficial, too.
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Mon, 1 Oct 2012 09:44:49 +0200

Post by Alberto Pulvirenti
A useful option could be to set it as a boolean data member in the
DBLIBCONTEXT object, and add it as an option in the dbinit() function,
setting as default its current behaviour.

Yes. It would also need at least one of:

1. an "emulate ODBC" property in freetds.conf
2. a way to set the compile-time default in confgure.ac

and documentation.

I've slowly come to understand that the easiest way to think about
runtime configuration is that the library should always use environment
variables, if for no other reason than that getenv(3) is easy to use.
Processing freetds.conf becomes a matter of calling putenv for any
variables *not* already set.

I'll repeat here something I said a few years ago, for the benefit of
someone who'd like to get his hands dirty. We need better
session property mangement in FreeTDS.

Microsoft hasn't stood still in the 20 years since it licensed SQL
Server from Sybase. The gulf between a "plain old" connection and a
"modern" one continues to grow, a gulf bridged by session properties.
More and more desirable functionality requires one or more "ANSI"
properties. A session without them is cut off from such useful
features as indexed views. The fix is arcane, hard to find, and hard to
understand. And unnecessary.

In the ODBC driver we set the ODBC-bit in the login packet and
IIRC set session properties to match what Microsoft's driver does. In
db-lib, we do not set any properties because that's the way db-lib
always worked, and what old db-lib applications expect. But modern
db-lib applications -- those using FreeTDS's db-lib -- need a way to
set the session properties at login time. One of those applications is
freebcp, which currently cannot read an indexed view without some
seriously clever use of the -O option.

Perhaps the best outcome would be for db-lib to default to whatever
settings we use in ODBC, with compile- and run-time options to revert
to the current default. That would have the effect of modernizing
db-lib by default while retaining backward compatibility for those who
want it or need it.

Toward that end I suggest someone, not me, implement a new
freetds.conf setting, "ANSI defaults". If set to "yes", it would have
these effects:

1. Turn on the ODBC bit in the login packet.
2. Send SET commands, upon logging in, sufficient to create and use
indexed views and/or equal to those sent by the ODBC driver.

The setting would affect both db-lib and ct-lib (hence implemented in
libtds). Setting "ANSI defaults = no" would prevent the
above-described behavior, thus reverting to current form.

Regards,

--jkl