Snitz Forums 2000 - Oracle 9i port

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Snitz Forums 2000 DEV-Group

DEV Discussions (Oracle)

Oracle 9i port - few tips...

Forum Locked

Printer Friendly

Author

Topic

SirZooro
Starting Member

Poland
12 Posts

Posted - 18 February 2004 : 11:56:57

1. Type conversion
NUMBER's are returned as variant of type 14 (it's unsupported in VBScript). You need to convert all to long's - use CLng().
If variable can be null, use CLngNull function:


function CLngNull(n)
	If IsNull(n) Then
		CLngNull = n
	Else
		CLngNull = CLng(n)
	End If
end function

You also need to replace function chkSelect, chkExist, chkExistElse, chkRadio and chkCheckbox (inc_func_common.asp):


function chkSelect(actualValue, thisValue)
	if isNumeric(actualValue) then actualValue = CLng(actualValue)
	if VarType(thisValue) = 14 Then thisValue = CLng(thisValue)
	if VarType(actualValue) = 14 Then actualValue = CLng(actualValue)
	if actualValue = thisValue then
		chkSelect = " selected"
	else 
		chkSelect = ""
	end if
end function


function chkExist(actualValue)
	if VarType(actualValue) = 14 Then actualValue = CLng(actualValue)
	if trim(actualValue) <> "" then
		chkExist = actualValue
	else 
		chkExist = ""
	end if
end function


function chkExistElse(actualValue, elseValue)
	if VarType(elseValue) = 14 Then elseValue = CLng(elseValue)
	if VarType(actualValue) = 14 Then actualValue = CLng(actualValue)
	if trim(actualValue) <> "" then
		chkExistElse = actualValue
	else 
		chkExistElse = elseValue
	end if
end function


function chkRadio(actualValue, thisValue, boltf)
	if VarType(thisValue) = 14 Then thisValue = CLng(thisValue)
	if VarType(actualValue) = 14 Then actualValue = CLng(actualValue)
	if isNumeric(actualValue) then actualValue = cLng(actualValue)
	if actualValue = thisValue EQV boltf then
		chkRadio = " checked"
	else 
		chkRadio = ""
	end if
end function


function chkCheckbox(actualValue, thisValue, boltf)
	if VarType(thisValue) = 14 Then thisValue = CLng(thisValue)
	if VarType(actualValue) = 14 Then actualValue = CLng(actualValue)
	if isNumeric(actualValue) then actualValue = cLng(actualValue)
	if actualValue = thisValue EQV boltf then
		chkCheckbox = " checked"
	else 
		chkCheckbox = ""
	end if
end function

Best regards,
Sir Zooro

SirZooro
Starting Member

Poland
12 Posts

Posted - 18 February 2004 : 12:13:08

2. Limiting displayed records
Displayed records are limited in two ways: in way provided by MDAC, or by changing SQL. You need to change TopSQL function like this (inc_func_commnon.asp):


Function TopSQL(strSQL, lngRecords)
	if ucase(left(strSQL,7)) = "SELECT " then
 		select case strDBType 
			case "sqlserver"
				TopSQL = "SET ROWCOUNT " & lngRecords & vbNewLine & strSQL & vbNewLine & "SET ROWCOUNT 0"
			case "access"
				TopSQL = "SELECT TOP " & lngRecords & mid(strSQL,7)
			case "mysql"
				if instr(strSQL,";") > 0 then
					strSQL1 = Mid(strSQL, 1, Instr(strSQL, ";")-1)
					strSQL2 = Mid(strSQL, InstrRev(strSQL, ";"))
					TopSQL = strSQL1 & " LIMIT " & lngRecords & strSQL2
				else
					TopSQL = strSQL & " LIMIT " & lngRecords
				end if
			case "oracle"
				Set oRX = CreateObject("VBScript.RegExp")
				oRX.IgnoreCase = True
				oRX.Pattern = "^\s*(SELECT\s+.+)\s+(FROM\s+.+)\s+(ORDER\s+BY\s+.+)$"

				Set oMatches = oRX.Execute(strSQL)
				If Not oMatches Is Nothing Then
					If oMatches.Count > 0 Then
						Set oSubMatches = oMatches(0).SubMatches
						sSelect = oSubMatches(0)
						sFrom = oSubMatches(1)
						sOrderBy = oSubMatches(2)
						sWynik = sSelect & " FROM (" & sSelect & ", RANK() OVER (" & sOrderBy & ") AS Emp_Rank " _
							& sFrom & " " & sOrderBy & ") WHERE Emp_Rank "
						nPos = InStr(lngRecords, ",")
						If nPos = 0 Then
							sWynik = sWynik & "<= " & lngRecords
						Else
							sWynik = sWynik & "BETWEEN " & Left(lngRecords, nPos-1) & _
								" AND " & Mid(lngRecords, nPos+1)
						End If
					End If
				End If
				if sWynik = "" then TopSQL = strSQL Else TopSQL = sWynik
		end select
	else
		TopSQL = strSQL
	end if
End Function

Best regards,
Sir Zooro

SirZooro
Starting Member

Poland
12 Posts

Posted - 18 February 2004 : 12:14:13

3. Dealing with CLOBs
Strings form CLOBs can be read without any additional work. Writing to CLOB is more complicated. You need two things: PL/SQL function (in database) and VBScript function:


CREATE OR REPLACE FUNCTION CreateClob (
	t01 varchar2 default null,
	t02 varchar2 default null,
	t03 varchar2 default null,
	t04 varchar2 default null,
	t05 varchar2 default null,
	t06 varchar2 default null,
	t07 varchar2 default null,
	t08 varchar2 default null,
	t09 varchar2 default null,
	t10 varchar2 default null,
	t11 varchar2 default null,
	t12 varchar2 default null,
	t13 varchar2 default null,
	t14 varchar2 default null,
	t15 varchar2 default null,
	t16 varchar2 default null,
	t17 varchar2 default null,
	t18 varchar2 default null,
	t19 varchar2 default null,
	t20 varchar2 default null,
	t21 varchar2 default null,
	t22 varchar2 default null,
	t23 varchar2 default null,
	t24 varchar2 default null,
	t25 varchar2 default null
) return clob
IS
wyn clob := null;
begin
	IF wyn IS NULL THEN wyn := t01; ELSIF t01 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t01); END IF;
	IF wyn IS NULL THEN wyn := t02; ELSIF t02 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t02); END IF;
	IF wyn IS NULL THEN wyn := t03; ELSIF t03 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t03); END IF;
	IF wyn IS NULL THEN wyn := t04; ELSIF t04 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t04); END IF;
	IF wyn IS NULL THEN wyn := t05; ELSIF t05 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t05); END IF;
	IF wyn IS NULL THEN wyn := t06; ELSIF t06 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t06); END IF;
	IF wyn IS NULL THEN wyn := t07; ELSIF t07 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t07); END IF;
	IF wyn IS NULL THEN wyn := t08; ELSIF t08 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t08); END IF;
	IF wyn IS NULL THEN wyn := t09; ELSIF t09 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t09); END IF;
	IF wyn IS NULL THEN wyn := t10; ELSIF t10 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t10); END IF;
	IF wyn IS NULL THEN wyn := t11; ELSIF t11 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t11); END IF;
	IF wyn IS NULL THEN wyn := t12; ELSIF t12 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t12); END IF;
	IF wyn IS NULL THEN wyn := t13; ELSIF t13 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t13); END IF;
	IF wyn IS NULL THEN wyn := t14; ELSIF t14 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t14); END IF;
	IF wyn IS NULL THEN wyn := t15; ELSIF t15 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t15); END IF;
	IF wyn IS NULL THEN wyn := t16; ELSIF t16 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t16); END IF;
	IF wyn IS NULL THEN wyn := t17; ELSIF t17 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t17); END IF;
	IF wyn IS NULL THEN wyn := t18; ELSIF t18 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t18); END IF;
	IF wyn IS NULL THEN wyn := t19; ELSIF t19 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t19); END IF;
	IF wyn IS NULL THEN wyn := t20; ELSIF t20 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t20); END IF;
	IF wyn IS NULL THEN wyn := t21; ELSIF t21 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t21); END IF;
	IF wyn IS NULL THEN wyn := t22; ELSIF t22 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t22); END IF;
	IF wyn IS NULL THEN wyn := t23; ELSIF t23 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t23); END IF;
	IF wyn IS NULL THEN wyn := t24; ELSIF t24 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t24); END IF;
	IF wyn IS NULL THEN wyn := t25; ELSIF t25 IS NOT NULL THEN DBMS_LOB.APPEND(wyn, t25); END IF;
	return wyn;
end;


Function ToClob(str)
	nMax = 4000
	nParam = 25
	str = CStr(str)
	if Len(str) <= nMax Then
		ToClob = "'" & str & "'"
	Else
		wyn = "CreateClob("
		Set rx = New RegExp
		rx.IgnoreCase = True
		rx.Pattern = "^([^']|'')*$"
		For n = 1 to nParam
			If n > 1 Then
				wyn = wyn & ","
			End If
			If Len(str) <= nMax Then
				wyn = wyn & "'" & str & "'"
				Exit For
			Else
				ss = Left(str, nMax)
				If Right(ss, 1) = "'" Then 'poprawka par apostrofow
					If rx.Test(ss) Then
						str = Mid(str, nMax+1)
					Else
						ss = Left (ss, nMax-1)
						str = Mid(str, nMax)
					End If
				ElseIf Right(ss, 1) = "\" And Mid(str, nMax+1, 1) = "&" Then 'poprawka ciagu \&
					ss = Left (ss, nMax-1)
					str = Mid(str, nMax)
				Else
					str = Mid(str, nMax+1)
				End If
				wyn = wyn & "'" & ss & "'"
			End If
		Next
		ToClob = wyn & ")"
		Set rx = Nothing
	End If
End Function

Best regards,
Sir Zooro

pweighill
Junior Member

United Kingdom
453 Posts

Posted - 18 February 2004 : 12:18:27

quote:
Originally posted by SirZooro

2. Limiting displayed records
Displayed records are limited in two ways: in way provided by MDAC, or by changing SQL. You need to change TopSQL function like this (inc_func_commnon.asp):

There is a lot shorter version, just add the following:


case "sqlserver"
	TopSQL = "SELECT * FROM (" & strSQL & ") WHERE ROWNUM <= " & lngRecords

Topic

Forum Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.08 seconds.