[SOLVED] Using IsNull with TMSSQLConnection lazarus / 16-Databases ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-12 10:44 Got a wierd one, wondering if anyone else has encountered anything like this? Until last night I've been using TODBCConnection.  I've now switched over to TMSSQLConnection.  Nearly everything is working, except for this one SQL.  This SQL runs perfectly under TODBCConnection, within SQL Server Management, and within an ADO app developed under Delphi years ago...  I've also confirmed via SQL Server Profiler that the SQL that runs on the server is indeed the SQL that I expect to run. Code: [Select]Select IsNull(DT.Prefix+'_', '') +TD.Table_Name As 'Table_Name',     SC.Date_Changed, SC.GUID From CHANGE SC Inner Join TABLEDEFINITIONS TD On (TD.TD_ID=SC.TD_ID) Inner Join VIEWDEFINITIONS DT On (TD.DT_ID=DT.DT_ID) Where Date_Changed > '2014-09-11' Order By Date_Changed DescThe issue arises when DT.Prefix Is Null.  Under TODBCConnection (and every other test I try) I get 'Table1'. Under TMSSQLConnection I get '_Table1'  (note the leading _) I've a minor secondary issue - under TMSSQLConnection, GUID is returned with an extra trailing two spaces that aren't returned by the other apps. I'm kind of convinced this is a bug (though I can't see how this can be happening), but before I lodge a report I was wondering if anyone had seen and dealt with this before? ---------------------------------------------------------------------- BigChimp · 2014-09-12 10:50 Ah - it's perhaps the ANSI NULL setting or some such (whatever it's called) http://wiki.lazarus.freepascal.org/mssqlconn ... or a related setting - I think there must be one that regulates whether NULL+something is NULL or something... Second issue: no idea but could be a bug, yes. ---------------------------------------------------------------------- BigChimp · 2014-09-12 10:54 Looks like this: http://msdn.microsoft.com/en-us/library/ms176056.aspx and http://msdn.microsoft.com/en-us/library/ms188340.aspx would be helpful ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-12 10:55 Quote from: BigChimp on September 12, 2014, 12:50:44 pmAh - it's perhaps the ANSI NULL setting or some such (whatever it's called) Nice and fast :-)   Many thanks.    Right, I'll play with both ANSI_NULL_DFLT_ON & ANSI_PADDING, they've both got a kind of good/related feeling about them :-) ---------------------------------------------------------------------- BigChimp · 2014-09-12 10:59 Even though setting those to ON should be fine, I don't think they're related to your problem... see post above yours though. ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-12 13:11 Many thanks @BigChimp - I've been going through a learning curve here, and I would never have got started without your pointers... OK, I'm getting somewhere, but I'm hitting a roadblock (due to ignorance).  Apologies for the long post, but I don't really know what I'm doing, so I'm going to show my work... SQL Server Profiler confirms the TCP connection contains a different set of defaults to all the other apps. Quote-- network protocol: TCP/IP * set quoted_identifier off set arithabort off set numeric_roundabort off * set ansi_warnings off * set ansi_padding off * set ansi_nulls off * set concat_null_yields_null off set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed I've prefixed the different values with a *, and yup concat_null_yields_null is wrong. So, here's the code I'm now running Code: [Select]          FConnection.Connected := True;           FConnection.ExecuteDirect('SET CONCAT_NULL_YIELDS_NULL ON');           FConnection.ExecuteDirect('SET QUOTED_IDENTIFIER ON');           FConnection.ExecuteDirect('SET ANSI_WARNINGS ON');           FConnection.ExecuteDirect('SET ANSI_PADDING ON');           FConnection.ExecuteDirect('SET ANSI_NULLS ON');  Side note: Looking at SQL Server Profiler, I don't need to actually call SET QUOTED_IDENTIFIER ON, mssqlconn does it automatically (and when I look, it's in TMSSQLConnection.DoInternalConnect;) My problem is that the above makes no difference.   SQL Server Profiler show's all the above calls, but shows them wrapped between "BEGIN TRANSACTION" and "ROLLBACK" When I add a FTransaction.Commit;  to the end of the code above, then sure, SQL Server Profiler now shows them wrapped between "BEGIN TRANSACTION" and "COMMIT", but yeah, still no difference to my results. I do note that somehow TMSSQLConnection is calling "SET QUOTED_IDENTIFIER ON" before every single SQL that my app executes.  Finally, the questions: 1.  Is there a way of calling ExecuteDirect so the SET's persist across the entire session? 2.  If no to 1, then how do I call all those ExecuteDirects before each TSQLQuery.Open?  (I can refactor code, but it strikes me there must be a more correct way...) Errr, and a confession - I've no idea how to handle Transactions.  All my code is readonly Selects, so I've never had to handle them before... ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-12 13:32 I now have a working solution.  I'm manually adding the SET when I set the SQL Code: [Select]    TSQLQuery(oDataset).SQL.Text := 'SET CONCAT_NULL_YIELDS_NULL ON; '+AValue; This works, and I'm now refactoring my code so this only happens if Connection=TMSSQLConnection. I'd still like to know how to make this setting global though... ---------------------------------------------------------------------- BigChimp · 2014-09-13 05:16 In my pasql [1] tool this works (gives empty result as expected, not _): QuoteSQL  >select isnull(NULL+'_','') as result; SQL+ >go ============================================================== result ============================================================== With this code just after connecting - note: that pasql code doesn't seem too bothered about transactions either... will have to have another look at that. Code: [Select]    try       FConnection.Connected := true;     except       on E: Exception do       begin         writeln('Error connecting to server: ' + E.ClassName + '/' + E.Message);         halt(32); //Exit code 32: FConnection error       end;     end;     FTransaction := TSQLTransaction.Create(nil);     FConnection.Transaction := FTransaction;     FQuery := TSQLQuery.Create(nil);     FQuery.Database := FConnection; ...     case FDatabaseType of ...       dtMSSQL:       begin //the packetrecords stuff was already there         FQuery.PacketRecords := -1; //To fetch all pending rows; See mssqlconn readme         FTransaction.StartTransaction;         FConnection.ExecuteDirect('SET CONCAT_NULL_YIELDS_NULL ON');         FTransaction.Commit;       end; AFAIU, the set concat bla applies to the entire connection so once you've committed it just after connecting it should just work?? ... and your earlier code (with the .commit) should just work!?!? [1] updated at https://bitbucket.org/reiniero/smalltools/src ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-13 10:18 Since yesterday, I've been doing a lot of reading/research RE: MS SQL Server.  As a followup to something @Taazz said in another topic RE: Named Pipes/Named Instances, I was poking around in MS SQL Server Management Studio, and I found a series of checkboxes under "Server Properties - Connections - Default Connection Options".  I note that everything is turned off in there, including "concat null yields null".  I haven't played yet, but my current thinking is that TMSSQLConnection obeys these Default Connection options, whereas TODBCConnection (and others) overrule them.  Maybe... A quick google also found a series of kind of related known issues with this when dealing with network connections (the two articles I read were both Delphi though).  Seems if the network connection drops the original database settings are used on reconnect, not the new ones.  Sorry, I can't find those articles again :-(  I'm guessing your code is connecting back to a local database server?  Mine's the other side of this vessel, and a lot of our network cables (by necessity) are run adjacent to power lines... And finally, I note your code is using FTransaction to start/end the transaction, I was using the FConnection, so I'll try your code. All of this is a long winded way of saying at this stage I just don't know enough to draw any conclusions.  Today I'll try changing the properties in SQL Server Management Console, see if that changes anything.  I'll change my code to use FTransaction, see if that changes anything.  I'll download your tool, give that a go, and I'll post the results. EDIT:  Incidently - I've trawled the SQLDB source code dblib/msconn, and I just cannot find anywhere the connection properties are set (everything I find are .Execute's called AFTER the connection is already made).  None of the items above (CONCAT_NULL_YIELDS_NULL etc) are covered under any connection string, so there's no way it's the Connection.Params at play.  I'm now thinking this behaviour is entirely set in the freetds dll, not in our code. ---------------------------------------------------------------------- BigChimp · 2014-09-13 10:23 Quote from: Mike.Cornflake on September 13, 2014, 12:18:53 pmcurrent thinking is that TMSSQLConnection obeys these Default Connection options, whereas TODBCConnection (and others) overrule them.  Maybe... Well.... ODBC at least has a certain set of defaults (ANSI stuff on) as specified in the MS docs. Apart from that, there's per database settings that are default for that db and IIUC can be overruled by per connection settings. Who knows. At least with the db test suite and my (limited) fiddling with MS SQL+Laz, things went ok for me... but that's not saying much. Quote from: Mike.Cornflake on September 13, 2014, 12:18:53 pmSeems if the network connection drops the original database settings are used on reconnect, not the new ones.  Sorry, I can't find those articles again :-(  I'm guessing your code is connecting back to a local database server?  Mine's the other side of this vessel, and a lot of our network cables (by necessity) are run adjacent to power lines... Yes it is local. Obvious solution (when you can download etc): test pasql; immediately execute a command or write a simple program that does the same. But... you already intend to do that, as you say below Quote from: Mike.Cornflake on September 13, 2014, 12:18:53 pmAll of this is a long winded way of saying at this stage I just don't know enough to draw any conclusions.  Today I'll try changing the properties in SQL Server Management Console, see if that changes anything.  I'll change my code to use FTransaction, see if that changes anything.  I'll download your tool, give that a go, and I'll post the results. Ok. Good luck. ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-14 13:05 Results are in :-) All results repeatable in my app, and in BigChimps pasql.exe (I used the precompiled download) Switched my code to use FTransaction.StartTransaction; ... FTransaction.Commit; SQL failed in my app, and in pasql (SEE UPDATE BELOW) Commented out all the ExecuteDirects, and switched to looking at Database Properties 1.  At the database server level, I checked "concat null yields null" (previously off) Success.  SQL ran as expected in both my app and pasql.exe 2. At the database server level, I restored the "concat null yields null" to its original off state At the actual database properties level, I changed "Options" - "Concatenate Null Yields Null" from False to True Success.  SQL ran as expected in both my app and pasql.exe All the above tests really established is that CONCAT_NULL_YIELDS_NULL is definitely the relevant setting for my issue, and that calling ExecuteDirect doesn't result in a persisted change of connection properties. Tentative conclusion:  TSQLConnection doesn't override the database server side properties. The database I'm accessing is a proprietary database (not mine).  I did all my testing on a local backup of the database, and won't be changing the production server settings.  For now I've logged a support item with the vendor, and I've reset the code in my app that prepends the SET commands to each SQL I issue. I'm loath to report this as a bug in TSQLConnection (even though I don't have this issue in TODBCConnection) as I can demonstrate that TSQLConnection is obeying the server configuration.  Actually, now I've dug into this issue, this more feels like a bug in ALL the other connection mechanisms :-) Also: Other than ExecuteDirect not persisting, this issue will slowly go away.  Microsoft have announced that future engines will ship with CONCAT_NULL_YIELDS_NULL configured on by default. UPDATE:  What the!??   OK, if I enter "SET CONCAT_NULL_YIELDS_NULL ON" into pasql, then the subsequent SQL executes just fine.   Now, as I understand BigChimp's code, entering SET CONCAT_NULL_YIELDS_NULL ON" into that app means it's run by TSQLQuery, not TSQLConnection.ExecuteDirect.   I may have a way out of here after all... UPDATE TO UPDATE: Bah - doing it that way is the same as me pre- pending the call to each SQL.  So this represents no new information after all. ---------------------------------------------------------------------- BigChimp · 2014-09-14 13:10 Quote from: Mike.Cornflake on September 14, 2014, 03:05:53 pmTentative conclusion:  TSQLConnection doesn't override the database server side properties. Well, IIRC I came to the opposite conclusion with SQL Server 2008 R2 Express: before the pasql modification the query gave the wrong results; after it gave the right results. But I'll check again... though probably not soon... ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-14 13:14 Quote from: BigChimp on September 14, 2014, 03:10:17 pmQuote from: Mike.Cornflake on September 14, 2014, 03:05:53 pmTentative conclusion:  TSQLConnection doesn't override the database server side properties. Well, IIRC I came to the opposite conclusion with SQL Server 2008 R2 Express: before the pasql modification the query gave the wrong results; after it gave the right results. But I'll check again... though probably not soon... If you have Microsoft SQL Server Management Studio installed, can you let me know what Server Roles your user has?   Now I've started playing with MS SQL Server, I'm wondering if this isn't a general configuration issue... ---------------------------------------------------------------------- Mike.Cornflake · 2014-09-14 13:25 Stupid idiot newbie mistake.  Apologies for wasting everyone's time. @BigChimp, it was this line in your last post that forced me to rethink Quotebefore the pasql modification the query gave the wrong results; after it gave the right results. I'd only tested with the precompiled download... There are TWO places in my code I set FConnection.Connected := True;  The second shouldn't be run (and I'd forgotten about it's existence) - I've obviously a bug somewhere, but that doesn't matter.  If I call the ExecuteDirect's in that second place as well, my problem goes away... Again, apologies... ---------------------------------------------------------------------- BigChimp · 2014-09-14 13:53 No problem, glad it got sorted and that wasn't my code (this time)