my CMPro cms has supported Access, mySQL, SQL Server and Oracle for a
few years now, and all code uses the same SQL. Doing the job isn't as
bad as you might think, but you do have to make some allowances, and
in some cases dumb-down your SQL so it will work on anything, perhaps
moving some things out of the db and into your code (like the
case-sensitivity issue for Oracle).
The only truly separate SQL I have to write is the table creators in
the install routine. The difference in the syntax is significant.
Access uses memo fields, mySQL uses text or mediumtext, SQL Server
uses ntext and Oracle uses CLOB. Everything is different, basically.
In Oracle you can only have one long text field per table, so this may
impact your application design (you can break this rule by writing an
ALTER TABLE cfquery that will add the second CLOB field and it will
work, however).
Inserts are a small challenge. All platforms support numeric PK's,
although Oracle does it through sequences. My system looks at a
number. db type "4" = Oracle. So an insert would look like this:
<cfquery
username="#request.UserName#"
password="#request.Password#"
datasource="#request.DSN#">
INSERT INTO myfile
(
<cfif not Compare(query.whichDB,"4")>ID,</cfif>
myfield
)
VALUES
(
<cfif not Compare(query.whichDB,"4")>seq_myfile.nextval,</cfif>
<cfqueryparam
cfsqltype="CF_SQL_VARCHAR"
value="#form.myfield#"
null="#YesNoFormat(not Len(form.myfield))#">
)
</cfquery>
Note the null statement. If your app allows feeding null values you
will have to use this kind of null checker to keep Oracle from blowing
up. Its probably a smart idea to do on general principles.
Keep your cfsqltypes simple. Universal ones are cf_sql_varchar,
cf_sql_longvarchar (long text fields), cf_sql_numeric and
cf_sql_decimal. You will have to either move all of your date fields
out of cfqueryparam (i.e. use raw sql) or switch to text fields and
handle date logic in CF to preserve your use of cfqueryparam, as
everything but Access understands cf_sql_timestamp for a date/time
field. Access understands nothing.
Then there's the concept of assigning a pk and querying back for the
thing as you continue your processing. There is only one truly
cross-platform way to do this other than writing custom SQL that fires
conditionally. Create a UUID in memory, put it into a field on your
insert and query back for it after the insert. Its an extra indexed
field but you need no locks or cftransaction blocks to get gauranteed
integrity. Also a UUID can be handy for other things down the road,
like salt for a hash, so I don't consider the practice inefficient in
the grand scheme.
And little crap is going to pop up in your code. I had some fits with
some list code that turned out to be Access feeding back numeric
values to me with a decimal whether I liked it or not and whether the
field was formatted for decimals or not (i.e. '12.0' instead of
'12'.). I couldn't get it to stop so now I use fix() anywhere that
might be an issue.
Once you get past the initial hump it isn't so bad. Overall it can be
fairly simple to write cross-platform apps but you'll have to unlearn
some things.
--
--mattRobertson--
Janitor, MSB Web Systems
mysecretbase.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202426
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
