1 Partial Class sqlCompatibilty
\r
2 Inherits System.Web.UI.UserControl
\r
4 ' ---------------------------------------------------------------------------
\r
5 ' Functions to allow cross-db compatibility within Rico
\r
7 ' SQL dialect possible values: Access, Oracle, TSQL (MS SQL Server)
\r
8 ' ---------------------------------------------------------------------------
\r
10 Protected Dialect as String
\r
12 Sub New(dbDialect as String)
\r
19 Sub SetDialectFromProvider(Provider as String)
\r
20 Provider=ucase(Provider)
\r
21 if Provider="SQLOLEDB" then
\r
23 elseif InStr(Provider,"ORACLE")>0 then
\r
25 elseif InStr(Provider,"JET")>0 then
\r
28 Throw New Exception("Unknown ADO provider")
\r
32 Public function CurrentTime() as String
\r
34 case "TSQL","DB2": CurrentTime="CURRENT_TIMESTAMP"
\r
35 case "Access": CurrentTime="Now()"
\r
36 case else: CurrentTime="LOCALTIMESTAMP"
\r
40 Public function Convert2Char(s as String) as String
\r
42 case "TSQL" : Convert2Char="cast(" & s & " as varchar)"
\r
43 case "Access": Convert2Char="CStr(" & s & ")"
\r
44 case "DB2" : Convert2Char="CHAR(" & s & ")"
\r
45 case "Oracle": Convert2Char="cast(" & s & " as varchar2(20))"
\r
46 case else: Convert2Char=s ' implicit conversion (MySQL)
\r
50 Public function Wildcard() as String
\r
54 Public function SqlDay(s as String) as String
\r
56 case "Oracle": SqlDay="to_char(" & s & ",'DD')"
\r
57 case "MySQL": SqlDay="dayofmonth(" & s & ")"
\r
58 case else: SqlDay="day(" & s & ")"
\r
62 Public function SqlMonth(s as String) as String
\r
64 case "Oracle": SqlMonth="to_char(" & s & ",'MM')"
\r
65 case else: SqlMonth="month(" & s & ")"
\r
69 Public function SqlYear(s as String) as String
\r
71 case "Oracle": SqlYear="to_char(" & s & ",'YYYY')"
\r
72 case else: SqlYear="year(" & s & ")"
\r
76 Public function addQuotes(s as String) as String
\r
80 addQuotes="#" & s & "#"
\r
82 addQuotes="""" & replace(s,"""","""""") & """"
\r
84 case "MySQL": addQuotes="'" & replace(replace(s,"\","\\"),"'","\'") & "'"
\r
85 case else: addQuotes="'" & replace(s,"'","''") & "'"
\r
89 Public function Concat(arStrings() as String, addQuotes as Boolean) as String
\r
92 For Each i in arStrings
\r
93 'arStrings(i)=addQuotes(arStrings(i))
\r
97 case "TSQL": Concat=join(arStrings,"+")
\r
98 case "Access": Concat=join(arStrings," & ")
\r
99 case "MySQL": Concat="concat(" & join(arStrings,",") & ")"
\r
100 case else: Concat=join(arStrings," || ")
\r