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 ' MS Access does not support ordering by column name
\r
80 Public Function UnparseDistinctColumnAccess(colnum as integer) As String
\r
81 dim sqltext As String
\r
82 sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " FROM " & FromClause
\r
83 if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause
\r
85 Headings.Add(SelectList(colnum).name)
\r
86 UnparseDistinctColumnAccess=sqltext & " ORDER BY " & SelectList(colnum).sql
\r
90 Public Function UnparseDistinctColumn(colnum as integer) As String
\r
91 dim sqltext As String
\r
92 sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " as col1 FROM " & FromClause
\r
93 if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause
\r
95 Headings.Add(SelectList(colnum).name)
\r
96 UnparseDistinctColumn=sqltext & " ORDER BY col1"
\r
100 Public function UnparseColumnList() As String
\r
101 dim strSelectList As New ArrayList(), i as integer, sql as String
\r
103 for i=0 to SelectList.count-1
\r
104 strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)
\r
105 Headings.Add(SelectList(i).name)
\r
107 UnparseColumnList=join(strSelectList.ToArray(),",")
\r
111 Public function UnparseColumnListSkip(arSkipCols() as String) As String
\r
112 dim strSelectList As New ArrayList(), i as integer
\r
113 dim SkipIdx as integer=0, skip as boolean
\r
115 for i=0 to SelectList.count-1
\r
117 if SkipIdx < arSkipCols.Length then
\r
118 skip=CBool(arSkipCols(SkipIdx)=CStr(i))
\r
119 if skip then SkipIdx+=1
\r
122 strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)
\r
123 Headings.Add(SelectList(i).name)
\r
126 UnparseColumnListSkip=join(strSelectList.ToArray(),",")
\r
130 ' returns a "windowed" select query
\r
131 ' includeAS should be true for SQL Server 2005+ and false for Oracle
\r
132 Public function UnparseWithRowNumber(offset as Integer, numrows as Integer, includeAS as Boolean, arSkipCols() as String) as String
\r
133 dim unparseText as String
\r
134 if OrderBy.count = 0 then Throw New Exception("an OrderBy clause is required")
\r
135 unparseText="SELECT ROW_NUMBER() OVER (ORDER BY " & join(OrderBy.ToArray(),",") & ") AS rico_rownum," & UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause
\r
136 if not IsNothing(WhereClause) then unparseText &= " WHERE " & WhereClause
\r
137 if GroupBy.count > 0 then unparseText &= " GROUP BY " & join(GroupBy.ToArray(),",")
\r
138 if not IsNothing(HavingClause) then unparseText &= " HAVING " & HavingClause
\r
139 unparseText="SELECT * FROM (" & unparseText & ")"
\r
140 if includeAS then unparseText &= " AS rico_Main"
\r
141 unparseText &= " WHERE rico_rownum > " & offset & " AND rico_rownum <= " & CStr(offset+numrows)
\r
142 UnparseWithRowNumber=unparseText
\r
151 WhereClause=Nothing
\r
152 HavingClause=Nothing
\r
157 ' -------------------------------------------------------------
\r
158 ' Parse a SQL select statement into its major components
\r
162 ' 3) more than one space between "group" and "by", or "order" and "by"
\r
163 ' 4) stored procedures
\r
164 ' -------------------------------------------------------------
\r
165 Public function ParseSelect(ByVal sqltext as String) As Boolean
\r
166 dim i As Integer, j As Integer, l As Integer, idx As Integer, parencnt As Integer
\r
167 dim clause As String, ch As String, curfield As String, nexttoken As String, inquote As Boolean, endquote As String
\r
170 sqltext=replace(sqltext,vbLf," ")
\r
171 sqltext=" " & replace(sqltext,vbCr," ") & " SELECT " ' SELECT suffix forces last curfield to be saved
\r
172 'response.write "<p>ParseSelect: " & sqltext & "</p>"
\r
179 ch=mid(sqltext,i,1)
\r
181 if ch=endquote then
\r
182 if endquote="'" and mid(sqltext,i,2)="''" then
\r
190 elseif ch="'" or ch="""" or ch="`" then
\r
199 parencnt=parencnt+1
\r
202 if parencnt=0 then exit function ' sql statement has a syntax error
\r
203 parencnt=parencnt-1
\r
205 elseif parencnt > 0 then
\r
208 'response.write "<p>" & clause & ": " & server.htmlencode(curfield) & "</p>"
\r
211 AddColumn(curfield)
\r
214 SelectList(SelectList.count-1).name=curfield
\r
217 case "GROUP BY": ArrayPush(GroupBy,curfield)
\r
218 case "ORDER BY": ArrayPush(OrderBy,curfield)
\r
219 case else: curfield &= ch
\r
222 j=InStr(i+1,sqltext," ")
\r
226 if ucase(mid(sqltext,j+1,3))="BY " then j=j+3
\r
227 nexttoken=ucase(mid(sqltext,i+1,j-i-1))
\r
228 'wscript.echo "'" & nexttoken & "'"
\r
229 'response.write "<p>" & clause & " : " & nexttoken & " : " & server.htmlencode(curfield) & "</p>"
\r
230 select case nexttoken
\r
231 case "SELECT","INTO","FROM","WHERE","GROUP BY","HAVING","ORDER BY":
\r
234 AddColumn(curfield)
\r
237 SelectList(SelectList.count-1).name=curfield
\r
239 case "FROM": SetParseField(FromClause,curfield)
\r
240 case "WHERE": SetParseField(WhereClause,curfield)
\r
241 case "GROUP BY": ArrayPush(GroupBy,curfield)
\r
242 case "HAVING": SetParseField(HavingClause,curfield)
\r
243 case "ORDER BY": ArrayPush(OrderBy,curfield)
\r
249 if clause="SELECT" then
\r
250 AddColumn(curfield)
\r
254 elseif curfield<>"" then
\r
259 if clause="SELECT" then
\r
263 elseif curfield<>"" then
\r
267 case else: if curfield<>"" then curfield &= ch
\r
279 Private Sub ArrayPush(s as ArrayList, ByRef newvalue as string)
\r
284 Private Sub SetParseField(ByRef f as string, ByRef newvalue as string)
\r
290 Public Sub AddColumn(sqlParm as String, Optional nameParm As String = "")
\r
291 SelectList.add(new sqlColumn(sqlParm,nameParm))
\r
295 ' -------------------------------------------------------------
\r
296 ' Add a join to the from clause
\r
297 ' -------------------------------------------------------------
\r
298 Public Sub AddJoin(ByVal JoinClause As String)
\r
299 if InStr(FromClause," join ")>0 then FromClause="(" & FromClause & ")" ' required by Access
\r
300 FromClause=FromClause & " " & JoinClause
\r
303 Private Sub SplitSortSpec(ByVal sortspec As String, ByRef sortcol As String, ByRef sortdir As String)
\r
304 sortspec=ucase(sortspec)
\r
305 if right(sortspec,3)="ASC" then
\r
306 sortcol=trim(left(sortspec,len(sortspec)-3))
\r
308 elseif right(sortspec,4)="DESC" then
\r
309 sortcol=trim(left(sortspec,len(sortspec)-4))
\r
312 sortcol=trim(sortspec)
\r
317 Private Function FindSortColumn(ByVal sortspec As String) As Integer
\r
318 dim i As Integer, findcol As String, finddir As String, sortcol As String, sortdir As String
\r
320 SplitSortSpec(sortspec, findcol, finddir)
\r
321 for i=0 to OrderBy.count-1
\r
322 SplitSortSpec(OrderBy(i), sortcol, sortdir)
\r
323 if sortcol=findcol then
\r
330 ' -------------------------------------------------------------
\r
331 ' Add sort criteria to the beginning of the order by clause
\r
332 ' -------------------------------------------------------------
\r
333 Public Sub AddSort(ByVal NewSort As String)
\r
334 dim i As Integer, colidx As Integer
\r
335 colidx=FindSortColumn(NewSort)
\r
337 for i=colidx to 1 step -1
\r
338 OrderBy(i)=OrderBy(i-1)
\r
342 OrderBy.insert(0,NewSort)
\r
346 ' -------------------------------------------------------------
\r
347 ' Append sort criteria to the order by clause
\r
348 ' -------------------------------------------------------------
\r
349 Public Sub AppendSort(ByVal NewSort As String)
\r
350 OrderBy.add(NewSort)
\r
353 ' -------------------------------------------------------------
\r
354 ' Add a condition to the where clause
\r
355 ' -------------------------------------------------------------
\r
356 Public Sub AddWhereCondition(ByVal NewCondition)
\r
357 AddCondition(WhereClause,NewCondition)
\r
360 ' -------------------------------------------------------------
\r
361 ' Add a condition to the having clause
\r
362 ' -------------------------------------------------------------
\r
363 Public Sub AddHavingCondition(ByVal NewCondition)
\r
364 AddCondition(HavingClause,NewCondition)
\r
367 Private Sub AddCondition(ByRef Clause, ByVal NewCondition)
\r
368 if IsNothing(NewCondition) then exit sub
\r
369 If IsNothing(Clause) Then
\r
370 Clause="(" & NewCondition & ")"
\r
372 Clause &= " AND (" & NewCondition & ")"
\r
376 Public Sub DebugPrint(writer as object)
\r
378 writer.write("<p>Parse Result:")
\r
379 writer.write("<table border='1'>")
\r
380 if IsDistinct then writer.write("<tr valign='top'><td>DISTINCT<td> ")
\r
381 writer.write("<tr valign='top'><td>COLUMNS:<td><ol>")
\r
382 for i=0 to SelectList.count-1
\r
383 writer.write("<li>" & SelectList(i).Unparse)
\r
385 writer.write("</ol><tr valign='top'><td>FROM:<td>" & FromClause)
\r
386 if not IsNothing(WhereClause) then writer.write("<tr valign='top'><td>WHERE:<td>" & WhereClause)
\r
387 if GroupBy.count > 0 then writer.write("<tr valign='top'><td>GROUP BY:<td>" & join(GroupBy.ToArray(),"<br>"))
\r
388 if not IsNothing(HavingClause) then writer.write("<tr valign='top'><td>HAVING:<td>" & HavingClause)
\r
389 if OrderBy.count > 0 then writer.write("<tr valign='top'><td>ORDER BY:<td>" & join(OrderBy.ToArray(),"<br>"))
\r
390 writer.write("</table>")
\r