Simple SQL functions are okay (insert, delete, update). Outer joins have different (non-standard) syntax everywhere they are supported (Postgres does not yet support it, mysql does not). Some DBs support subqueries using standard SQL syntax (mysql does not). Some DBs support autoincrement fields (mysql, postgresql) others (Oracle) have sequences that you can use to do the same thing. Aggregate functions (min, max, count, ...) are pretty standard. Other functions are not. Cursors are completely nonstandard. Dates and time are nonstandard. All bets are off for stored procedures.

For defining the schema, each RDBMS has a different set of datatypes that it supports. You can use the standard SQL types if you want since most RDBMS map them to their internal types. Basically it is a mess.

If you want to write generic queries then you can use an ODBC driver since it will map 'standard' SQL to the DB flavor. But it is pretty slow.

What I do is wrap all code that accesses the DB in a subroutine so that my main code makes a standard call and passes in a DB handle that it got from another subroutine I have. Then inside the accessor routine the can do whatever it needs to get the data in a useful form and return it. I try to write 'generic' SQL and avoid any DB specific stuff, but at some point you will need to rely on something DB specific. At that point you can have the subroutine determine which RDBMS it is talking to and execute the correct code. I like to wrap anything that needs access to an autoincrement call with a stored procedure since the way you call them is somewhat standard and I can hide the details of getting the next ID in there.

For handling the table definition, you could write standard SQL and then have a perl script translate all of the datatypes from the generic form to the DB specific one. The problem here is deciding how to handle the autoincrement columns.

Good luck. The problem is not insurmountable, but it is twisty. You also have to make sure that you don't become DB dependent due to convenience. If you must do something that you know is DB dependent, flag it so you can find it later.

-ben

In reply to Re: Cross platform commonality with the Perl DBI by knobunc
in thread Cross platform commonality with the Perl DBI by cLive ;-)

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.