SPUG:Problems getting rows from sybase db

[Marc M. Adkins]

Another buttinsky...

> 3) And since I don't save much by saving lines because I use a
> here doc, I
> would rewrite that statement like this:
>
> $dbh->dbcmd(<<SQL);
> select NSS.name
> 	, NPDB.name
> from PFWUtil..NavAreaPhysicalDbSqlServer NAPDBSS
> 	, PFWUtil..NavAreaLogicalDb NALDB
> 	, PFWUtil..NavLogicalDb NLDB
> 	, PFWUtil..NavPhysicalDb NPDB
> 	, PFWUtil..NavSqlServer NSS
> 	, PFWUtil..NavArea NA
> where  NPDB.oid = NAPDBSS.oidPhysicalDb
>         and  NSS.oid = NAPDBSS.oidSqlServer
> 	and  NLDB.oid = NALDB.oidLogicalDb
>         and  NALDB.oidAreaPhysicalDbSqlServer = NAPDBSS.oid
> 	and  NALDB.oidArea = NA.oid
>         and  NLDB.name = 'AXYSDB'
> 	and lower(NA.name) like lower('Western%')
> SQL
>
> This makes it easier to add and remove columns/tables/conditions.

Moreover, you can use the same here doc name (e.g. SQL) each time.  So by
structuring all of your DB calls in this manner and using the same name each
time you can go back with a _simple_ script and pull out all of the SQL
statements for your documentation.  Or for your DBA to analyze for the
purpose of tweaking indices in your database.  On my last project I wrote a
script that would pre-process Perl into something that Doxygen (a
public-domain documentation tool) would consume and a side-effect was a page
with all the SQL statements on it.  Useful result for little work.

4) It can be very worthwhile to preprocess statement handles for oft-used
statements.  See the DBI doc.  See the DBI doc or O'Reilly's 'Programming
the Perl DBI.'

Make no mistake, moving from one database to another is not completely
painless.  I wrote one project using SQL Server and deployed using Oracle.
Mostly it's OK, but (ominous background music...) _all SQLs are NOT alike_.
Date formatting was one of the gotchas as I recall.  But at least if you're
using DBI the transition is way _less_ painful than if you're not.

Marc M. Adkins

P.S.  Like, there's a Perl binding to Axys now?  Awesome...  -- mma