Skip to Content.
Sympa Menu

freetds - Re: [freetds] Difference between tsql and libsybdb

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Difference between tsql and libsybdb
  • Date: Fri, 28 Sep 2012 22:12:49 +0100

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

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

Frediano Ziglio

Il giorno 28/set/2012, alle ore 17:11, Alberto Pulvirenti
<alberto.pulvirenti AT crowdengineering.com> ha scritto:

> 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>
>
>> Hello,
>>
>> 2012/9/27 James K. Lowden <jklowden AT freetds.org>
>>
>>> On Tue, 25 Sep 2012 18:15:38 +0200
>>> Alberto Pulvirenti <alberto.pulvirenti AT crowdengineering.com> wrote:
>>>
>>>>> 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.)
>>>>
>>>> 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".
>>
>>
>>>
>>> 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.
>>>
>>>> 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
>>
>>
>>>
>>> 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




Archive powered by MHonArc 2.6.24.

Top of Page