of_parse

pfcapsrv.pbl   >   pfc_n_cst_sql   >   of_parse

Name	Datatype
lc_char	char
li_Cnt	integer
li_KWNum	integer
li_NumStats	integer
li_Pos	integer
li_PosU	integer
LI_UNIONREPLACE	string
lnv_string	n_cst_string
ls_Clause	string[7]
ls_Keyword	string[7]
ls_right	string
ls_SQL	string[]
ls_UpperSQL	string

public function integer of_parse (string as_sql, ref n_cst_sqlattrib astr_sql[]);//////////////////////////////////////////////////////////////////////////////
//
//	Function:  		of_Parse
//
//	Access: 			public
//
//	Arguments:
//	as_SQL			The SQL statement to parse.
//	astr_sql[]		An array of sql attributes, passed by
//						reference, to be filled with the parsed SQL.
//
//	Returns:  		integer
//						The number of elements in the array.
//
//	Description:	Parse a SQL statement into its component parts.
//
//////////////////////////////////////////////////////////////////////////////
//
//	Revision History
//
//	Version
//	5.0   Initial version
// 7.0	Parse for UNION cannot include data from WHERE CLAUSE
//
//////////////////////////////////////////////////////////////////////////////
//
//	Copyright © 1996-1997 Sybase, Inc. and its subsidiaries.  All rights reserved.
//	Any distribution of the PowerBuilder Foundation Classes (PFC)
//	source code by other than Sybase, Inc. and its subsidiaries is prohibited.
//
//////////////////////////////////////////////////////////////////////////////

integer	li_Pos, li_KWNum, li_NumStats, li_Cnt, li_PosU
string	ls_UpperSQL, ls_Keyword[7], ls_Clause[7], ls_SQL[], ls_right
constant string LI_UNIONREPLACE="*%$!@"
char lc_char
n_cst_string  lnv_string

// Remove Carriage returns, Newlines, and Tabs
as_SQL = lnv_string.of_GlobalReplace(as_SQL, "~r", " ")
as_SQL = lnv_string.of_GlobalReplace(as_SQL, "~n", " ")
as_SQL = lnv_string.of_GlobalReplace(as_SQL, "~t", " ")

// Search for UNION keyword, must be followed by SELECT Keyword.
// Replace UNION with bogus string so that the UNIONs can
// be separated
li_PosU = Pos(Lower(as_SQL), "union") 
li_Pos = li_PosU
Do Until li_Pos <= 0
	If li_Pos > 0 Then
		li_Pos = li_Pos + 5
		Do While True
			//  Bypass embedded spaces  
			lc_char = Mid(as_SQL, li_Pos, 1)
			If lnv_string.of_IsSpace(lc_char) Then
				li_Pos++
			Else
				Exit
			End If
		Loop
		ls_right = Mid(Trim(as_SQL), li_Pos, 6)
		If Upper(ls_right) = "SELECT" Then
			as_SQL = Replace(as_SQL, li_PosU, 5, LI_UNIONREPLACE)
		End If
		li_PosU = Pos(Lower(as_SQL), "union", li_Pos+5) 
		li_Pos = li_PosU
	End If
Loop

// Separate the statement into multiple statements separated by UNIONs
li_NumStats = lnv_string.of_ParseToArray(as_SQL, LI_UNIONREPLACE, ls_SQL)

For li_Cnt = 1 to li_NumStats

	// Remove leading and trailing spaces
	ls_SQL[li_Cnt] = Trim(ls_SQL[li_Cnt])

	// Convet to upper case
	ls_UpperSQL = Upper(ls_SQL[li_Cnt])

	// Determine what type of SQL this is
	// and assign the appropriate kewords
	// for the corresponding type
	If Left(ls_UpperSQL, 7) = "SELECT " Then
		// Parse the SELECT statement
		ls_Keyword[1] = "SELECT "
		ls_Keyword[2] = " FROM "
		ls_Keyword[3] = " WHERE "
		ls_Keyword[4] = " GROUP BY "
		ls_Keyword[5] = " HAVING "
		ls_Keyword[6] = " ORDER BY "

	Elseif Left(ls_UpperSQL, 7) = "UPDATE " Then
		// Parse the UPDATE statement
		ls_Keyword[1] = "UPDATE "
		ls_Keyword[2] = " SET "
		ls_Keyword[3] = " WHERE "
		ls_Keyword[6] = " ORDER BY "

	Elseif Left(ls_UpperSQL, 12) = "INSERT INTO " Then
		// Parse the INSERT statement (test before 'insert')
		ls_Keyword[1] = "INSERT INTO "
		ls_Keyword[7] = " VALUES "
		
	Elseif Left(ls_UpperSQL, 7) = "INSERT " Then
		// Parse the INSERT statement (test after 'insert to')
		ls_Keyword[1] = "INSERT "
		ls_Keyword[7] = " VALUES "		

	Elseif Left(ls_UpperSQL, 12) = "DELETE FROM " Then
		// Parse the DELETE statement (test before 'delete')
		ls_Keyword[1] = "DELETE FROM "
		ls_Keyword[3] = " WHERE "

	Elseif Left(ls_UpperSQL, 7) = "DELETE " Then
		// Parse the DELETE statement (test after 'delete from')
		ls_Keyword[1] = "DELETE "
		ls_Keyword[3] = " WHERE "
		
	End if

	// There is a maximum of 7 keywords
	For li_KWNum = 7 To 1 Step -1
		If ls_Keyword[li_KWNum] <> "" Then
			// Find the position of the Keyword
			li_Pos = Pos(ls_UpperSQL, ls_Keyword[li_KWNum]) - 1

			If li_Pos >= 0 Then
				ls_Clause[li_KWNum] = Right(ls_SQL[li_Cnt], &
													(Len(ls_SQL[li_Cnt]) - &
														(li_Pos + Len(ls_Keyword[li_KWNum]))))
				ls_SQL[li_Cnt] = Left(ls_SQL[li_Cnt], li_Pos)
			Else
				ls_Clause[li_KWNum] = ""
			End if
		End if
	Next

	astr_sql[li_Cnt].s_Verb = Trim(ls_Keyword[1])

	If Pos(astr_sql[li_Cnt].s_Verb, "SELECT") > 0 Then
		astr_sql[li_Cnt].s_Columns = Trim(ls_Clause[1])
		astr_sql[li_Cnt].s_Tables 	= Trim(ls_Clause[2])
	Else
		astr_sql[li_Cnt].s_Tables = Trim(ls_Clause[1])

		If Pos(astr_sql[li_Cnt].s_Verb, "INSERT") > 0 Then
			li_Pos = Pos(astr_sql[li_Cnt].s_Tables, " ")
			If li_Pos > 0 Then
				astr_sql[li_Cnt].s_Columns = Trim(Right(astr_sql[li_Cnt].s_Tables, &
											(Len(astr_sql[li_Cnt].s_Tables) - li_Pos)))
				astr_sql[li_Cnt].s_Tables = Left(astr_sql[li_Cnt].s_Tables, (li_Pos - 1))
			End if
		Else
			astr_sql[li_Cnt].s_Columns = Trim(ls_Clause[2])
		End if
	End if

	astr_sql[li_Cnt].s_Where 	= Trim(ls_Clause[3])
	astr_sql[li_Cnt].s_Group 	= Trim(ls_Clause[4])
	astr_sql[li_Cnt].s_Having 	= Trim(ls_Clause[5])
	astr_sql[li_Cnt].s_Order 	= Trim(ls_Clause[6])
	astr_sql[li_Cnt].s_Values 	= Trim(ls_Clause[7])
Next

Return li_NumStats
end function

Name	Owner
systemfunctions.left	systemfunctions
systemfunctions.len	systemfunctions
systemfunctions.lower	systemfunctions
systemfunctions.mid	systemfunctions
systemfunctions.pos	systemfunctions
systemfunctions.pos	systemfunctions
systemfunctions.replace	systemfunctions
systemfunctions.right	systemfunctions
systemfunctions.trim	systemfunctions
systemfunctions.upper	systemfunctions
pfc_n_cst_string.of_parsetoarray	pfc_n_cst_string
pfc_n_cst_string.of_isspace	pfc_n_cst_string
pfc_n_cst_string.of_globalreplace	pfc_n_cst_string