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 :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring

Why you can’t be a good .Net Developer // Postgres and MySQL

Day Jobs Day jobs aside, there are some people that have had a thing or two to say in response to an Industry's rant on "Why you can't be a good .Net developer". Here are some of them.... Enrico, Italian programmer in Sweden. On Being a Good .NET Developer Link Ted Neward It is too possible … Continue reading Why you can’t be a good .Net Developer // Postgres and MySQL

Informatica – PowerCenter Express – Datatype – XML

Background As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.   Table Structure Here is our table structure. MSSQL MySQL   Informatica Original Table Structure Physical Data Object - Admin.CourseManagerUserLog Explanation: Later on, was cognizant that our XML Column, details, precision … Continue reading Informatica – PowerCenter Express – Datatype – XML

MySQL – Datatype – Data Definition Language (DDL) – GUIDs

Forward Unfortunately, MySQL does not currently have a native datatype support for GUIDs. Some of the other RDBMS, have concise native support for GUIDs. i.e MSSQL has uniqueIdentifier.   Lab From Googling, here is a workable workaround. DDL Create Table - datatypeGUID.person Outline GUIDs are 32 characters plus the separating hyphens ( - ) Code DDL … Continue reading MySQL – Datatype – Data Definition Language (DDL) – GUIDs

Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL ODBC Driver )

Background In our last post we spoke about copying data from MSSQL to MySQL via using MySQL JDBC Driver. In this post, we will cover using an ODBC Driver.   Lab ODBC Drivers Availability MySQL's ODBC Driver is available here. As of June 28th, 2016, the current MySQL ODBC Driver is 5.3.6. Our targeted machine's … Continue reading Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL ODBC Driver )

Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )

Background A dated follow-up to our post on using Informatica - PowerCenter Express. In that post we copied data between two MS SQL Server Instances. In this post we will copy data from SQL Server to MySQL.   Outline Here is a brief outline of the steps we will take: Select a Driver to use … Continue reading Informatica – PowerCenter Express – Data Sync – MSSQL to MySQL ( Using MySQL JDBC Driver )

MySQL :- Secondary Indexes and the Clustering Keys – Day 2

Preface In our last post, we spoke about how one might not need to add the Clustering columns when defining a Secondary Index. At that point, we touched on the fact that just reviewing the Query Plan via "Visual Explain" might not fully reveal whether the Index used is "covering" or whether the Clustered Index is … Continue reading MySQL :- Secondary Indexes and the Clustering Keys – Day 2

MySQL :- Secondary Indexes and the Clustering Keys

Preface RDMS Database tables can either be stored as a Heap or Clustered. When stored as a Heap, data is appended as they come in.  When Clustered, data is sequenced based on the Clustering Columns. Secondary Indexes For Clustered tables, the Clustering data is written as an additional data on each record.  On the other hand … Continue reading MySQL :- Secondary Indexes and the Clustering Keys

Database – Create Table Syntax in different Databases

Preface Wanted to cover a couple of databases and show by example how the "Create table" implementation differ.   Databases Couple of unfortunates: Will only cover two of the many database flavors Syntactically create table is very broad and so will not cover the many options We will only cover what it took to convert … Continue reading Database – Create Table Syntax in different Databases