1 Partial Class sqlParse
\r
2 Inherits System.Web.UI.UserControl
\r
3 Implements ICloneable
\r
5 Public Class sqlColumn
\r
6 Public sql As String, name As String
\r
7 Public LookupQuery As String ' query to populate column
\r
9 Public Sub New(Optional sqlParm As String = "", Optional nameParm As String = "")
\r
10 if sqlParm<>"" then sql=sqlParm
\r
11 if nameParm<>"" then name=nameParm
\r
14 Public function Unparse()
\r
16 if not IsNothing(name) then
\r
25 '********************************************************************************************************
\r
26 ' Parse SQL a statement
\r
27 '********************************************************************************************************
\r
29 Public IsDistinct As Boolean
\r
30 Public SelectList As New ArrayList()
\r
31 Public GroupBy As New ArrayList()
\r
32 Public OrderBy As New ArrayList()
\r
33 Public FromClause As String, WhereClause As String, HavingClause As String
\r
34 Public Headings As New ArrayList() ' set after an any unparse* call
\r
36 Public Function Clone As Object Implements ICloneable.Clone
\r
37 Dim NewObj as object = Me.MemberwiseClone, item as String
\r
38 ' shallow copy of OrderBy is insufficient because it may be modified by ricoResponse.ascx
\r
39 NewObj.OrderBy = New ArrayList()
\r
40 for each item in Me.OrderBy
\r
41 NewObj.OrderBy.Add(item)
\r
46 ' -------------------------------------------------------------
\r
47 ' Rebuilds a SQL select statement that was parsed by ParseSelect
\r
48 ' -------------------------------------------------------------
\r
49 Private Function Unparse(arSkipCols) As String
\r
50 dim sqltext As String = "SELECT "
\r
51 if IsDistinct then sqltext &= "DISTINCT "
\r
52 sqltext &= UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause
\r
53 if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause
\r
54 if GroupBy.count > 0 then sqltext &= " GROUP BY " & join(GroupBy.ToArray(),",")
\r
55 if not IsNothing(HavingClause) then sqltext &= " HAVING " & HavingClause
\r
56 if OrderBy.count > 0 then sqltext &= " ORDER BY " & join(OrderBy.ToArray(),",")
\r
61 Public Function UnparseSelect() As String
\r
62 dim arSkipCols(-1) as string
\r
63 UnparseSelect=Unparse(arSkipCols)
\r
67 Public function UnparseSelectSkip(arSkipCols)
\r
68 UnparseSelectSkip=Unparse(arSkipCols)
\r
72 Public Function UnparseSelectDistinct() As String
\r
73 dim arSkipCols(-1) as string
\r
75 UnparseSelectDistinct=Unparse(arSkipCols)
\r
79 Public Function UnparseDistinctColumn(colnum as integer) As String
\r
80 dim sqltext As String
\r
81 sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " FROM " & FromClause
\r
82 if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause
\r
84 Headings.Add(SelectList(colnum).name)
\r
85 UnparseDistinctColumn=sqltext
\r
89 Public function UnparseColumnList() As String
\r
90 dim strSelectList As New ArrayList(), i as integer, sql as String
\r
92 for i=0 to SelectList.count-1
\r
93 strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)
\r
94 Headings.Add(SelectList(i).name)
\r
96 UnparseColumnList=join(strSelectList.ToArray(),",")
\r
100 Public function UnparseColumnListSkip(arSkipCols() as String) As String
\r
101 dim strSelectList As New ArrayList(), i as integer
\r
102 dim SkipIdx as integer=0, skip as boolean
\r
104 for i=0 to SelectList.count-1
\r
106 if SkipIdx < arSkipCols.Length then
\r
107 skip=CBool(arSkipCols(SkipIdx)=CStr(i))
\r
108 if skip then SkipIdx+=1
\r
111 strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)
\r
112 Headings.Add(SelectList(i).name)
\r
115 UnparseColumnListSkip=join(strSelectList.ToArray(),",")
\r
119 ' returns a "windowed" select query
\r
120 ' includeAS should be true for SQL Server 2005+ and false for Oracle
\r
121 Public function UnparseWithRowNumber(offset as Integer, numrows as Integer, includeAS as Boolean, arSkipCols() as String) as String
\r
122 dim unparseText as String
\r
123 if OrderBy.count = 0 then Throw New Exception("an OrderBy clause is required")
\r
124 unparseText="SELECT ROW_NUMBER() OVER (ORDER BY " & join(OrderBy.ToArray(),",") & ") AS rico_rownum," & UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause
\r
125 if not IsNothing(WhereClause) then unparseText &= " WHERE " & WhereClause
\r
126 if GroupBy.count > 0 then unparseText &= " GROUP BY " & join(GroupBy.ToArray(),",")
\r
127 if not IsNothing(HavingClause) then unparseText &= " HAVING " & HavingClause
\r
128 unparseText="SELECT * FROM (" & unparseText & ")"
\r
129 if includeAS then unparseText &= " AS rico_Main"
\r
130 unparseText &= " WHERE rico_rownum > " & offset & " AND rico_rownum <= " & CStr(offset+numrows)
\r
131 UnparseWithRowNumber=unparseText
\r
140 WhereClause=Nothing
\r
141 HavingClause=Nothing
\r
146 ' -------------------------------------------------------------
\r
147 ' Parse a SQL select statement into its major components
\r
151 ' 3) more than one space between "group" and "by", or "order" and "by"
\r
152 ' 4) stored procedures
\r
153 ' -------------------------------------------------------------
\r
154 Public function ParseSelect(ByVal sqltext as String) As Boolean
\r
155 dim i As Integer, j As Integer, l As Integer, idx As Integer, parencnt As Integer
\r
156 dim clause As String, ch As String, curfield As String, nexttoken As String, inquote As Boolean, endquote As String
\r
159 sqltext=replace(sqltext,vbLf," ")
\r
160 sqltext=" " & replace(sqltext,vbCr," ") & " SELECT " ' SELECT suffix forces last curfield to be saved
\r
161 'response.write "<p>ParseSelect: " & sqltext & "</p>"
\r
168 ch=mid(sqltext,i,1)
\r
170 if ch=endquote then
\r
171 if endquote="'" and mid(sqltext,i,2)="''" then
\r
179 elseif ch="'" or ch="""" or ch="`" then
\r
188 parencnt=parencnt+1
\r
191 if parencnt=0 then exit function ' sql statement has a syntax error
\r
192 parencnt=parencnt-1
\r
194 elseif parencnt > 0 then
\r
197 'response.write "<p>" & clause & ": " & server.htmlencode(curfield) & "</p>"
\r
200 AddColumn(curfield)
\r
203 SelectList(SelectList.count-1).name=curfield
\r
206 case "GROUP BY": ArrayPush(GroupBy,curfield)
\r
207 case "ORDER BY": ArrayPush(OrderBy,curfield)
\r
208 case else: curfield &= ch
\r
211 j=InStr(i+1,sqltext," ")
\r
215 if ucase(mid(sqltext,j+1,3))="BY " then j=j+3
\r
216 nexttoken=ucase(mid(sqltext,i+1,j-i-1))
\r
217 'wscript.echo "'" & nexttoken & "'"
\r
218 'response.write "<p>" & clause & " : " & nexttoken & " : " & server.htmlencode(curfield) & "</p>"
\r
219 select case nexttoken
\r
220 case "SELECT","INTO","FROM","WHERE","GROUP BY","HAVING","ORDER BY":
\r
223 AddColumn(curfield)
\r
226 SelectList(SelectList.count-1).name=curfield
\r
228 case "FROM": SetParseField(FromClause,curfield)
\r
229 case "WHERE": SetParseField(WhereClause,curfield)
\r
230 case "GROUP BY": ArrayPush(GroupBy,curfield)
\r
231 case "HAVING": SetParseField(HavingClause,curfield)
\r
232 case "ORDER BY": ArrayPush(OrderBy,curfield)
\r
238 if clause="SELECT" then
\r
239 AddColumn(curfield)
\r
243 elseif curfield<>"" then
\r
248 if clause="SELECT" then
\r
252 elseif curfield<>"" then
\r
256 case else: if curfield<>"" then curfield &= ch
\r
268 Private Sub ArrayPush(s as ArrayList, ByRef newvalue as string)
\r
273 Private Sub SetParseField(ByRef f as string, ByRef newvalue as string)
\r
279 Public Sub AddColumn(sqlParm as String, Optional nameParm As String = "")
\r
280 SelectList.add(new sqlColumn(sqlParm,nameParm))
\r
284 ' -------------------------------------------------------------
\r
285 ' Add a join to the from clause
\r
286 ' -------------------------------------------------------------
\r
287 Public Sub AddJoin(ByVal JoinClause As String)
\r
288 if InStr(FromClause," join ")>0 then FromClause="(" & FromClause & ")" ' required by Access
\r
289 FromClause=FromClause & " " & JoinClause
\r
292 Private Sub SplitSortSpec(ByVal sortspec As String, ByRef sortcol As String, ByRef sortdir As String)
\r
293 sortspec=ucase(sortspec)
\r
294 if right(sortspec,3)="ASC" then
\r
295 sortcol=trim(left(sortspec,len(sortspec)-3))
\r
297 elseif right(sortspec,4)="DESC" then
\r
298 sortcol=trim(left(sortspec,len(sortspec)-4))
\r
301 sortcol=trim(sortspec)
\r
306 Private Function FindSortColumn(ByVal sortspec As String) As Integer
\r
307 dim i As Integer, findcol As String, finddir As String, sortcol As String, sortdir As String
\r
309 SplitSortSpec(sortspec, findcol, finddir)
\r
310 for i=0 to OrderBy.count-1
\r
311 SplitSortSpec(OrderBy(i), sortcol, sortdir)
\r
312 if sortcol=findcol then
\r
319 ' -------------------------------------------------------------
\r
320 ' Add sort criteria to the beginning of the order by clause
\r
321 ' -------------------------------------------------------------
\r
322 Public Sub AddSort(ByVal NewSort As String)
\r
323 dim i As Integer, colidx As Integer
\r
324 colidx=FindSortColumn(NewSort)
\r
326 for i=colidx to 1 step -1
\r
327 OrderBy(i)=OrderBy(i-1)
\r
331 OrderBy.insert(0,NewSort)
\r
335 ' -------------------------------------------------------------
\r
336 ' Append sort criteria to the order by clause
\r
337 ' -------------------------------------------------------------
\r
338 Public Sub AppendSort(ByVal NewSort As String)
\r
339 OrderBy.add(NewSort)
\r
342 ' -------------------------------------------------------------
\r
343 ' Add a condition to the where clause
\r
344 ' -------------------------------------------------------------
\r
345 Public Sub AddWhereCondition(ByVal NewCondition)
\r
346 AddCondition(WhereClause,NewCondition)
\r
349 ' -------------------------------------------------------------
\r
350 ' Add a condition to the having clause
\r
351 ' -------------------------------------------------------------
\r
352 Public Sub AddHavingCondition(ByVal NewCondition)
\r
353 AddCondition(HavingClause,NewCondition)
\r
356 Private Sub AddCondition(ByRef Clause, ByVal NewCondition)
\r
357 if IsNothing(NewCondition) then exit sub
\r
358 If IsNothing(Clause) Then
\r
359 Clause="(" & NewCondition & ")"
\r
361 Clause &= " AND (" & NewCondition & ")"
\r
365 Public Sub DebugPrint(writer as object)
\r
367 writer.write("<p>Parse Result:")
\r
368 writer.write("<table border='1'>")
\r
369 if IsDistinct then writer.write("<tr valign='top'><td>DISTINCT<td> ")
\r
370 writer.write("<tr valign='top'><td>COLUMNS:<td><ol>")
\r
371 for i=0 to SelectList.count-1
\r
372 writer.write("<li>" & SelectList(i).Unparse)
\r
374 writer.write("</ol><tr valign='top'><td>FROM:<td>" & FromClause)
\r
375 if not IsNothing(WhereClause) then writer.write("<tr valign='top'><td>WHERE:<td>" & WhereClause)
\r
376 if GroupBy.count > 0 then writer.write("<tr valign='top'><td>GROUP BY:<td>" & join(GroupBy.ToArray(),"<br>"))
\r
377 if not IsNothing(HavingClause) then writer.write("<tr valign='top'><td>HAVING:<td>" & HavingClause)
\r
378 if OrderBy.count > 0 then writer.write("<tr valign='top'><td>ORDER BY:<td>" & join(OrderBy.ToArray(),"<br>"))
\r
379 writer.write("</table>")
\r