The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 2,466 other followers

Archive for June 29th, 2021

SQL server: getting database names and IDs

Posted by jpluimers on 2021/06/29

A few statements go get database names and IDs based on these functions or system tables:

Part of it has the assumption that a master database always exists.

-- gets current database name
select db_name() as name
;
name
--------------------------------------------------------------------------------------------------------------------------------
acc

(1 row affected)
-- gets current database ID
select db_id() as dbid
;
dbid
------
5

(1 row affected)
-- gets all database IDs and names
select dbid,name from sys.sysdatabases
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master
5      acc

(2 rows affected)
-- gets current database name by ID
select db_name(db_id()) as name
;
name
--------------------------------------------------------------------------------------------------------------------------------
acc

(1 row affected)
-- gets case corrected database name for sys.sysdatabases.name having a case insensitive collation sequence
select dbid,name from sys.sysdatabases 
where name='Master'
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master

(1 row affected)
-- gets case corrected database name for sys.sysdatabases.name having a case sensitive collation sequence
select dbid,name from sys.sysdatabases 
where name = 'Master' collate Latin1_General_100_CI_AI
;
dbid   name
------ --------------------------------------------------------------------------------------------------------------------------------
1      master

(1 row affected)

Note that:

  • even though by default the SQL server collation sequence is case insensitive, it can make sense to do a case insensitive search, for example by using the upper function, specifying a collation, or casting to binary. I like upper the most, because  – though less efficient – it is a more neutral SQL idiom.
  • the most neutral case insensitive collation seems to be Latin1_General_100_CI_AI

Related:

  • [WayBack] SQL server ignore case in a where expression – Stack Overflow answered by Solomon Rutzky, summarised as:
    • Do not use upper as upper with lower does not always round-trip.
    • Do not use varbinary as it is not case insensitive.
    • Neither the = or like operators are case sensitive by default: both need a collate clause.
    • Find the collation of the column(s) involved; if it contains _CI, then you are done (it is already case insensitive); if it contains _CS, then replace that with _CI (case insensitive) and add that in a collate clause.
    • Collations are per predicate, so not per query, per table, per column nor per database. This means you have to specify them if you want to use a different one than the default.
  • [WayBack] What is Collation in Databases? | Database.Guide
    Latin1_General_100_CI_AI Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
  • [WayBack] Collation Info: Information about Collations and Encodings for SQL Server
  • [WayBack] SQL Instance Collation – Language Neutral Required:

    I recommend using Latin1_General_100_CI_AI. I recommend this because:

    1. If Latin1_General_CI_AI is supported, then there’s almost no chance thatLatin1_General_100_CI_AI (which is a far better choice) isn’t also supported. The version 100 collation has about 15,400 more sort weight definitions, plus 438 more uppercase/lowercase mappings. Not having those sort weights means that 15,400 more characters in the non-100 version equate to space, an empty string, and to each other. Not having those case mappings means that 438 more characters in the non-100 version return the character passed in (i.e. no change) for the UPPER() and LOWER() functions. There is no reason at all to want Latin1_General_CI_AI instead of Latin1_General_100_CI_AI. There might be a need if code was put into place to work around these deficiencies, and that code would behave incorrectly under the newer, better version of that collation. However, it’s highly unlikely that code was put into place to account for this, and extremely unlikely that if such code did exist, that it would error or doing things incorrectly due to the newer collation.
  • [WayBack] Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2) – Sql Quantum Leap
  • [WayBack] How to do a case sensitive search in WHERE clause (I’m using SQL Server)? – Stack Overflow answered by Jonas Lincoln:

    By using collation or casting to binary, like this:

    SELECT *
    FROM Users
    WHERE   
        Username = @Username COLLATE SQL_Latin1_General_CP1_CS_AS
        AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
        AND Username = @Username 
        AND Password = @Password 

    The duplication of username/password exists to give the engine the possibility of using indexes. The collation above is a Case Sensitive collation, change to the one you need if necessary.

    The second, casting to binary, could be done like this:

    SELECT *
    FROM Users
    WHERE   
        CAST(Username as varbinary(100)) = CAST(@Username as varbinary))
        AND CAST(Password as varbinary(100)) = CAST(@Password as varbinary(100))
        AND Username = @Username 
        AND Password = @Password 
  • [WayBack] sql – How to get Database name of sqlserver – Stack Overflow

–jeroen

Posted in Database Development, Development, Encoding, internatiolanization (i18n) and localization (l10), SQL Server | Leave a Comment »

Public traffic ticked vending machines can have good user experience.

Posted by jpluimers on 2021/06/29

At the bottom a few examples on on how not do do user experience.

Most of them are related to public traffic ticket vending machines, which seem to have a common pattern of having very low usability.

Good UX is possible

There is one example I know that has quite a good user experience, because taking usability into account aspart of the design was done at the start of the project.

This is contrary to most machines: they are built by engineers just taking into account their needs and challenges: build from existing parts, allowing for easier serviceability, aiming for ease of manufacturing.

Dutch GVB did it differently: they hired Dutch design agency [WayBack] Fabrique to design and test the user experience before the whole machine went into production.

This resulted in a machine that combines easy usability, good servicing, and straightforward manufacturing process. In addition, an “extended” version that allows for non-electronic payment was designed and manufactured in the same go.

[WayBack] Fabrique

Fabrique is a strategic design agency, specialised in service design, app development, e-commerce and website design. Discover Fabrique!

(Note I am not affiliated, nor endorsed by Fabrique. I just think they did a very good job)

Here are some pictures of the designed and manufactured machines; the vertical stripes light up the place where the next user interaction takes place:

Read the rest of this entry »

Posted in Development, Software Development, Usability, User Experience (ux) | Leave a Comment »

Keeping your ESXi infrastructure up-to-date: Subscribe to VMware ESXi Patch Tracker RSS Feed

Posted by jpluimers on 2021/06/29

[WayBack] Subscribe to VMware ESXi Patch Tracker RSS Feed has all the post-feedburner (see below) RSS links in a nice table:

Subscribe to VMware ESXi Patch Tracker RSS Feed

For popular web based readers:
Feedly [All] [6.7] [6.5] [6.0] [5.5] [5.1] [5.0]
My Yahoo! [All] [6.7] [6.5] [6.0] [5.5] [5.1] [5.0]
netvibes [All] [6.7] [6.5] [6.0] [5.5] [5.1] [5.0]
For manual subriptions with other web based or offline readers copy-and-paste these links:
manual [All] [6.7] [6.5] [6.0] [5.5] [5.1] [5.0]

Post-feedburner

Parts of feedburner have been deprecated since 2012: FeedBurner – Wikipedia.

There are all sorts of reports of feedburner being unstable, for instance:

–jeroen

Posted in ESXi5, ESXi5.1, ESXi5.5, ESXi6, ESXi6.5, ESXi6.7, Power User, Virtualization, VMware, VMware ESXi | Leave a Comment »

 
%d bloggers like this: