direxions + 2011 – connectivity inside & out file i/o updates, odbc 64-bit, & sql command...

DireXions+ 2011 – Connectivity Inside & Out

File I/O Updates, ODBC 64-bit, & SQL Command Utility

Presenter: Devon Austen

DireXions+ 2011 – Connectivity Inside & Out

Outline• Overview• How we made file I/O easier• How we made file I/O more useful• We live in a 64-bit world• The command line is King

DireXions+ 2011 – Connectivity Inside & Out

Overview• What’s new in the file I/O products

• But first a quick discussion about key topics

• What is ODBC?– Open DataBase Connectivity– Common interface standard– Uses Structured Query Language (SQL)

DireXions+ 2011 – Connectivity Inside & Out

Overview• PxPlus ODBC Driver– PxPlus implementation of ODBC API– Allows programs that use ODBC interface to connect to

PxPlus data– Local or remote using file server

• PxPlus File Server– Access PxPlus data on remote systems

• PVKIO library– PxPlus file I/O library for accessing PxPlus data– Local or remote using file server

DireXions+ 2011 – Connectivity Inside & Out

How We Made File I/O Easier

DireXions+ 2011 – Connectivity Inside & Out

New Installers• ODBC Drivers and File Server– New and improved installers– Faster and light weight– Default install location of file I/O products now

C:\PVX Plus Technologies\xxx

• Installers are NOT MSI

DireXions+ 2011 – Connectivity Inside & Out

New Installers• New PxPlus ODBC driver installer

DireXions+ 2011 – Connectivity Inside & Out

New Installers• New File Server installer

DireXions+ 2011 – Connectivity Inside & Out

Case Insensitive Table Names• The ODBC driver now ignores case when

processing table names in SQL commands

• Reduces the number of errors

• ODBC driver no longer supports table names that only differ in case

DireXions+ 2011 – Connectivity Inside & Out

TIMESTAMPADD Improvements

• Scalar function TIMESTAMPADD has been enhanced– TIMESTAMPADD function adds an interval to a

timestamp– Used to be limit on size of interval– Now standard integers usable for intervals

DireXions+ 2011 – Connectivity Inside & Out

Miscellaneous Improvements• File DSNs• Windows file server startup• Large tables using bookmarks• PVKIO library function Pvk_RestrictedRead• ODBC views

DireXions+ 2011 – Connectivity Inside & Out

How We Made File I/O More Useful

DireXions+ 2011 – Connectivity Inside & Out

Enhanced VLR File Support• File I/O products now support PxPlus

enhanced VLR format files– Allow more than 255 key entries per key block

DireXions+ 2011 – Connectivity Inside & Out

EFF File Support

• Added support for EFF files in the PVKIO library

DireXions+ 2011 – Connectivity Inside & Out

Per Connection Read Only Flag• New ODBC connection string parameter

ReadOnly=[0|1]

• Can be set using ODBC Administrator configuration page

• Can be set manually

DireXions+ 2011 – Connectivity Inside & Out

We Live In a 64-bit World

Whether we like it or not

DireXions+ 2011 – Connectivity Inside & Out

Running 32-bit File I/O Applications on a 64-bit OS

• 32-bit ODBC drivers can run on 64-bit OS’s– Limited to 32-bit applications

• 32-bit File Servers can run on 64-bit OS’s

DireXions+ 2011 – Connectivity Inside & Out

Confusion Caused by 32-bit ODBC Driver on 64-bit Windows

• On 64-bit OS’s there are two ODBC Administrators a 32-bit and a 64-bit version– 64-bit ODBC Administrator is located in:

Control Panel -> Administrative Tools

– 32-bit ODBC Administrator is located in: C:\Windows\SysWOW64

• 64-bit applications cannot use the 32-bit ODBC driver

DireXions+ 2011 – Connectivity Inside & Out

PxPlus 64-bit ODBC Driver• Increasing number of 64-bit platforms and

applications• Current ODBC driver will not work• So we developed a 64-bit ODBC driver• Can be installed alongside the 32-bit ODBC driver• Must re-enter activation information• Use the ODBC Administrator in the control panel

DireXions+ 2011 – Connectivity Inside & Out

The Command Line is King

DireXions+ 2011 – Connectivity Inside & Out

PxPlus SQL Command Line Client• Windows & Linux versions• Windows PxPlus SQL Command Line Client

installed with version 10 ODBC driver• Linux PxPlus SQL Command Line Client has

separate instraller• Connects to local or remote PxPlus data and

executes SQL commands

DireXions+ 2011 – Connectivity Inside & Out

When and Why to Use It• Quick and direct access to PxPlus data

• Testing ODBC driver and data sources

• Can be executed from web pages, scripts, and applications

DireXions+ 2011 – Connectivity Inside & Out

How to Use It• From the command line

pxpsql [connection info] [options] SQL command

• Example: Get all data from a local table named “products”

pxpsql -d C:\pxp_data SELECT * FROM \”Sales Rep\”

DireXions+ 2011 – Connectivity Inside & Out

How to Use It• pxpsql has –h or --help parameters• Windows/Unix command line considerations– Quotes (‘/”)– Parenthesis ()– Asterisks (*)

• SQL syntax for table names with spaces requires quotes (“)

• Escape character is (\) for Windows and Linux

DireXions+ 2011 – Connectivity Inside & Out

How to Use It• pxpsql has -c or --connection-string

parameters which accept a connection string in the following format

“param1=val1; param2=val2; …”

Example:“Directory=/home/devon/pxp_data; RemotePVKIOHost=192.168.0.100;

RemotePVKIOPort=20222;”

DireXions+ 2011 – Connectivity Inside & Out

How to Use It• pxpsql has -f or –config-file parameters– INI style file– Long style parameters– Overridden by command line parameters

DireXions+ 2011 – Connectivity Inside & Out

Demonstration• Example using pxpsql

DireXions+ 2011 – Connectivity Inside & Out

Questions?

DireXions+ 2011 – Connectivity Inside & Out

End of Presentation

Thank You