Saturday, March 29, 2008

Postgres East 2008 Conference

I'm in Baltimore this weekend at the Postgres East 2008 conference. I'm giving a presentation tomorrow on SQL/XML for developers. Below is the presentation and below that is the code used in the presentation. Code to follow along: If you aren't using javascript, you can get download the presentation or the script from scribd. Or, to make it easy, just grab this code:

Slide 25

CREATE TABLE EMP
(
  LAST_NAME text,
  EMP_ID integer NOT NULL,
  FIRST_NAME text,
  DEPT_ID integer,
  SALARY integer,
  CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID)
)
WITH (OIDS=FALSE);
ALTER TABLE EMP OWNER TO xmluser;


Slide 26

INSERT INTO 
    EMP(
       LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)
    VALUES
      ('Blow', 1, 'Joe', 1, 10000),
      ('Head', 2, 'Helmut', 1, 12000),
      ('Jack', 3, 'Noe', 1, 12000),
      ('Hard', 4, 'Blow', 2, 20000),
      ('First', 5, 'Hugo', 2, 21000),
      ('Spaem',6, 'Kingoof', 2, 20000),
      ('Ventura', 7, 'Ace', 3, 35000),
      ('Nickleby', 8, 'Nick', 3, 400000),
      ('Budd', 9, 'Billy', 4, 99000),
      ('Cleaver', 10, 'Wally', 4, 100000) ;


Slide 29

SELECT xmlelement(name main, last_name) from emp;

Slide 30

SELECT xmlelement(name main, last_name),
       xmlelement(name main, first_name)
FROM emp;


Slide 31

SELECT XMLForest(last_name, first_name)
FROM emp;

Slide 32

SELECT xmlelement(name main, 
       xmlforest(last_name, first_name) )
FROM emp;

Slide 33

SELECT XMLAgg(
  XMLForest(last_name, first_name) )
FROM emp;


Slide 34

SELECT XMLElement(name main, 
       XMLAgg(XMLForest(last_name, first_name) ))
FROM emp;


Slide 35

SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;


Slide 36

CREATE TEMP SEQUENCE t_seq;

SELECT xmlelement(name main, xmlattributes(nextval('t_seq') AS rownum),
       xmlforest(last_name, first_name) )
FROM emp;

DROP SEQUENCE t_seq;

Slide 38

SELECT xmlelement(name main, 
       xmlforest(last_name || first_name AS fullname, salary) )
FROM emp;


Slide 39

SELECT xmlelement(name main,
       xmlelement(name fullname, xmlattributes(dept_id), last_name || first_name ),
       xmlforest(salary) )
FROM emp;



Slide 40

SELECT xmlelement(name main, xmlcomment('Each Name Goes Here'),
       xmlforest(last_name, first_name))
FROM emp;


Slide 41

SELECT xmlelement(name lastname, last_name),
       xmlelement(name firstname, first_name)
FROM emp;

Slide 42

SELECT xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) )
FROM emp;

Slide 43

SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp;

Slide 44

CREATE TABLE xmltab (col1 XML);

INSERT INTO xmltab ( 
  SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp);

Slide 45

SELECT * FROM xmltab;

Slide 47

SELECT xpath('/main/firstname/text()', col1) FROM xmltab;

Slide 48

SELECT textcol[1] FROM (
  SELECT xpath('/main/firstname/text()', col1) AS textcol
    FROM xmltab ) AS xmlsource;

Slide 49

select query_to_xml('select * from emp', TRUE, TRUE, 'scott');

Slide 50

select table_to_xml('emp', TRUE, TRUE, 'scott');

Wednesday, March 26, 2008

32 Bit ODBC Drivers in Vista 64

This post isn't really PostgreSQL specific. It also happens with MySQL and Ingres (and any other 32 bit driver).

I am running Vista 64 on one of my machines. I installed Postgres 8.3 and the ODBC drivers. When I tried to setup an ODBC connection, the windows ODBC administrator program ODBCAD32.exe did not list Postgres. I noticed that it also did not list MySQL or Ingres even though I knew I had installed drivers for those databases also.

After a bit of research, I tracked down the issue. My ODBC drivers did in fact install. Microsoft, in all their wisdom, has 2 versions of odbcad32. A 32 bit and a 64 bit. They are both named odbcad32.exe.

It gets better. They put the 32 bit odbcad32.exe in the syswow64 directory. They put the 64 bit odbcad32.exe in the system32 directory. 32 bit apps will pick up the 32 bit registry setting and 64 bit will pick up the 64 bit registry setting. system32 comes before syswow64 in the system path so the 64bit software runs before the 32 bit software.

So, when I manually ran odbcad32.exe in the syswow64 directory, I was able to configure my connections and everything worked hunky dory. I know have a shortcut to syswow64\odbcad32.exe on my desktop for working with 32 bit databases.

I wonder how stable the system would be if syswow64 was in the path before system32? I don't plan to find out.

LewisC


Tuesday, March 4, 2008

Oracle #1 but Postgres Gaining Ground

I just read this in InfoWeek: Database Survey Gives Oracle The Lead In All 13 Categories The survey compared Oracle with DB2, MySQL, Informix Dynamic Server, PostgreSQL, Microsoft SQL Server, and Sybase Advanced Server Enterprise. The significant parts of that are that Postgres was included (we don't see enough of that) and that Postgres performed exceptionally well. Some important quotes:

In a few categories, the open source system, MySQL, trailed most of the five commercial systems with which it was compared. But high user satisfaction in several categories indicates thatSun Microsystems (NSDQ: JAVA) may have gotten its money's worth when it paid $1 billion for the database's parent company, MySQL AB, last month. MySQL was second only to Oracle in multiplatform support, an important factor in hosting Web applications. When it came to the all important "performance" category, it ranked higher than Microsoft SQL Server, Informix, and Sybase.
Is MySQL really supported on more platforms than Postgres?
In scalability, Oracle lead, followed by DB2, then PostgreSQL took over the commanding position of the open source systems, followed by SQL Server, Informix, Sybase, and MySQL.
Oracle lead the security category, with its ability to automatically store data in encrypted form and to maintain an Audit Vault of information drawn from the operating system, database, and any other auditing source. DB2 was number two followed in the third position by PostgreSQL, with its "robust security layer," said Andrews. Number four was SQL Server, followed by Sybase, MySQL and Informix.
Oracle was tops in atomicity, followed by DB2, PostgreSQL, SQL Server, Sybase, MySQL and Informix.
An increasingly important category for databases that are intended to serve Web applications is XML data handling. Again, Oracle and SQL Server lead the category, followed by DB2. But the open source systems, MySQL and PostgreSQL made up the sixth and seventh ranks, respectively.
When it comes to management tools that come with the database, Oracle lead, followed by SQL Server, DB2, MySQL, Sybase, and Informix. Bringing up the rear was PostgreSQL. "PostgreSQL, which has a large community of open source developers to create tools for it, does not provide them with the database," the report noted.
All in all, I think those are good numbers for Postgres. The really important thing is getting Postgres out there and making it visible. Articles like this go a long way towards that goal. LewisC