[geeklog-devel] Fwd: MSSQL Class - SQL Coding

[Joe Mucchiello]

At 12:49 AM 6/2/2009, Vincent Furia wrote:
>I don't think this is going to be as big an issue. While mysql, 
>mssql, and pgsql (and other dbs as well) all have special notation 
>that can be used, if we migrate to standard ANSI sql we can use a 
>single query for all the databases and save ourselves the overhead 
>of writing seperate queries for each db. I suggested to Stan that as 
>he finds queries that can be done in ANSI sql shared by all dbs, he 
>should use the ANSI sql and remove the per datbase arrays.

That would be great. But I don't think there are standards for stuff 
like NOW() or UNIXTIME(). That's why I was suggesting creating 
functions to hide that kind of thing:

$sql = 'SELECT id, title, etc, ' . DB_fieldAsTimestamp('created') . ' 
FROM ' . $_TABLES['foo'] . ' WHERE created > ' . DB_now() . ' ORDER 
BY created DESC';

mysql: SELECT id, title, etc, UNIXTIME(created) FROM gl_foo WHERE 
created > NOW() ORDER BY created DESC
mssql: SELECT id, title, etc, DATEDIFF(s, '19700101', created) FROM 
gl_foo WHERE created > GETDATE() ORDER BY created DESC

Although UNIXTIME is a poor example since mysql is just about the 
only database engine that supports it. Still dates are always a 
problem. pgsql and mssql don't handle big text fields like mysql. I'm 
sure mssql users would like to have stories with greater than 4k of 
text. pgsql users will have the same issue.

----
Joe Mucchiello
Throwing Dice Games
http://www.throwingdice.com 



No virus found in this outgoing message
Checked by PC Tools AntiVirus (6.0.0.19 - 10.004.038).
http://www.pctools.com/free-antivirus/