The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,689 other followers

Archive for the ‘Database Development’ Category

Static Code Analyzer for T-SQL – MS SQL Server. Plugs into MS SSMS and can al…

Posted by jpluimers on 2017/01/12

Static Code Analyzer for T-SQL – MS SQL Server.

Plugs into MS SSMS and can also be run from command line.It reports useful clues which you can turn/on off to your liking. http://sqlcodeguard.com/index-database-issues.html

It will spot declared but unused variables, but it appears it doesn’t do code coverage or execution path to spot stuff like variables being used without being initialized.

http://sqlcodeguard.com/ Price: Free

Source: Lars Fosdal on G+: Static Code Analyzer for T-SQL – MS SQL Server. Plugs into MS SSMS and can al…

–jeroen

Posted in Database Development, Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Firebird – date and time literals, conversion to ISO-8601 compatible strings

Posted by jpluimers on 2016/12/28

For my reference:

select 'Date literal values' as description
      , (cast('now' as timestamp)) as nowTimeStamp
      , (cast('today' as timestamp)) as todayTimeStamp
      , (cast('tomorrow' as timestamp)) as tomorrowTimeStamp
      , (cast('yesterday' as timestamp)) as yesterdayTimeStamp
      , cast(cast('Now' as date) as varchar(10)) as nowDateCast
      , replace(cast(cast('Now' as date) as varchar(10)), '-', '') nowDateIso8601
      , cast(cast('Now' as time) as varchar(13)) as nowTimeCast
      , replace(cast(cast('Now' as time) as varchar(13)), ':', '') nowTimeIso8601
      , cast(cast('Now' as timestamp) as varchar(24)) as nowTimeStampCast
      , substring(replace(replace(replace(cast(cast('Now' as timestamp) as varchar(24)), '-', '') , ' ', 'T'), ':', '') from 1 for 15) as nowTimeStampIso8601
from rdb$database 
;

Output and transposed output:

DESCRIPTION         NOWTIMESTAMP            TODAYTIMESTAMP          TOMORROWTIMESTAMP       YESTERDAYTIMESTAMP      NOWDATECAST NOWDATEISO8601 NOWTIMECAST   NOWTIMEISO8601 NOWTIMESTAMPCAST         NOWTIMESTAMPISO8601     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date literal values 3-8-2016 8:45:12        3-8-2016                4-8-2016                2-8-2016                2016-08-03  20160803       08:45:12.3860 084512.3860    2016-08-03 08:45:12.3860 20160803T084512         

DESCRIPTION             Date literal values
NOWTIMESTAMP            2016-08-03 8:45:12
TODAYTIMESTAMP          2016-08-03
TOMORROWTIMESTAMP       2016-08-04
YESTERDAYTIMESTAMP      2016-08-02
NOWDATECAST             2016-08-03
NOWDATEISO8601          20160803
NOWTIMECAST             08:45:12.3860
NOWTIMEISO8601          084512.3860
NOWTIMESTAMPCAST        2016-08-03 08:45:12.3860
NOWTIMESTAMPISO8601     20160803T084512

References:

–jeroen

Posted in Database Development, Development, Firebird | Leave a Comment »

Firebird [# CORE-3558] server reports that DB file is used by another application on secondary attachment attempt through a symlink

Posted by jpluimers on 2016/12/15

It took quite a while to find out what happened, but this is the underlying issue: [# CORE-3558] FB server reports that DB file is used by another application on secondary attachment attempt through a symlink – Firebird RDBMS Issue Tracker.

The error you get in Windows is this one: I/O error during “CreateFile (open)” “The process cannot access the file because it is being used by another process.”

When you look with Process Explorer or handle.exe (both from SysInternals) they will show exactly the same filename (the canonical name to which the SymLink points).

But when you look in the config files of the applications, the database connections point to different filenames. Either (or both) are a symlink themselves or have a directory in their path that is a symlink.

 

In my case it was the latter: one of the directories in the path had an mklink.exe created link to another directory that had a directory contained the final file. There is even an API function that gets the canonical file name: GetFinalPathNameByHandle function (Windows)

Firebird doesn’t use that API on Windows (probably because it’s only available as of Windows Vista and Windows Server 2008) but does use a similar POSIX function (so on Mac OS X and Linux you don’t get this error).

Note that the above error messages can also happen when you use an embedded connection to a database (i.e. a connection string without a network address). There it is normal you get an exclusive file lock.

–jeroen

via: ADO.NET – a problem with multiple connections to the database (Russian) with Google Translate

Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »

SQL Server, Modulo, floats

Posted by jpluimers on 2016/12/08

SQL server % (modulo, not mod) operator doesn’t like floats (with reason).

You should get rid of the floats as they will give inaccurate results.

As a workaround, cast either through an integer or through a decimal: sql server modulo float – Google Search

CAST(CAST(TheInaccurateFloatValue AS decimal(38,19)) % ModuloValue AS float)

The decimal(38,19) is the maximum non-float precision you get.

( cast(dividend as integer) % divisor ) + ( dividend - cast(dividend as integer))

–jeroen

Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Common Table Expressions: no nesting, but consecutively usage – via Stack Overflow

Posted by jpluimers on 2016/09/28

Common table expressions are awesome. They work in at least Oracle and SQL Server.

You cannot nest them, but you can use them consecutively. Thanks spender for explaining that:

WITH
x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y

–jeroen

via: sql – Can you create nested WITH clauses for Common Table Expressions? – Stack Overflow.

Posted in Development, Database Development, SQL Server, SQL Server 2008, SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, OracleDB, SQL Server 2014 | Leave a Comment »

 
%d bloggers like this: