MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL Server 2008’ Category

SQL Server XQuery

with 2 comments

I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.

You can query a sequence with xquery as follows:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query('(1,2,(10,11,12,13,14,15)),-6');

It returns:

1 2 10 11 12 13 14 15 -6

You can query a sequence with an xquery FLOWR statement. FLOWR stands for: FOR, LET, ORDER BY, WHERE, and RETURN. A sample xquery with a FLOWER statement is:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query('for $i in ((1,2,(10,11,12,13,14,15)),-6)
                 order by $i
                 return $i');

It returns:

-6 1 2 10 11 12 13 14 15

You can query the entire node tree with the following xquery statement because it looks for the occurrence of any node with the /* search string:

DECLARE @x xml;
SET @x = N'<marvel>
             <avenger_name>Captain America</avenger_name>
           </marvel>';
SELECT @x.query('/*');

You can query the avenger_name elements from the marvel_xml table with the following syntax:

SELECT xml_table.query('/marvel/avenger_name')
FROM   marvel_xml;

It returns the following set of avenger_name elements:

<avenger_name>Hulk</avenger_name>
<avenger_name>Iron Man</avenger_name>
<avenger_name>Black Widow</avenger_name>
<avenger_name>Thor</avenger_name>
<avenger_name>Captain America</avenger_name>
<avenger_name>Hawkeye</avenger_name>
<avenger_name>Winter Soldier</avenger_name>
<avenger_name>Iron Patriot</avenger_name>

You can query the fourth avenger_name element from the marvel_xml table with the following xquery statement:

SELECT xml_table.query('/marvel[4]/avenger_name')
FROM   marvel_xml;

It returns the following avenger_name element:

<avenger_name>Thor</avenger_name>

You can use the value() function to verify an avenger_name element exists:

SELECT CASE
          WHEN xml_table.value('(/marvel[4]/avenger_name)','nvarchar') = 'T' THEN
            'Exists'
       END AS "Verified"
FROM   marvel_xml
WHERE  id = 3;

The query returns the Exists string when it finds a valid avenger_name element. You have a number of other tools to query results sets from the XML node tree.

I hope this helps my students and anybody interested in writing xquery-enable queries.

Written by maclochlainn

February 19th, 2015 at 1:54 am

Insert into XML Column

with one comment

Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:

  • An XML Schema Collection type.
  • A table that uses an XML Schema Collection as a column’s data type.
  • An example on how you can transfer the contents of a table into the XML Schema Collection.

This post assumes you understand the basics about XML structures. If you’re unfamiliar with XML, please note that everything within it is case sensitive unlike SQL. You raise exceptions when the case of your XML fails to match the case of your XML Schema Collection definitions. I raised the following exception by using a Marvel element tag in title case when the XML Schema Collection uses a lowercase marvel element tag:

Msg 6913, Level 16, State 1, Line 2
XML Validation: Declaration not found for element 'Marvel'. Location: /*:Marvel[1]

The basic marvel table is defined in this earlier blog post. To ensure you don’t run into conflicts with previously existing objects, you can delete the marvel table with the following syntax:

1
2
IF OBJECT_ID('studentdb.marvel_xml','U') IS NOT NULL
  DROP TABLE marvel_xml;

There is no predefined function that lets you conditionally drop the XML Schema Collection from the data base. The alternative is to query the Microsoft SQL Server data catalog for the existence of a row before dropping the XML Schema Collection, like this:

1
2
3
4
IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MarvelXMLTable')
  DROP XML SCHEMA COLLECTION MarvelXMLTable;
ELSE
  SELECT 'Not found.';

You can now create the MarvelXMLTable XML Schema Collection with the following syntax:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE XML SCHEMA COLLECTION MarvelXMLTable AS
N'<?xml version="1.0" encoding="UTF-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
                schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Marvel">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="marvel_id" type="sqltypes:int" />
          <xsd:element name="avenger_name" type="sqltypes:nvarchar" />
          <xsd:element name="first_name" type="sqltypes:nvarchar" />
          <xsd:element name="last_name" type="sqltypes:nvarchar" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>';

After creating the XML Schema Collection, you can confirm whether it works correctly with the following statements. It will fail when you use semicolons on the DECLARE or SET lines, so avoid them as shown below:

1
2
3
4
5
6
7
8
DECLARE @marvel AS XML(MarvelXMLTable)
SET @marvel = '<marvel>
                 <marvel_id>1</marvel_id>
                 <avenger_name>Falcon</avenger_name>
                 <first_name>Sam</first_name>
                 <last_name>Wilson</last_name>
               </marvel>'
SELECT @marvel;

After creating and verifying the integrity of the XML Schema Collection, you can create a marvel_xml table. The xml_table column of the marvel_xml table uses a strongly-typed XML type, as shown:

1
2
3
CREATE TABLE marvel_xml
( id        INT IDENTITY(1,1) CONSTRAINT marvel_xml_pk PRIMARY KEY
, xml_table XML(MarvelXMLTable));

You can write an INSERT statement with single element like this:

1
2
3
4
5
6
7
8
9
INSERT INTO marvel_xml 
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>');

You can insert two elements with an INSERT statement like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO marvel_xml
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>
  <Marvel>
    <marvel_id>10</marvel_id>
    <avenger_name>Scarlet Witch</avenger_name>
    <first_name>Wanda</first_name>
    <last_name>Witch</last_name>
  </Marvel>');

There’s a lot of typing to insert XML literal values. It a lot easier to transfer relational data from a table to an XML type. Unfortunately, Microsoft didn’t make it very easy. The FOR XML AUTO doesn’t work for two reasons. First, the FOR XML AUTO clause doesn’t render the correct XML structure. Second, the INSERT statement disallows nested SELECT queries that include the FOR XML AUTO clause.

Here’s the way you insert relational data into an XML type column:

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @xml_elements NVARCHAR(MAX);
SET @xml_elements = N'';
WITH x AS
(SELECT CONCAT(N'<marvel>'
              ,N'<marvel_id>',marvel_id,N'</marvel_id>'
              ,N'<avenger_name>',avenger_name,N'</avenger_name>'
              ,N'<first_name>',first_name,N'</first_name>'
              ,N'<last_name>',last_name,N'</last_name>'
              ,N'</marvel>') AS element
 FROM   marvel)
SELECT @xml_elements += element FROM x;
INSERT INTO marvel_xml (xml_table)
VALUES (@xml_elements);

Line 1 declares an xml_element session variable. Line 2 initializes the xml_element session variable as an empty string. The WITH clause on lines 3 through 10 creates a Common Table Expression (CTE) with the valid XML structure for all rows in the marvel table. The nested SELECT statement on line 11 returns data from the CTE and adds returned row to the session-level string variable. Finally, the INSERT statement on lines 12 and 13 inserts the XML table into the xml_table column.

You can then query the table with the following statement:

SELECT xml_table
FROM   marvel_xml;

Click on the return type in the SQL Server Management Studio (SSMS), and the XML structure will expand to show this:

<marvel>
  <marvel_id>1</marvel_id>
  <avenger_name>Hulk</avenger_name>
  <first_name>Bruce</first_name>
  <last_name>Banner</last_name>
</marvel>
<marvel>
  <marvel_id>2</marvel_id>
  <avenger_name>Iron Man</avenger_name>
  <first_name>Tony</first_name>
  <last_name>Stark</last_name>
</marvel>
<marvel>
  <marvel_id>3</marvel_id>
  <avenger_name>Black Widow</avenger_name>
  <first_name>Natasha</first_name>
  <last_name>Romanoff</last_name>
</marvel>
<marvel>
  <marvel_id>4</marvel_id>
  <avenger_name>Thor</avenger_name>
  <first_name>Thor</first_name>
  <last_name>Odinsson</last_name>
</marvel>
<marvel>
  <marvel_id>5</marvel_id>
  <avenger_name>Captain America</avenger_name>
  <first_name>Steve</first_name>
  <last_name>Rogers</last_name>
</marvel>
<marvel>
  <marvel_id>6</marvel_id>
  <avenger_name>Hawkeye</avenger_name>
  <first_name>Clint</first_name>
  <last_name>Barton</last_name>
</marvel>
<marvel>
  <marvel_id>7</marvel_id>
  <avenger_name>Winter Soldier</avenger_name>
  <first_name>Bucky</first_name>
  <last_name>Barnes</last_name>
</marvel>
<marvel>
  <marvel_id>8</marvel_id>
  <avenger_name>Iron Patriot</avenger_name>
  <first_name>James</first_name>
  <last_name>Rhodey</last_name>
</marvel>

As always, I hope this post benefits those who read it.

Written by maclochlainn

February 18th, 2015 at 2:22 am

A T-SQL Table Function

with 3 comments

I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they’re available in Microsoft T-SQL User-Defined Functions (UDFs), my students wanted a small example. One of them said they’d tried to do it but couldn’t get it to work because they found the Microsoft web pages difficult to read and use. Specifically, they didn’t like the sparseness of this one on how to create a function.

Here’s a quick definition of a UDF table function that runs in the studentdb schema (created in this post for migrating SQL Server into a MySQL database). The following getConquistador function takes a single string, which acts to filter the result set from a query positioned as the return value of the function. You should note that this is an implementation of Microsoft’s Common Language Infrastructure (CLI).

CREATE FUNCTION studentdb.getConquistador
(@nationality AS VARCHAR(30))
RETURNS TABLE
RETURN SELECT * FROM studentdb.conquistador WHERE nationality = @nationality;

Unlike Oracle SQL, where you need to use the TABLE function to read the content of a table result from a function, you don’t need anything other than the function call in the FROM clause of a T-SQL query. Here’s an example of calling the table function:

SELECT * FROM studentdb.getConquistador('German');

The complete result from the query would produce these results when run from the sqlcmd command-line interface:

conquistador_id conquistador          actual_name          nationality
--------------- --------------------- -------------------- ------------
             11 Nicolas de Federman   Nikolaus Federmann   German
             13 Jorge de la Espira    George von Speyer    German
 
(2 rows affected)

However, you also have the ability to query only rows of interest without any specialized syntax, like this:

1> USE studentdb;
2> SELECT conquistador AS "Conquistador"
3> ,      actual_name AS "Name"
4> FROM   studentdb.getConquistador('German');
5> GO

This produces the following two-column result set:

Conquistador          Name
--------------------- --------------------
Nicolas de Federman   Nikolaus Federmann
Jorge de la Espira    George von Speyer
 
(2 rows affected)

Hope this helps those interested in T-SQL UDFs.

Written by maclochlainn

February 12th, 2013 at 1:16 am

Setup SQL Server Schema

with 7 comments

After you’ve installed Microsoft SQL Server 2012 and created a database schema, you still need to setup or configure the database schema. This is the recommended Microsoft SQL Server Best Practice.That means you must grant permissions to a user to work in the database schema, otherwise you get the following type of error message.

Msg 2760, Level 16, State 1, Line 1
The specified schema name "studentdb" either does not exist or you do not have permission to use it.

You receive the foregoing error message when trying to create a table in a database schema that hasn’t been assigned a user or role. This typically occurs after you create a new database schema, which has a <default> owner.

For those new to the idea of schemas (or scheme), they were introduced by Microsoft in SQL Server 2005. Here are some rules of thumb on schemas:

  • Database schema names are distinct from user names.
  • Multiple users may share a schema, which means it can hold objects owned by multiple users.
  • Permissions to act inside the schema may be granted to individual users, and you have a varied set of privileges that you can assign to users.
  • Ownership of objects in a schema can be transferred using the ALTER SCHEMA command.
  • Ownership of a schema can be changed by using the ALTER AUTHORIZATION command.
  • Database users can be dropped without changing objects in schemas, which is a big difference between Oracle and SQL Server.

The following example occurs when you try to create a table in a studentdb database schema. It doesn’t expose you to the SQL syntax but demonstrates how to manage the changes within the SQL Server Management Studio (SSMS).

By the way, you start the new schema creation process by right clicking on the Databases folder in the SQL Server Management Studio. Then, you give the new database schema a name in the New Database dialog; and click the OK button to complete the process.

The balance of the instructions show you how to create a user account that will map to your new database schema. It assumes you’ve installed it on your local machine and have privileges through local Windows Authentication to the System Administrator (sa) account. You create a student user account, assign the studentdb database schema, log off as the System Administrator, log in as the new user, conditionally drop a table from the studentdb schema, create a table in the studentdb schema, and query the results. It’s also possible to create the user first, database second, and assign the database as the default database for the user account.

  1. The first screen asks you to authenticate as the System Administrator using Windows Authentication. Click the Connect button to connect to the SQL Server Management System (SMSS).

 

  1. Click the Security folder and expand the subordinate list of folders. Right click on the Logins folder and click on the New Login option in the context menu. It launches a new dialog where you enter a user account.

 

  1. On the General page of the Login – New dialog, enter a Login name and click the SQL Server authentication radio button. Clicking the radio button enables the Password and Confirm password fields where you enter the same password twice. Click the Default database drop down and choose the studentdb created earlier. Click the Server Roles page to continue.

 

  1. On the Server Roles page, ensure that only the public server role is checked. Click on the User Mapping page to continue.

 

  1. On the User Mapping page, click the Map check box for the studentdb database, enter student in the User field, and enter studentdb in the Default Schema field. Click the db_owner and public database role membership for studentdb. Click on the Securables page to continue.

 

  1. There are no required changes on the Securables page. Check that screen matches the illustration at the left, and click on the Status page to continue.

 

  1. There are no required changes on the Status page. Check that screen matches the illustration at the left, and click on the OK button to complete creating the user. Then, exit the SQL Server Management Studio as the System Administrator.

 

  1. Launch the SQL Server Management Studio, choose SQL Server Authentication as the authentication method, and enter the student Login (user name) and Password before clicking the Connect button to continue.

 

  1. The first time a user logs in, they must re-enter a valid password. After entering the password twice, click the OK button to continue.

 

  1. The initial display shows only the Object Explorer. Click the New Query option in the menu toolbar.

 

  1. Click on the Databases folder to expand the list and you will see the studentdb database schema displayed, as shown in the illustration on the left.

 

  1. Enter a conditional drop of a conquistador table from the studentdb database schema. Click the Execute button from the toolbar menu to run the statement.
IF OBJECT_ID('studentdb.conquistador','U') IS NOT NULL
  DROP TABLE studentdb.conquistador;

  1. Enter a CREATE statement for a conquistador table in the studentdb database schema. Click the Execute button from the toolbar menu to run the statement.
CREATE TABLE studentdb.conquistador
( conquistador_id   INT  NOT NULL  IDENTITY(1,1) CONSTRAINT pk_conquistador PRIMARY KEY
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30));

  1. Enter an INSERT statement to the conquistador table in the studentdb database schema. Click the Execute button from the toolbar menu to run the statement and see that it inserted 9 rows.
INSERT INTO conquistador
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek')
,('Nicolás de Federmán','Nikolaus Federmann','German')
,('Sebastián Caboto','Sebastiano Caboto','Venetian')
,('Jorge de la Espira','Georg von Speyer','German')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian')
,('Fernando Consag','Ferdinand Konšcak','Croatian')
,('Américo Vespucio','Amerigo Vespucci','Italian')
,('Alejo García','Aleixo Garcia','Portuguese');

  1. Click the studentdb folder to expand content, and you should see the conquistador table.
SELECT * FROM conquistador;

  1. Click in the SQL Query frame and enter a query against the conquistador table. Click the Execute button to run the query and you should see the following output from the conquistador table.

 

As always, I hope this helps those trying to sort out how to solve a similar problem.

Written by maclochlainn

September 16th, 2012 at 1:00 am

Trying to Migrate Data

with 16 comments

Getting the MySQL Workbench’s Database Migration has been interesting, and at present incomplete. While I can now successfully connect to the SQL Server 2012 source database and capture a schemata list, migrating the data from SQL Server doesn’t work. Actually, the connection doesn’t work without modifying a Python library in the current MySQL Workbench release.

I blogged about the SQL Server 2012 installation and Windows SQL Server DSN setup last night because the development manager requested them to create a repeatable test case to help resolve Bug 66516. The existing blog post on the MySQL Workbench blog provides step-by-step instructions, so I passed on providing them. This post documents how far I’ve been able to get with the database migration and where I’m stuck.

MySQL Workbench 5.2.42, which is the current release at time of writing, doesn’t retrieve a schemata list from SQL Server 2008 or 2012 unless you edit one of the Python libraries. The instructions for the change are in Bug 66030.

You need to edit the db_mssql_grt.py source file and include the fix from the bug. While the bug lists where to find the file on Mac OS X, you find the db_mssql_grt.py source and compiled files are in the following Windows directory (on a 64-bit OS). Then, you delete the db_mssql_grt.pyc file, which is recompiled the next time you launch MySQL Workbench and there’s a call to a function in the module (or library).

C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\modules

You need to edit line 173, and add the CAST function call before you attempt the migration.

170
171
172
173
174
175
176
177
178
def getServerVersion(connection):
    """Returns a GrtVersion instance containing information about the server version."""
    version = grt.classes.GrtVersion()
    ver_string = execute_query(connection, "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)").fetchone()[0]
    ver_parts = [ int(part) for part in ver_string.split('.') ] + 4*[ 0 ]
    version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[0:4]
    return version
 
@ModuleInfo.export(grt.LIST, grt.classes.db_mgmt_Connection)

After editing the file and saving it, you need to delete the compiled version and start MySQL Workbench to generate the db_mssql_grt.pyc. If MySQL Workbench is running you need to shut it down after deleting the compiled Python file and restart it to generate a new compiled file.

Having fixed the error, you should see verification of your connection to the Microsoft SQL Server. If it fails at this point, you have made an error installing or configuring the Microsoft SQL Server or Windows DSN, or you haven’t made the change to the db_mssql_grt.py file.

 

All the other steps work except for copying the data from the Microsoft SQL Server to the MySQL Server. The failure occurs in Bulk Data Transfer segment of the Database Migration wizard. The first error occurs on the Determine number of rows to copy step. The error log didn’t help very much, so I generated the manual script file.

Testing the generated script manually it fails to connect to the SQL Server instance. It appears the command syntax in the generated script is incorrect or one or more of the choices made during the installation of SQL Server or definition of the Windows SQL Server DSN is incorrect.

Here’s the generated script file:

REM Workbench Table Data copy script
REM 
REM Execute this to copy table data from a source RDBMS to MySQL.
REM Edit the options below to customize it. You will need to provide passwords, at least.
REM 
REM Source DB: Mssql@SQL Server ODBC (Microsoft SQL Server)
REM Target DB: Mysql@mclaughlinsql:3306
 
 
REM Source and target DB passwords
REM set arg_source_password=
REM set arg_target_password=
REM Uncomment the following options according to your needs
 
REM Whether target tables should be truncated before copy
REM set arg_truncate_target=--truncate-target
REM Enable debugging output
REM set arg_debug_output=--log-level=debug3
 
wbcopytables.exe '--odbc-source=DSN=SQL Server ODBC;DATABASE=studentdb;UID=sa' --target=student@mclaughlinsql:3306  --source-password=%arg_source_password% --target-password=%arg_target_password% %arg_truncate_target% %arg_debug_output% --table '[dbo].[ORGANIZATION]' '`studentdb`' '`ORGANIZATION`'

You need to add the following directory to your %PATH% environment variable to test a call to wbcopytables executable:

SET PATH=%PATH%;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE

The following call to the wbcopytables executable requires you unremark the source and target password statements or set them externally from the script. This call should connect and migrate data from the Microsoft SQL Server database to the MySQL Workbench.

wbcopytables.exe --odbc-source='[DSN=SQL Server ODBC;DATABASE=studentdb;UID=sa]' --target=student@mclaughlinsql:3306  --source-password=cangetin --target-password=student   --table '[dbo].[ORGANIZATION]' '`studentdb`' '`ORGANIZATION`'

However, running it in my environment generates the following error:

wbcopytables.exe: Invalid option '--odbc-source=DRIVER={.\SQLEXPRESS};DSN=SQL

This was resolved by development in MySQL Workbench 5.2.43; and you can reference Bug 66516 for more information. Although, you need to watch out for character set mismatches, as qualified in this later post.

Written by maclochlainn

September 13th, 2012 at 1:25 am

Missing Features in R2

without comments

As I’ve mentioned before, I try to support Oracle, MySQL, and SQL Server in my database classes. When I downloaded SQL Server 2008 R2 Express Edition, I just downloaded the Database with Management Tools. That’s the one on the first page and shown on the left.

It appears what I really wanted was the Database with Advanced Services because I found basic features weren’t available in the Database with Management Tools version. So, you should click the Other Installation Options link. On that page you can choose between the various options.

For example, you can’t use an IF statement or local variable assignment. Also, the available data types are a subset of the mainline product. You can’t use a VARCHAR but must use the NVARCHAR. Many features of the Microsoft SQL Server Management Studio are removed too. A brief forum discussion shows that these types of problem exist in other versions too.

I thought a couple quick examples of raised exceptions would be helpful. They’re listed below.

Missing IF Syntax:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = IF ]

Local variable assignment Syntax:

DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = DECLARE ]

After I clear out the install and retry it with the other, I’ll update this as to whether or not these are available in the Database with Advanced Services SQL Server 2008 R2 Express Edition.

Written by maclochlainn

May 23rd, 2010 at 9:24 pm

SQL Aggregation Tutorial

without comments

I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.

My first take at the post …

This is a lesson on basic aggregation in SQL. Aggregation in SQL means counting, adding, and grouping by results of counts or sums. Aggregation is a critical part of using the SQL language. At a basic level, aggregation includes the COUNT, SUM, AVERAGE, MAX, and MIN aggregation functions; and the ORDER BY, GROUP BY, and HAVING clauses.

You’ll find the setup scripts for these examples at the bottom of this blog page. The best way to use this page is to copy the setup code, run it in your database, and then test the examples as you work though them.

Written by maclochlainn

February 26th, 2010 at 1:29 am

The class, they survived …

with 3 comments

The rumor is that my database is hard, but I’ve always hoped it was fun and laid the foundation of success for my students. As I walked into class to give them their final exam, they had their jackets on, which isn’t uncommon for Rexburg, Idaho in December. However, that’s normal outside but unusual in the heated classroom. Then, they all took their jackets off to show their new t-shirts.

They thought it would be fun to post on the blog, so here it is. The shirts says:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    class= CIT 320;

It’s unfortunate that they missed the enclosing quote marks around the string literal. 😉 It should be like this:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    i.class= 'CIT 320';

Here’s to a great group of students who know how to read, write, and think SQL. Any openings out there for internships, please drop me a note.

Happy holidays!

Written by maclochlainn

December 16th, 2009 at 4:42 pm

Agnostic String Comparisons

without comments

Oracle™ spoils us for other databases. We begin to expect too much of other database products. At least, that’s the way that I felt while working comparative syntax samples out for my students. I assumed wrongly that all strings would compare based on case sensitive strings. I found that Oracle does that, but MySQL and SQL Server don’t do that with an ordinary = (equals) comparison operator.

Oracle

The = (equals) symbol compares case sensitive strings. The following query returns nothing because a title case string isn’t equal to an uppercase string (databases, as they should be).

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

The query only resolves when the strings are case sensitive matches, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'String';

MySQL

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (a case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol. You may also chuckle that you need the DUAL pseudo table to make this work when there’s a WHERE clause, covered in yesterday’s blog.

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

You can make it case sensitive by using the strcmp function, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE strcmp('String','STRING');

You can also make it case sensitive by using collation, which is the ability to shift it’s character set. Unfortunately, MySQL doesn’t support casting to a binary string, so you must apply a different character set for the equality comparision.

SQL> SELECT 'Truth' AS OUTPUT
  2> FROM dual WHERE 'String' COLLATE latin1_bin = 'STRING' COLLATE latin1_bin;

SQL Server

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (another case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol.

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('STRING' AS VARBINARY);
3> GO

You can make SQL Server resolve case sensitive strings by casting both of the strings to VARBINARY data types, like this:

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('String' AS VARBINARY);
3> GO

There is a clear lack of portability for basic comparison operations. I think its time that folks drop that time worn database agnosticism line because all it means is I don’t want to use that other database. I’ve heard the line too often with regard to PL/SQL, but oddly not about MySQL’s stored procedures or Microsoft’s T-SQL. Maybe I’m an Oracle bigot but it sure seems more ANSI standard like to me to compare strings with a simple = (equals) operator.

Written by maclochlainn

October 28th, 2009 at 6:35 pm

SQL Automated Numbers

with 4 comments

I’ve begun putting together an online database tutorial and expanded this entry and added horizontal scrolling to it. You can find the improved version of the blog post as blog page here.

Surrogate keys are interesting structures in databases. They’re essential if you want to make sure you optimize your design. They’re also very useful when you want to capture the automatic numbering value for a prior INSERT statement and reuse the automatic numbering value as the foreign key value in a subsequent statement. It was interesting to see how they’re implemented differently across Oracle, MySQL, and SQL Server while providing the same utility.

Below is a synopsis of how you implement these in Oracle, MySQL, and SQL Server.

Oracle

The first thing to qualify is that Oracle is generally always in a transactional mode. That means you don’t need to do anything special to set this example up.

Oracle doesn’t support automated numbering in tables prior to Oracle 12c. Oracle 12c introduces identity columns, and the mechanics change. However, you can use sequences to mimic automated numbering prior to Oracle 12c and without identity columns in Oracle 12c. A sequence is a structure in the database that holds a current value, increments by a fixed value – typically 1. Sequences are available in SQL and PL/SQL scopes through two pseudo columns. The pseudo columns are .nextval and .currval (note the two r’s because it’s not a stray dog).

The sequence_name.nextval call in any session places the next number from the sequence into your Personal Global Area (PGA), which is a memory context. After you’ve called the sequence into memory, you can access it again by using sequence_name.currval. The sequence only changes when you call it again with the .nextval pseudo column.

-- Conditionally drop data sturctures - tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
			WHERE  TABLE_NAME IN ('ONE','TWO')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINT';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
			WHERE  sequence_name IN ('ONE_S1','TWO_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create base table and sequence.
CREATE TABLE one
( one_id   INT         NOT NULL CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE one_s1;
 
-- Create dependent table and sequence.
CREATE TABLE two
( two_id   INT         NOT NULL CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE two_s1;
 
-- Insert rows into the tables with sequence values.
INSERT INTO one VALUES (one_s1.nextval,'One!');
INSERT INTO one VALUES (one_s1.nextval,'Two!');
INSERT INTO two VALUES (two_s1.nextval, one_s1.currval,'Other Two!');
 
-- Display the values inserted with sequences.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

If you mimic automatic numbering with database triggers, you may not have access to the .currval value for the second INSERT statement. This occurs when you provide a NULL value expecting the trigger to manage .NEXTVAL call for you.

Transactions require that you keep the primary key value for the first table in a locally scoped variable for reuse. Then, you can pass it to the next INSERT statement. You do that with the .CURRVAL value.

You can make a potentially erroneous assumption that you’re the only user updating the table. Operating under that assumption, you can query the highest sequence number from the table before an insert, add one to it, and then attempt the INSERT statement. In a multi-user system, it’s possible that somebody beats you to the finish line with their INSERT statement. Your insert would then have a duplicate surrogate key value for the one_id column, and fail on an ORA-00001 error for a uniqueness violation on a primary key column.

A database trigger can help you avoid a race condition. The trigger would ensure sequence values are unique but it may also introduce problems. A common Oracle trigger with a pseudo automatic numbering paradigm is represented by the following trigger (found in APEX generated code).

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

Caution is required on this type of automated sequence trigger. There are two problems with this type of trigger.

One scenario is where you include a call to sequence_name.NEXTVAL in your INSERT statement. It then increments the sequence, and attempts to insert the value whereupon the trigger fires and repeats the behavior. Effectively, this type of logic creates a sequence that increments by one when you submit a null value in the values clause and by two when you submit a sequence_name.NEXTVAL value.

Another scenario occurs when you attempt a bulk INSERT operation on the table. The sequence call and substitution occurs on each row of the sequence.

You face another problem when you rewrite the trigger to only fire when a surrogate primary key isn’t provided, like this:

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
WHEN (NEW.one_id IS NULL)  -- Asynchronous with bulk insert operations when a value is provided by the bulk operation to the surrogate key column.
BEGIN
  :NEW.one_id := one_s1.nextval;
END;
/

This trigger design causes a problem only with bulk INSERT statements. Effectively, the sequence remains unaltered when you provide surrogate key values as part of inserting an array of values. The next non-bulk INSERT statement would then grab the .NEXTVAL value, attempt to use it, and raise a unique constraint violation because the bulk operation probably already used the value from the sequence.

The fix to bulk operations requires that you lock the table, disable a trigger like this, and get the .NEXTVAL value. Then, you assign the .NEXTVAL value to two local variables. One of these remains unchanged while the other increments as you populate the array for the bulk insert operation. After assigning the result from the .NEXTVAL, you drop the sequence and find the highest key value for the bulk insertion operation, add one to the highest key value, and store it in another locally stored variable. You perform the bulk insert operation and then recreate the sequence with a value one greater than the highest value in the table, which should already be in a locally scored variable. Don’t forget that you’d locked the table, so unlock it now.

You should note that database triggers run in a subshell with access only to the immediate shell that fired them. Therefore, you can’t set a bind variable in a SQL*Plus session and subsequently reference it inside the trigger body because it doesn’t have access to the variable.

MySQL

MySQL supports automatic numbering but not a default transactional mode like Oracle. You need to disable auto commit and start a transaction. You also need to assign the last automatic numbering value to a variable before using it in a subsequent INSERT statement. You must also provide an overriding list of mandatory columns when you opt to exclude the automated numbering column value. The one thing that we should all appreciate about MySQL is their desire to stay close to and comply with ANSI standards.

-- Conditionally drop the tables.
DROP TABLE IF EXISTS one;
DROP TABLE IF EXISTS two;
 
-- Create the tables with a surrogate key that automatically increments.
CREATE TABLE one ( one_id   INT PRIMARY KEY AUTO_INCREMENT
                 , one_text VARCHAR(20));
 
CREATE TABLE two ( two_id INT PRIMARY KEY AUTO_INCREMENT
                 , one_id INT
                 , two_text VARCHAR(20));
 
-- Start transaction cycle.
START TRANSACTION;
 
-- Insert first row, transfer auto increment to memory.
INSERT INTO one (one_text) VALUES ('One');
 
-- Assign last auto increment to local scope variable, the = works too.
SET @one_fk := last_insert_id();
 
-- Insert second row with auto increment and local scope variable.
INSERT INTO b (one_id, two_text) VALUES (@one_fk,'Two');
 
COMMIT;
 
-- Display the values inserted with auto incremented values.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

SQL Server

SQL Server supports automatic numbering but they call it the identity value. There are two ways to use it but the one I’m showing is for SQL Server 2005 or newer. You can replace the older @@identity for the SCOPE_IDENTITY() function call but Microsoft has already removed first level support from SQL Server 2000. While they’ve not said @@identity is deprecated, it sure appears that’s possible in a future release.

USE student;
 
BEGIN TRAN;
 
-- Conditionally drop tables when they exist.
IF OBJECT_ID('dbo.one','U') IS NOT NULL DROP TABLE dbo.one;
IF OBJECT_ID('dbo.two','U') IS NOT NULL DROP TABLE dbo.two;
 
-- Create auto incrementing tables.
CREATE TABLE one
( one_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE TABLE two
( two_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
-- Insert the values, and magically no override signature required.
INSERT INTO one VALUES ('One!');
INSERT INTO one VALUES ('Two!');
INSERT INTO two VALUES (SCOPE_IDENTITY(),'Other Two!');
 
-- Query the results.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;
 
COMMIT TRAN;

You should note that T-SQL doesn’t require an override signature when you use an automatic numbering column. This is different, isn’t it?

While the prior example works with two tables, it doesn’t scale to a series of tables. You should consider the following assignment pattern when you’ll have multiple last identity values in a single transaction scope.

DECLARE @one_pk AS INT;
SET @one_pk = SCOPE_IDENTITY();

As mentioned, this style is important when you’ve got a series of primary and foreign keys to map in the scope of a single transaction. Also, I’d suggest that you put all the declarations at the beginning of the transaction’s scope.

As always, I hope this helps some folks.

Written by maclochlainn

May 16th, 2009 at 11:04 pm