Attribute VB_Name = "modSQLFunct"
Option Explicit
Public gintDataType As Integer '*** 資料庫類型
Public gstrWildChar As String
Public gintCharacterType As Integer '處理Big5 Character Set 的問題
Public Function cuIFSQL() As String
Dim tmpResult As String
If gintDataType = 1 Then
tmpResult = "iif("
Else
tmpResult = "if("
End If
cuIFSQL = tmpResult
End Function
Public Function cuIntSQL(tmpString) As String
Dim tmpResult As String
If gintDataType = 1 Then
tmpResult = " int(" & tmpString & ") "
Else
tmpResult = " truncate(" & tmpString & ",0) "
End If
cuIntSQL = tmpResult
End Function
Public Function cuCurrentTimeStamp() As String
Dim tmpResult As String
Select Case gintDataType
Case 1, 3
tmpResult = "'" & Now & "'"
Case Else
tmpResult = "current_timestamp"
End Select
cuCurrentTimeStamp = tmpResult
End Function
Public Function cuBlnDec(tmpData As Boolean) As String
If tmpData = True Then
cuBlnDec = 1
Else
cuBlnDec = 0
End If
End Function
Public Function cuIntStr(tmpData As Integer) As String
If tmpData < 1 Then
cuIntStr = 0
Else
cuIntStr = 1
End If
End Function
Public Function AddSlashes(tmpStr As String) As String
Dim intX As Long
Dim tmpResult As String
If Len(tmpStr) > 0 Then
For intX = 1 To Len(tmpStr)
tmpResult = tmpResult & Mid(tmpStr, intX, 1)
If Right(CStr(Hex(Asc(Mid(tmpStr, intX, 1)))), 2) = "5C" Then
tmpResult = tmpResult & "\"
End If
Next
End If
AddSlashes = tmpResult
End Function
Public Function cuRemoveTab(tmpData As String) As String
Dim tmpResult As String
tmpResult = Replace(tmpData, vbTab, "")
cuRemoveTab = tmpResult
End Function
Public Function cuChkString(tmpData As String) As String
Dim tmpResult As String
Select Case gintDataType
Case 1, 3
tmpResult = Replace(tmpData, "'", "''")
Case Else
'tmpResult = Replace(tmpData, "'", "''", , , vbTextCompare)
tmpResult = Replace(tmpData, "'", "''", , , vbTextCompare)
If gintCharacterType = 1 Then
tmpResult = AddSlashes(tmpResult) '解決中文2003 Server Mysql character-set 的問題
Else
tmpResult = Replace(tmpResult, "\", "\\", , , vbBinaryCompare) ' 如需使用中文 Mysql character-set 必須是Big5 測試時可用"許功蓋"
End If
End Select
cuChkString = tmpResult
End Function
Public Function AdjustLastSQL(tmpData As String, Optional tmpRecCount As Integer = 1) As String
If gintDataType = 2 Then
AdjustLastSQL = "select " & tmpData & " limit " & tmpRecCount
Else
AdjustLastSQL = "select top " & tmpRecCount & " " & tmpData
End If
End Function
Public Function cuChkDate(tmpDate As Date) As String
Select Case gintDataType
Case 1, 3
cuChkDate = "#" & CStr(tmpDate) & "#"
Case Else
cuChkDate = CStr(tmpDate)
End Select
End Function
Public Function cuChkNull(tmpStr As String) As String
If Len(Trim(tmpStr)) < 1 Then
cuChkNull = "null"
Else
cuChkNull = tmpStr
End If
End Function
Public Function cuChkBool(tmpBool As Boolean) As String
Select Case gintDataType
Case 1, 3
cuChkBool = CStr(tmpBool)
Case Else
If tmpBool = False Then
cuChkBool = "0"
Else
cuChkBool = "1"
End If
End Select
End Function
Public Function MysqlDate(InputDate As Date) As String
MysqlDate = Format(InputDate, "yyyy-mm-dd")
End Function
Public Function MysqlCDate(InputDate As String) As String
If Len(InputDate) > 0 Then
MysqlCDate = MysqlDate(CStr(InputDate))
Else
MysqlCDate = ""
End If
End Function
Public Function MysqlTime(InputTime As Date) As String
MysqlTime = Format(InputTime, "hh:mm:ss")
End Function
Public Function AdjSqlStr(InputStr As String) As String
If Len(InputStr) > 0 Then
AdjSqlStr = InputStr & " and "
Else
AdjSqlStr = " where "
End If
End Function
Public Function AndSqlStr(InputStr As String) As String
AndSqlStr = InputStr & " and "
End Function
Public Function MysqlBool(DataType As Integer, SQLBool As Boolean) As String
Select Case DataType
Case 1
MysqlBool = CStr(SQLBool)
Case Else
If SQLBool = False Then
MysqlBool = "0"
Else
MysqlBool = "1"
End If
End Select
End Function
Public Function MysqlStr(sqlstring As String) As String
Dim tmpResult As String
tmpResult = Replace(sqlstring, "'", "''", , , vbTextCompare)
MysqlStr = Replace(tmpResult, "\", "\\", , , vbTextCompare)
End Function
