Sample Database – w3resource

Background

Looking for sample database model for a query that I had in mind.

Found a simple on at w3Resource.

 

w3Resource

The sample is title “SQL Exercises, Practice, Solution – exercises on employee Database”.

And, it is available here.

 

Model

Data

Employee

 

SQL

Data Definition Language ( DDL )

Employee

SQL


if schema_id('w3resource') is null
begin

    exec('create schema [w3resource] authorization [dbo]')

end
go

/*

    w3resource
    SQL employee Database [115 Exercise with Solution]
    https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php

*/

/*

    drop table [w3resource].[employee];

*/
if object_id('[w3resource].[employee]') is null
begin

    create table [w3resource].[employee]
    (
          [id]              int          not null
                            identity(1,1)

        , [empID]           int          not null

        , [empName]         varchar(60)  not null

        , [jobName]         varchar(60)  null

        , [managerID]       int          null

        , [hireDate]        date         null

        , [salary]          decimal(10,2) null

        , [commision]       decimal(7,2) null

        , [departmentID]    int          null

        , constraint [PK_w3resource.employee]
            primary key
            (
                [empID]
            )
        , constraint [FK_w3resource.employee.managerID]
            foreign key
            (
                [managerID]
            )
            references [w3resource].[employee]
            (
                [empID]
            )

   ) 

end   

go



 

Data Manipulation Language ( DML )

Employees

SQL



set nocount on
go

set XACT_ABORT on
go


/*

    w3resource
    SQL employee Database [115 Exercise with Solution]
    https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php

*/

delete 
from    [w3resource].[employee]
;

DBCC CHECKIDENT ('[w3resource].[employee]', RESEED, 0)  
go

insert into [w3resource].[employee]
(
      [empID]   
    , [empName] 
    , [jobName] 
    , [managerID]
    , [hireDate]
    , [salary]
    , [commision]
    , [departmentID]
)     
select   
      63819 as [empID]   
    , 'Kayling' as [empName] 
    , 'President' as [jobName] 
    , null as [managerID]
    , '1991-11-18'
    , 6000
    , null
    , 1001


union all

select   
      66928 as [empID]   
    , 'Blaze' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-05-01'
    , 2750
    , null
    , 3001

union all

select   
      67832 as [empID]   
    , 'Clare' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-06-09'
    , 2550
    , null
    , 1001

union all

select   
      65646 as [empID]   
    , 'Jonas' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-04-02'
    , 2957.00
    , null
    , 2001

union all

select   
      67858 as [empID]   
    , 'Scarlet' as [empName] 
    , 'Analyst' as [jobName] 
    , 65646 as [managerID]
    , '1997-04-19'
    , 3100.00
    , null
    , 2001


union all

--Frank
select   
      69062 as [empID]   
    , 'Frank' as [empName] 
    , 'Analyst' as [jobName] 
    , 65646 as [managerID]
    , '1991-12-03'
    , 3100.00
    , null
    , 2001


union all

-- Sandrine
select   
      63679 as [empID]   
    , 'Sandrine' as [empName] 
    , 'Clerk' as [jobName] 
    , 69062 as [managerID]
    , '1990-12-18'
    , 900
    , null
    , 2001

union all

-- Adelyn
select   
      64989 as [empID]   
    , 'Adelyn' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-02-20'
    , 1700
    , 400
    , 3001


union all

-- Wade
select   
      65271 as [empID]   
    , 'Wade' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-02-22'
    , 1350.00
    , 600.00
    , 3001

union all
-- Madden
select   
      66564 as [empID]   
    , 'Madden' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-09-28'
    , 1350.00
    , 600.00
    , 3001

union all

-- Tucker
select   
      68454 as [empID]   
    , 'Tucker' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-09-08'
    , 1600.00
    , 0.00
    , 3001

union all

-- Adnres
select   
      68736 as [empID]   
    , 'Adnres' as [empName] 
    , 'Clerk' as [jobName] 
    , 67858 as [managerID]
    , '1997-05-23'
    , 1200.00
    , null
    , 2001

union all

-- Julius
select   
      69000 as [empID]   
    , 'Julius' as [empName] 
    , 'Clerk' as [jobName] 
    , 66928 as [managerID]
    , '1991-12-03'
    , 1050.00
    , null
    , 3001

union all

-- Marker
select   
      69324 as [empID]   
    , 'Marker' as [empName] 
    , 'Clerk' as [jobName] 
    , 67832 as [managerID]
    , '1992-01-23'
    , 1400.00
    , null
    , 1001

go

 

Source Control

GitHub

DanielAdeniji/w3ResourceDBSample
Link

Transact SQL :- List Default Constraints

Background

List SQL Server Default Constraints.

Metadata

If one decides to use Sequences, one has to be able to document where it is being used.

Identity Columns

Identify Columns has self documenting tooling.

Inclusive :-

  1. sys.columns
    • is_identity
  2. sys.identity_columns
  3. OBJECTPROPERTY
    • TableHasIdentity
  4.  COLUMNPROPERTY
    • IsIdentity

Sequence

There is really nothing like a Sequence Column.

They are defined outside of a table definition.

It is not intrinsic so to speak.

It is a service that can be used as a communal toolset.

Outline

To detect use we can ask :-

  1. sys.columns
  2. sys.default_constraints
  3. sys.sql_expression_dependencies

Code


 if SUSER_ID('rdsa') is null
 begin

    use [master]

 end
 go

 if object_id('[dbo].[sp_helpConstraintDefault]') is null
 begin

    exec('create procedure [dbo].[sp_helpConstraintDefault] as ')

 end
 go

 alter procedure [dbo].[sp_helpConstraintDefault]
 as
 begin

   select 

              [schema]
                = tblSS.[name]

            , [object]
                = tblSO.[name]

            , [column]
                = tblSC.[name]

            , [columnType]
                = tblST.[name]

            , [is_identity]
                = tblSC.is_identity

            , [is_rowguidcol]
                = tblSC.is_rowguidcol

            , [columnDefault]
                = tblSDC.[name]

            , [definition]
                = tblSDC.[definition]

            , [referencedObject]
                = quotename
                    (
                        tblSORefS.[name]
                    )
                    + '.'
                    + quoteName
                        (
                            tblSORef.[name]
                        )

            , [referencedObjectType]
                = tblSORef.[type_desc]

    from   sys.columns tblSC

    inner join sys.types tblST

        on  tblSC.system_type_id = tblST.system_type_id

        and tblSC.user_type_id = tblST.user_type_id

    inner join sys.objects tblSO

        on tblSC.[object_id] = tblSO.[object_id]

    inner join sys.schemas tblSS

        on tblSO.[schema_id] = tblSS.[schema_id]

    inner join sys.default_constraints tblSDC

        on  tblSC.object_id = tblSDC.parent_object_id

        and tblSC.column_id = tblSDC.parent_column_id

    left outer join sys.sql_expression_dependencies AS tblSSED 

        on tblSC.[default_object_id] = tblSSED.referencing_id

    left outer join sys.objects tblSORef

        on tblSSED.referenced_id = tblSORef.[object_id]

    left outer join sys.schemas tblSORefS
        on tblSORef.[schema_id] = tblSORefS.[schema_id]

    order by

              tblSS.[name]

            , tblSO.[name]

            , tblSC.[name]

 end
 go

 if SUSER_ID('rdsa') is null
 begin

    exec sp_MS_marksystemobject '[dbo].[sp_helpConstraintDefault]'

 end
 go

Output

Output – Sequence – #1

Output – Database – Idf – #1

 

Source Control

GitHub

Repository

DanielAdeniji/dbo.sp_helpConstraintDefault
Link

Transact SQL :- Sequence Object

Background

As I read more about Hibernate, reminded once again the usage of Identity Columns to implement Sequences is quite unique to SQL Server and likely Sybase.

And, that Oracle, for one, uses Sequence to generate increasing numbers.

Hibernate

hibernate.IdGeneratorStrategy

Sample Code

Outline

  1. Schema
    • Does Schema exists?
      • schema_id()
    • If Schema does not exist?
      • Create Schema
  2. Sequence
    • Does Sequence exists?
      • Consult sys.sequences
    • If Sequence does not exist
      • Create Using “CREATE SEQUENCE”
  3. Utilize Sequence Generator
    • Add Data
      • Get next available Sequence
        • Syntax
          • NEXT VALUE FOR [sequence object]
        •  Sample
          • NEXT VALUE FOR [lab].[seq]
  4. Review Sequence Objects
    • sys.sequences
      • name
      • is_cached
      • increment
      • current_value
      • is_exhausted
      • maximum_value

Code


use [tempdb]
go

set nocount on
go

set XACT_ABORT on
go

declare @commit bit

declare @object  sysname

declare @schema_id int
declare @schema    sysname
declare @sequence  sysname

set @commit = 0

set @schema = 'lab'
set @sequence = 'seq';

begin tran

    /*
        Get schema_id for schema @schema
    */
    set @schema_id = schema_id(@schema);

    /*
        If schema_id is null, then does not exist
        create it
    */
    if @schema_id is null
    begin

        exec('create schema [lab] authorization [dbo]')

    end

    set @schema_id = schema_id(@schema);

    /*
        If schema does not exist, please create it
    */
    if not exists
    (
        select *
        from   sys.sequences
        where  [name] = @sequence
        and    [schema_id] = @schema_id
    )
    begin

        CREATE SEQUENCE [lab].[seq]
        AS INTEGER
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        --MAXVALUE 1000000
        CYCLE
        ; 

    end

    /*
        Please create table lab.vehicle
    */
    if object_id('[lab].[vehicle]') is null
    begin

        create table [lab].[vehicle]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]
        )

    end

    /*
        Please create table lab.bridge
    */
    if object_id('[lab].[bridge]') is null
    begin

        create table [lab].[bridge]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]

            , [name] nvarchar(80) not null

        )

    end

    /*
        Insert data
    */
    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Seven Mile Bridge');

    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Royal Gorge Bridge and Park');

    insert into [lab].[bridge]([name]) values('New River Gorge Bridge');

    /*
        Review data
    */
    select *

    from   [lab].[vehicle]

    select *

    from   [lab].[bridge]

    /*
        Review Sequence
    */
    select
              [schema] = tblSS.[name]
            , tblSeq.[name]
            , tblSeq.is_cached
            , tblSeq.increment
            , tblSeq.current_value
            , tblSeq.is_exhausted
            , tblSeq.maximum_value

    from   sys.sequences tblSeq

    inner join sys.schemas tblSS

            on tblSeq.[schema_id] = tblSS.schema_id

    where  tblSeq.[name] = @sequence
    ;

while (@@TRANCOUNT>0)
begin

    if (@commit =1)
    begin

        commit tran;

    end
    else
    begin

        rollback tran;
    end
end

Output

sequence.lab.20190512.1134PM

Hibernate – SQL Server – Exercise – 01

Background

Wanted to share source code for a sample helloDB Script that I am trying to use as I self teach Hibernate.

Outline

  1. Database File
    • DDL
      • Table
        • dbo.Country
  2. Java Source File
    • helloDB.java
  3. Hibernate Configuration File
    • SQLServerSavila.cfg.xml
  4. Entity
    • Country
      • Country.java ( POJO File )
      • Country.hbm.xml ( XML )

Source Code

Database

DDL

Table

Country
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [sakila]
GO

/*
    drop table [dbo].[country];
*/

/*
    truncate table [dbo].[country];
*/

/*
    select *
    from [dbo].[country];
*/
IF NOT EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[country]')
    AND type in (N'U')
)
BEGIN

    CREATE TABLE [dbo].[country]
    (
        [country_id] [smallint] NOT NULL,
        [country] [varchar](50) NOT NULL,
        [last_update] [datetime] NULL

        , constraint [dbo.PK_Country]
          primary key
        (
            [country_id]
        )
    )
    ON [PRIMARY]

END
GO

Source Code

helloDB.java

package sakila;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.Transaction;

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;
import java.util.Set;

public class helloDB
{

	Configuration objConfiguration = null;

	static String CONFIGURATION_FILE_SQLSERVER = "SQLServerSakila.cfg.xml";

	SessionFactory factoryMSSQL = null;

	static String CHAR_TAB = "\t";

	public static void main(String[] args)
	{

		// TODO Auto-generated method stub
		run();

	}

	public static void run()
	{

			helloDB objHelloDB = null;

			objHelloDB = new helloDB();

			try
			{
				objHelloDB.dbWork();
			}
			catch (Exception ex)
			{

				System.out.println(ex.getMessage());

			}

			objHelloDB =null;

	}

    private Session getDBSession()
    {
    	Session session = null;

    	Configuration cfg= new Configuration();

    	cfg.configure(CONFIGURATION_FILE_SQLSERVER);

    	factoryMSSQL
			= cfg.buildSessionFactory();

    	session = factoryMSSQL.openSession(); 

    	//session = factoryMSSQL.getCurrentSession(); 

    	return session;

    }

    void dbWork()
	//throws Exception
    {

    	try
    	{

    		Session session = null;

    		session = getDBSession();

	    	entityList(session);

	    	countryAddList(session);

	    	countryList(session);

	    	session.close();

	    	factoryMSSQL.close();

    	}
		catch (Exception ex)
		{

			System.out.println(ex.getMessage());

			System.err.println("Failed to create sessionFactory object." + ex);

			throw new ExceptionInInitializerError(ex);
		}

    }

    // list all objects
    private void countryList(Session session)
    {

    	List lists;
    	Country       objCountry;

    	String strLog;

		System.out.println("List Countries");

		System.out.println("==============");

    	lists = session.createQuery("from Country").list(); 

    	System.out.println(lists.size()); 

    	for (int i = 0; i < lists.size(); i++)
    	{ 

			objCountry = lists.get(i);

			strLog = CHAR_TAB
						+ objCountry.getCountry()
						+ " "
						+ "("
						+ objCountry.getCountryID()
						+ ")";

			System.out.println(strLog);

    	}	

    }

    private void countryAddList
    (
		Session session
	)
	throws Exception
    {

    	countryAdd(session, 1, "United States of America");
    	countryAdd(session, 33, "France");
    	countryAdd(session, 44, "United Kingdom");
    	countryAdd(session, 55, "Brazil");
    	countryAdd(session, 61, "Australia");

    }

    private void countryAdd
    (
		  Session session
		, int countryID
		, String country
	)
	throws Exception
    {
        try
        {

        	Transaction transaction = session.getTransaction();

            transaction.begin();

            Country objCountry = new Country();

            objCountry.setCountryID(countryID);
            objCountry.setCountry(country);
            objCountry.settsUpdate(new Timestamp(Calendar.getInstance().getTime().getTime()));

            session.persist(objCountry);

            transaction.commit();

        }

    	catch (Exception ex)
    	{

			System.err.println("Failed to Add country " + country);

			System.out.println(ex.getMessage());

            if (session.getTransaction().isActive())
            {
                session.getTransaction().rollback();
            }

        }

	}

    private void entityList
    (
		  Session session
	)
	//throws Exception
    { 

    	String strEntity;
    	int iNumberofElements;
    	Set objEntityTypeList;

    	try
        {

            objEntityTypeList = session.getMetamodel().getEntities();

            iNumberofElements = objEntityTypeList.size();

        	System.out.println("Retrieve entities ...."); 

        	System.out.println(CHAR_TAB + "Number of elements is " + iNumberofElements); 

        	Transaction transaction = session.getTransaction();

            transaction.begin();

            for (javax.persistence.metamodel.EntityType objEntityType:  session.getMetamodel().getEntities())
            {

            	strEntity = objEntityType.toString();

            	System.out.println(CHAR_TAB + CHAR_TAB + "entity name "+strEntity); 

            }

        	System.out.println("Entities Retrieved"); 

            transaction.commit();

        }

    	catch (Exception ex)
    	{

    		System.out.println(ex.getMessage()); 

            if (session.getTransaction().isActive()) {
                session.getTransaction().rollback();
            }

            //throw ex;
        }

	}

}

Hibernate Configuration File

SQLServerSavila.cfg.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>

  <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

  <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>

  <property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;databaseName=sakila;</property>

  <property name="hibernate.connection.username">sakila</property>

  <property name="hibernate.connection.password">VecRadrI3h</property>

  <!-- JDBC connection pool (use the built-in) -->
  <property 
  	name="connection.pool_size">
  	1
  </property>

  <property name="current_session_context_class">thread</property>

  <!-- Disable the second-level cache  -->
  <property 
  	name="hibernate.cache.use_second_level_cache"> 
  	false
  </property>

  <!-- Echo all executed SQL to stdout -->
  <property name="show_sql">true</property>
  
  <!-- Drop and re-create the database schema on startup -->
  <!-- validate schema on startup -->
 
  <property name="hbm2ddl.auto">validate</property>
  
  <property name="hibernate.default_schema">dbo</property>

 
  <mapping 
  	resource="sakila/Country.hbm.xml"
  	/>
 
  <!-- 
  	 <mapping class="sakila.Employee"/>
	-->
 
 </session-factory>
</hibernate-configuration>

Entity

Country

Country.java ( POJO File )

package sakila;

/*
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
*/

import java.sql.Timestamp;

public class Country
{

	private int CountryID;
	private String Country;
	private Timestamp tsUpdate;

	public Country()
	{

	}

	public Timestamp gettsUpdate()
	{
		return tsUpdate;
	}

	public void settsUpdate(Timestamp tsUpdate)
	{
		this.tsUpdate = tsUpdate;
	}

	public int getCountryID()
	{
		return CountryID;
	}

	public void setCountryID(int countryID)
	{
		CountryID = countryID;
	}

	public String getCountry()
	{
		return Country;
	}

	public void setCountry(String country)
	{
		Country = country;
	}

}

Country.hbm.xml ( HBM.XML File )

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping 
	PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping/hibernate-mapping-3.0.dtd"
>
<hibernate-mapping>

 <class 
 	name="sakila.Country" 
 	schema="dbo" 
 	table="country"
>


  <id 
  		column="country_id" 
  		name="CountryID" 
  		type="short"
  		/>

  <property 
  		column="country" 
  		generated="never" 
  		lazy="false"
   		name="Country" 
   		type="string"
   		/>
   
  <property 
  		column="last_update" 
  		generated="never" 
  		lazy="false"
   		name="tsUpdate" 
   		type="timestamp"
   		/>
   		
 </class>
</hibernate-mapping>

Source Code Control

GitHub

DanielAdeniji/javaHibernateSQLServerSakila
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.

 

 

SQL Server Agent on AWS/RDS

Background

There is quite a bit of SQL Server management surface area that is not available when running on AWS/RDS.

 

Surface Area

For instance with SQL Server Agent :-

  1. SQL Server Agent
    • Category
      • Add new category
        • API
          • sp_add_category
        • Error
          • Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_add_category
          • The EXECUTE permission was denied on the object ‘sp_add_category’, database ‘msdb’, schema ‘dbo’.
    • Job Step
      • Fetch Job Steps
        • API
          • SELECT * FROM msdb.dbo.sysjobsteps
        • Error
          • Msg 229, Level 14, State 5, Line 71
          • The SELECT permission was denied on the object ‘sysjobsteps’, database ‘msdb’, schema ‘dbo’.

 

Scripting

In terms of scripting, SQL Server Agent is not accessible in an AWS/RDS Environment.

XQuery/Transact SQL – Case Sensitivity

Background

When issuing XPath queries, XQuery in SQL, one has to keep in mind that though the database itself could be defined as case-insensitive, XML is case-sensitive.

 

Create & Populate Data

Create Table

SQL


declare @tbl TABLE
(
	[id] smallint not null
		identity(1,1)

	, [xml] xml
)

Populate Table with data

SQL


insert into @tbl
(
	[xml]
)
select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

union all

select ''

Data

 

Queries

  1. Normal Queries
    • Fetch all data
    • Fetch data whose part name is Teeth
  2. Function – fn:lower-case
    • Fetch all data
    • Fetch data whose part name is Teeth ( Case In-Sensitive )

Normal Query

Fetch all Records

Code


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'nvarchar(max)'
                ) 

from   @tbl

Output

Body Part = teeth


select
          [src] = 'name=teeth'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[@name="teeth"])'
        )
        = 1		 

 

Output

 

Function – Lower Case

Read All Records

SQL


select
          [src] = 'all'

        , *

        , [count]
            = [xml].value
                (
                    '(/body/part/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteeth]
            = [xml].value
                (
                    '(/body/part[@name="teeth"]/@count)[1]'
                    , 'smallint'
                ) 

        , [countOfteethCI]
            = [xml].value
              (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
              )

from   @tbl

Output

Body Part = teeth

SQL


select 

          [src] = 'name=teeth (lowercase)'

        , *

        , [countofteeth]
            = [xml].value
            (
                '(/body/part[fn:lower-case(@name)="teeth"]/@count)[1]'
                , 'smallint'
            ) 

from   @tbl

where  [xml].exist
        (
            '(/body/part[fn:lower-case(@name)="teeth"])'
        )
        = 1

Output

SQL Server Version

XML datatype and XQuery was introduced in SQL Server v2005.

But, v2005, programmability support was limited.

Some String functions such as lower-case and upper-case were not introduced till v2008.

 

Summary

You know your data.

If you suspect it might be mixed-case, please take “precausive” measures.

 

References

  1. Stackoveflow
    • Kevin Aenmey
      • how to perform a case-insensitive attribute selector in xquery
        Link
  2. Microsoft
    • XQuery
      • XQuery Functions against the xml Data Type
        Link
      • Functions on String Values – lower-case
        Link
  3. IBM
    • IBM Knowledge Center
      • Home > IBM i 7.1 > Database Programming > SQL XML programming > Overview of XPath > Descriptions of XPath functions
        • fn:lower-case function
          Link
  4. W3.Org
    • Xpath Functions
      • XQuery, XPath, and XSLT Functions and Operators Namespace Document
        Link