<%
'ocdFunctions.asp - 1 Click DB Common ASP Functions
'copyright 1997-2001 David Kawliche, AccessHelp.net

'1 Click DB technology is fully protected by international
'laws and treaties.  Never use, distribute, or redistribute
'any software and/or source code in violation of its licensing.

'Use of this software and/or source code is strictly at your own risk.
'All warranties are specificly disclaimed except as required by law.

'IMPORTANT : THIS IS NOT AN APPLICATION SECURITY PRODUCT  !
'
'To enforce application security, set appropriate logins 
'and permissions for all web server and database users.
'
'Use object and configuration properties _only_ to customize 
'application appearance and interactions with other components. 

'For more information see : http://1ClickDB.com

        
'**Start Encode**

'These functions are intended for internal 1 Click DB operations only:

Function FormatForSQL (byVal strToFormat, byVal DatabaseType, byVal FormatType)
	Select Case UCase(FormatType)
		Case "CLEANUSERSQL"
			strToFormat = replace(strToFormat,";", "' + ';' + '",1,-1,1)
			strToFormat = replace(strToFormat,"--", "' + '--' + '",1,-1,1)
			strToFormat = replace(strToFormat, "SELECT ", "' + 'SELECT ' + '",1,-1,1)
			strToFormat = replace(strToFormat, "UNION ", "' + 'UNION ' + '",1,-1,1)
			strToFormat = replace(strToFormat, "EXEC ", "' + 'EXEC ' + '",1,-1,1)
			strToFormat = replace(strToFormat, "INTO ", "' + 'EXEC ' + '",1,-1,1)
			FormatForSQL = strToFormat
		Case "ADDSQLIDENTIFIER"
			if instr(strToFormat,"""") = 0 and ((instr(strToFormat,".") = 0 and DatabaseType = "Oracle") or DatabaseType <> "Oracle") and instr(strToFormat,"[")=0 Then
				Select Case DatabaseType 
					Case "Access" 
						strToFormat = Replace(strToFormat,"]","")
						strToFormat = Replace(strToFormat,"[","")
						FormatForSQL = "[" & strToFormat & "]"
					Case "MySQL"
						strToFormat = Replace(strToFormat,"`","")
						strToFormat = "`" & strToFormat & "`"
					Case "IXS","ADSI"
						FormatForSQL = strToFormat 
					Case Else '"SQLServer","Oracle"
						strToFormat = Replace(strToFormat,"""","")
						FormatForSQL = """" & strToFormat & """"
				End Select
		Else 'no change
			FormatForSQL = strToFormat
		End if
	Case "REMOVESQLIDENTIFIER"
		Select Case DataBaseType
			Case "Oracle"
				strToFormat = mid(strToFormat,instr(strToFormat,".") + 2)
				strToFormat = left(strToFormat, len(strToFormat)-1)
				FormatForSQL = strToFormat
			Case "Access"
				strToFormat = Replace(strToFormat,"]","")
				strToFormat = Replace(strToFormat,"[","")
				FormatForSQL = strToFormat
			Case "MySQL"
				strToFormat = Replace(strToFormat,"`","")
				FormatForSQL = strToFormat 
			Case Else
				strToFormat = Replace(strToFormat,"""","")
				FormatForSQL = strToFormat 
		End Select
	Case "SAFEDATE"
		dim varRetVal
		varRetVal = ""
		if isDate(strToFormat) Then
			varRetVal = Day(strToFormat) & "-" & UCase(Monthname(Month(strToFormat),True)) & "-" & Year(strToFormat)
		End if
		FormatForSQL = varRetVal
	Case ELse
		FormatForSQL = "Unknown Format"
	End Select
end function
function getDatabaseType(byRef ADOConnection)
	Select Case UCASE(ADOConnection.Provider)
		Case "ADSDSOOBJECT"
			getDatabaseType = "ADSI"
		Case Else
			Select Case UCASE(ADOConnection.Properties("DBMS Name")) 
				Case "MICROSOFT INDEX SERVER"
					getDatabaseType = "IXS"
				Case "MS SQL SERVER", "MICROSOFT SQL SERVER"
					getDatabaseType = "SQLServer"
				Case "MYSQL"
					getDatabaseType = "MySQL"
				Case "MS JET", "ACCESS"
					getDatabaseType = "Access"
				Case Else
					If instr(UCASE(ADOConnection.Properties("DBMS Name")),"ORAC") = 0 Then
						getDatabaseType = "Unknown"
					Else
						getDatabaseType = "Oracle"
					End if
			End Select
	End Select
end function
function getPKFields(byRef ADOConnection, byVal DatabaseType, byVal ObjectName, byVal QuotePrefix, byVal QuoteSuffix)
	on error resume next
	dim strObjectOwner
	strObjectOwner = GetSQLIDFPart(ObjectName,"SQLOBJECTOWNER", quoteprefix,quotesuffix)
	dim strObjectNAME
	strObjectName = GetSQLIDFPart(ObjectName,"SQLOBJECTNAME", quoteprefix,quotesuffix)

	dim rsIDX
	dim strSQLIDX
	getPKFields = ""
	set rsIDX = server.createobject("ADODB.Recordset")
	Select Case UCASE(DatabaseType)
		Case "ORACLE"
			set rsIDX = server.createobject("ADODB.Recordset")
			call rsIDX.open ("SELECT ALL_CONS_COLUMNS.COLUMN_NAME AS COLNAME FROM ALL_CONSTRAINTS, ALL_CONS_COLUMNS WHERE ALL_CONSTRAINTS.OWNER = ALL_CONS_COLUMNS.OWNER AND ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME AND ALL_CONSTRAINTS.TABLE_NAME = ALL_CONS_COLUMNS.TABLE_NAME AND CONSTRAINT_TYPE = 'P' AND ALL_CONS_COLUMNS.TABLE_NAME = '" & FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier") & "' AND ALL_CONSTRAINTS.TABLE_NAME = '" & FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier") & "'", ADOConnection)
			do while not rsIDX.eof
				getPKFields = getPKFields & rsIDX("COLNAME") & ","
				rsIDX.movenext
			loop
			rsIDX.close
			set rsIDX = nothing
		Case "SQLSERVER"
				if strObjectOwner <> "" Then
			'	response.write ("select COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE On INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name where INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_name = '" & replace(strObjectName,"'","''") & "' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_schema = '" & replace(strObjectOwner,"'","''") & "'  AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'")
				set rsIDX = ADOConnection.Execute ("select COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE On INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name where INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_name = '" & replace(strObjectName,"'","''") & "' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_schema = '" & replace(strObjectOwner,"'","''") & "'  AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'") 'indexes

				Else
				
				set rsIDX = ADOConnection.Execute ("select COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE On INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name where INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_name = '" & replace(strObjectName,"'","''") & "' AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'" )				
				
				
				
				End if
				if err <> 0 Then
					response.write err.description
					response.end
				Else
				do while not rsIDX.eof
						getPKFields = getPKFields & rsIDX.Fields("COLUMN_NAME").Value & ","
					rsIDX.movenext
				loop
				response.write err.description
'				response.write getPKFields
				rsIDX.close
				set rsIDX = nothing
				End if
		Case "ACCESS"
			if (ADOConnection.provider  <> "MSDASQL.1") Then
				set rsIDX = ADOConnection.openSchema(12,array(empty,empty,empty,empty,Cstr(FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier")))) 'indexes
				do while not rsIDX.eof
					If UCASE(rsIDX.Fields("table_name").Value) = UCase(FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier")) and rsIDX.Fields("primary_key").Value = True Then
						getPKFields = getPKFields & rsIDX.Fields("COLUMN_NAME").Value & ","
					End if
					rsIDX.movenext
				loop
				rsIDX.close
				set rsIDX = nothing
			Else
			set rsIDX = ADOConnection.openSchema(12) 'adSchemaindexes, usually not populated for ODBC connects
			do while not rsIDX.eof
				If UCASE(rsIDX.Fields("table_name").Value) = UCase(FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier")) and rsIDX.Fields("primary_key").Value = True Then
					getPKFields = getPKFields & rsIDX.Fields("COLUMN_NAME").Value & ","
				End if
				rsIDX.movenext
			loop
			rsIDX.close
			set rsIDX = nothing
			End if
		Case Else
			set rsIDX = ADOConnection.openSchema(12) 'adSchemaindexes, usually not populated for ODBC connects
			do while not rsIDX.eof
				If UCASE(rsIDX.Fields("table_name").Value) = UCase(FormatForSQL(ObjectName,DatabaseType,"RemoveSQLIdentifier")) and rsIDX.Fields("primary_key").Value = True Then
					getPKFields = getPKFields & rsIDX.Fields("COLUMN_NAME").Value & ","
				End if
				rsIDX.movenext
			loop
			rsIDX.close
			set rsIDX = nothing
	End Select
	if getPKFields <> "" THen
		getPKFields = left(getPKFields,len(getPKFields)-1)
	End if	
	if err.number <> 0 THen
		err.clear
		getPKFields = ""
	End if
End Function
Function SafeName(byval strToFormat)
	dim tmpval
	tmpval = Cstr(strToFormat)
	SafeName = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(tmpval," ","_"),"-","_"),"#","_"),"$","_"),"'","_"),"""","_"),".","_"),"/","_"),"&","_"),"*","_"),"%","_"),"=","_"),"<","_"),">","_"),"!","_"),"?","_"),"|","_"),":","_"),"\","_")
end function
Function getDataBaseDisplayName	(ocdgnDatabaseType, ocdgnIsODBC, ocdgnDatabaseConn)			
dim tmpPVar
if ocdgnDatabaseType = "Access"  Then
	if ocdgnIsODBC Then
		getDataBaseDisplayName = mid(ocdgnDatabaseConn.Properties("Current Catalog"),instrrev(ocdgnDatabaseConn.Properties("Current Catalog"),"\")+1)
	Else
		
		tmpPVar = mid(connNDTarget.Properties("Data Source"),instrrev(ocdgnDatabaseConn.Properties("Data Source"),"\")+1)
if instr(tmpPVar,".") > 0 Then
tmpPVar = left(tmpPVar,instr(tmpPvar,".")-1)
End if
getDataBaseDisplayName = tmpPVar
	End if
	
Elseif ocdgnDatabaseType = "SQLServer" Then
	getDataBaseDisplayName = ocdgnDatabaseConn.Properties("Current Catalog")
Else

	if (connNDTarget.Properties("Data Source")) = "" Then
		getDataBaseDisplayName = "DB Properties"

	Else

		getDataBaseDisplayName = ocdgnDatabaseConn.Properties("Data Source")

	End if	
End if
end function
Function GetSQLIDFPart(strSQLIdf,strIdfPart, strPrefix,strSuffix)

	dim arrPieces, strTemp
	arrPieces = split(strSQLIdf,".")
	strTemp = ""
	select case UCASE(strIDfPart)
		Case "SQLOBJECTNAME"
			if Ubound(arrPieces) > -1 Then

			strTemp = arrPieces(UBound(arrPieces))
			End if
		Case "SQLOBJECTOWNER"
			if Ubound(arrPieces) > 0 Then
				strTemp = arrPieces(UBound(arrPieces)-1)
			end if
		Case Else
	End select
	strTemp = replace(strTemp,strPrefix,"")
	strTemp = replace(strTemp,strSuffix,"")	
GetSQLIDFPart = strTemp
'response.write strTemp
'response.write strSuffix
End Function

%>