--- /dev/null
+Partial Class sqlCompatibilty\r
+Inherits System.Web.UI.UserControl\r
+\r
+' ---------------------------------------------------------------------------\r
+' Functions to allow cross-db compatibility within Rico\r
+'\r
+' SQL dialect possible values: Access, Oracle, TSQL (MS SQL Server)\r
+' ---------------------------------------------------------------------------\r
+\r
+Protected Dialect as String\r
+\r
+Sub New(dbDialect as String)\r
+ Dialect=dbDialect\r
+End Sub\r
+\r
+Sub New()\r
+End Sub\r
+\r
+Sub SetDialectFromProvider(Provider as String)\r
+ Provider=ucase(Provider)\r
+ if Provider="SQLOLEDB" then\r
+ Dialect="TSQL"\r
+ elseif InStr(Provider,"ORACLE")>0 then\r
+ Dialect="Oracle"\r
+ elseif InStr(Provider,"JET")>0 then\r
+ Dialect="Access"\r
+ else\r
+ Throw New Exception("Unknown ADO provider")\r
+ end if\r
+End Sub\r
+\r
+Public function CurrentTime() as String\r
+ select case Dialect\r
+ case "TSQL","DB2": CurrentTime="CURRENT_TIMESTAMP"\r
+ case "Access": CurrentTime="Now()"\r
+ case else: CurrentTime="LOCALTIMESTAMP"\r
+ end select\r
+end function\r
+\r
+Public function Convert2Char(s as String) as String\r
+ select case Dialect\r
+ case "TSQL" : Convert2Char="cast(" & s & " as varchar)"\r
+ case "Access": Convert2Char="CStr(" & s & ")"\r
+ case "DB2" : Convert2Char="CHAR(" & s & ")"\r
+ case "Oracle": Convert2Char="cast(" & s & " as varchar2(20))"\r
+ case else: Convert2Char=s ' implicit conversion (MySQL)\r
+ end select\r
+end function\r
+\r
+Public function Wildcard() as String\r
+ Wildcard="%"\r
+end function\r
+\r
+Public function SqlDay(s as String) as String\r
+ select case Dialect\r
+ case "Oracle": SqlDay="to_char(" & s & ",'DD')"\r
+ case "MySQL": SqlDay="dayofmonth(" & s & ")"\r
+ case else: SqlDay="day(" & s & ")"\r
+ end select\r
+end function\r
+\r
+Public function SqlMonth(s as String) as String\r
+ select case Dialect\r
+ case "Oracle": SqlMonth="to_char(" & s & ",'MM')"\r
+ case else: SqlMonth="month(" & s & ")"\r
+ end select\r
+end function\r
+\r
+Public function SqlYear(s as String) as String\r
+ select case Dialect\r
+ case "Oracle": SqlYear="to_char(" & s & ",'YYYY')"\r
+ case else: SqlYear="year(" & s & ")"\r
+ end select\r
+end function\r
+\r
+Public function addQuotes(s as String) as String\r
+ select case Dialect\r
+ case "Access":\r
+ if IsDate(s) then\r
+ addQuotes="#" & s & "#"\r
+ else\r
+ addQuotes="""" & replace(s,"""","""""") & """"\r
+ end if\r
+ case "MySQL": addQuotes="'" & replace(replace(s,"\","\\"),"'","\'") & "'"\r
+ case else: addQuotes="'" & replace(s,"'","''") & "'"\r
+ end select\r
+end function\r
+\r
+Public function Concat(arStrings() as String, addQuotes as Boolean) as String\r
+ dim i as Integer\r
+ if addQuotes then\r
+ For Each i in arStrings\r
+ 'arStrings(i)=addQuotes(arStrings(i))\r
+ next\r
+ end if\r
+ select case Dialect\r
+ case "TSQL": Concat=join(arStrings,"+")\r
+ case "Access": Concat=join(arStrings," & ")\r
+ case "MySQL": Concat="concat(" & join(arStrings,",") & ")"\r
+ case else: Concat=join(arStrings," || ")\r
+ end select\r
+end function\r
+\r
+End Class\r