ANSI Standards – Database – Substring

Background

Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms.

Data

Our test table is Oracle’s SCOTT.EMP

Here is what the data looks like :-

oracle.scott.tiger.PNG

Substring/Substr

The HIREDATE is the only well formatted fixed length string.

Its format is DD-MMM-YY.

Query

Oracle

SQL


select 

           HIREDATE

        , SUBSTR(HIREDATE, 8, 2) as "YEAR"

        , SUBSTR(HIREDATE, 1, 2) as "DAY"

        , SUBSTR(HIREDATE, 4, 2) as "MONTH"

from   SCOTT.EMP 

Output

oracle.scott.tiger.HireDate.PNG

Explanation

  1. Oracle relies on Substr to extract portions of a string

SQL Server

Database :- WideWorldImporters

Table :- Sales.Customers

Outline

Here are the first five rows of the Sales.Customers table in WideWorldImporters.

sales.customers

Our effort is to separate out the Street Number and Street Name.

SQL

SELECT TOP (100) 

        [CustomerID]

      , [CustomerName]

      , [DeliveryAddressLine1]

      , [DeliveryAddressLine2]

      , case
            when (charindex(' ', [DeliveryAddressLine2]) > 0)
                then
                    substring
                    (
                          [DeliveryAddressLine2]
                        , 1
                        , charindex(' ', [DeliveryAddressLine2])
                    )
            else null
        end as [streetNumber]			

      , case
            when (charindex(' ', [DeliveryAddressLine2]) > 0)
                then
                    substring
                    (
                          [DeliveryAddressLine2]
                        , charindex(' ', [DeliveryAddressLine2]) +1
                        , 100
                    )
            else null
        end as [streetName]			

FROM [WideWorldImporters].[Sales].[Customers]

Output

sales.customers.deliveryAddress2.PNG

Explanation

  1. SQL Server’s Transact SQL relies on Substring to extract out portions of a string

Database Platforms

Vendor Database Platform Substr/Substring
IBM LUW substr and substring
Microsoft SQL Server substring
MySQL MySQL substr
Oracle Oracle substr
PostgreSQL PostgreSQL substring

Summary

Microsoft SQL Server Transact SQL and PostgreSQL uses substring.

MySQL and Oracle uses substr.

IBM/DB2 support both substr and substring.

References

  1. IBM
    • Db2
  2.  PostgreSQL
    • Documentation
      • Functions and Operators
        Link

Eclipse/Hibernate – Installation – Day/01

 

Outline

  1. Download & Install Eclipse
  2. JDBC Driver
    • Microsoft JDBC Driver
      • Download Microsoft JDBC Driver
  3. Configure Database
    • Microsoft SQL Server
      • Provision Database
        • WideWorldImporters ( Sample Database )
          • Restore Database
      • Provision Principal
        • Create SQL Server Principal
        • Grant Principal access to database
        • Grant principal access to database objects
  4. Configure Eclipse
    • Install Components
      • JBoss Tools
        • Hibernate

Tasks

Download & Install Eclipse

Please peruse this post per installing Eclipse :-

Eclipse IDE – Installation On MS Windows
Link

JDBC Drivers

Our database back-end is Microsoft SQL Server and so we will reach for Microsoft SQL Server JDBC Drivers.

Microsoft

SQL Server

JDBC Driver
Links
  1. Microsoft JDBC Driver for SQL Server
    Link
  2. Download Microsoft JDBC Driver for SQL Server
    Link
Available

Here are all the drivers available as of 2019-May-6th :-drivers.microsoft.jdbc.20190506.1231PM

We are on 7.0.

And, we will cover that in this post!

Database

Again, our database back-end is Microsoft SQL Server.  There are a few sample databases on that platform; the latest official is WorldWideImporters.

Microsoft

SQL Server

Worldwide Importers
Links
  1. SQL Samples
    Link
  2. Wide World Importers
    • Wide World Importers sample database v1.0
      Link
Brochure

Brochure

microsoft.sample.20190506.1246PM

Choice

We can go with traditional database backup files ( bak ).

Or with bacpac.

We chose :-

SQL Server 2016 SP1 (or later) Any Edition aside from LocalDB; SQL Server 2016 RTM (or later) Evaluation/Developer/Enterprise Edition
WideWorldImporters-Full.bak

Restore database

Launched SQL Server Management Studio and restored the backup file


USE [master]
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\'
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\'
go

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\downloads\Microsoft\SQLServer\Sample\WideWorldImporters\WideWorldImporters-Full.bak'
WITH
       FILE = 1
    ,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters.mdf'
    ,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_UserData.ndf'
    ,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\WideWorldImporters.ldf'
    ,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_InMemory_Data_1'
    ,  NOUNLOAD
    ,  STATS = 5
    ,  REPLACE

GO
Create Database Account

Let us create SQL Server Account


use [WideWorldImporters]
go

/*
    SQL Server Principal - helloDB
*/
if suser_id('helloDB') is null
begin

    create login [helloDB]
    with password = 'Nu3Hu5RuSt'
       , DEFAULT_DATABASE = [WideWorldImporters]
       , CHECK_EXPIRATION = OFF
       , CHECK_POLICY = OFF

end
go

/*
    SQL Server Principal - helloDB
*/
if user_id('helloDB') is null
begin

    create user [helloDB]
    from login [helloDB];

end
go

grant select, insert, update, delete on schema::[Application] to [helloDB]
go

grant select, insert, update, delete on schema::[Purchasing] to [helloDB]
go

grant select, insert, update, delete on schema::[Sales] to [helloDB]
go

grant select, insert, update, delete on schema::[Warehouse] to [helloDB]
go

jboss.org

Available Software

In the Work With entry box, please enter :-

http://download.jboss.org/jbosstools/photon/development/updates

Once entered please click the “Add…” button

install.AvailableSoftware.02.WorkWith.20190505.0225PM.PNG

Add Repository

Please give a Name for the Repository we are adding.

In our case we chose jboss.

install.AvailableSoftware.03.WorkWith.20190505.0225PM.PNG

 

Check the items that you wish to install

Using Web Services, jboss.org is reached.

Available Items are returned and displayed.

install.AvailableSoftware.05.AddRepository.20190505.0228PM.PNG

Item – jBoss Data Services Development

We selected the following items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.06.AddRepository.20190505.0229PM.PNG

Item – jBoss Data Services Development – Install Details

Please review the selected items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.07.AddRepository.20190505.0230PM.PNG

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

Item – Installing Software

 

Item – Security Warning

Security Warning – The authenticity or validity of the software cannot be established

Even Hibernate.org is shipping software without SSL Certificates?

 

It is good, please click “Install anyway”.

Restart

Please restart Eclipse ID to effect the changes.

Restarted

Upon restart, we are asked to opt in for anonymous usage statistics.

Summary

Enough shenanigans for Day 01.