Session Scope Connection Pool ??? - OO4O and Wizards(Archived)

Hi
I need to create a connection pool that a session scope.
so I did this:
in global.asa file ............................................................
<OBJECT RUNAT=Server SCOPE=Session ID=gobjOraSession PROGID="OracleInProcServer.XOraSession"></OBJECT>
after the user is prompted for username / password in an asp file .............
...
...
on error resume next          
     
' create connection pool
gobjOraSession.CreateDatabasePool 1, 100, 60, "xxx", "please/work", 0
if err.number <> 0 then
          
     ' return xml in response object
     objXmlDoc.loadXML("<error>" & err.Description & "</error>")
     objXmlDoc.Save(Response)
               
     ' destroy session
     gobjOraSession.destroyDatabasePool
else
     ' return xml in response object
     objXmlDoc.loadXML("<success>Logon Successful</success>")
     objXmlDoc.Save(Response)
end if
...
...
This all works great, BUT when I try to use retrieve a connection from the pool
later on in bombs out. I would have created the conn pool in global.asa file but
I cant as i dont have the users credentials yet
PLEASE help...
Thanks

Related

ORA-01008: not all variables bound

I finally figured out a workaround for that "OIP-04148 Unable to obtain a free database object"... I just had to Dim the ORADYN_NOCACHE and ORADYN_READONLY objects in the viewAlbum page.
Now, the program runs, and accepts an input for adding a new photo. The problem is now:
ORA-01008: not all variables bound
when I try to upload the photo... Any ideas? 
Code snippets would really help here.
I think you are working with the Java Photo Album Demo?
My guess is the error you see is that a statement in Java has not bound all the arguments (marked by ?) before execution.
Larry 
Lawrence, my man, am I glad to hear from you!
Here's the verbatim error message:
ORA-01008: not all variables bound <br>INSERT INTO photos ( id, description, location, image, thumb ) VALUES ( photos_sequence.nextval, :new_desc, :new_location, ORDSYS.ORDIMAGE.init(), ORDSYS.ORDIMAGE.init()) RETURN id INTO :new_id
I am using the interMedia 8i application for ASP/VBScript connection to an Oracle 8i database version 8.1.7.0.0 (download ordaspphoto.zip).
I believe the error originates in the getPhoto.asp page, and here it is:
<%# LANGUAGE=VBScript%>
<%Option Explicit%>
<%
' Copyright (c) Oracle Corporation 2002. All Rights Reserved.
'
' NAME
' getPhoto.asp
'
' DESCRIPTION
' Retrieve an image from the database and send to the browser
'
' MODIFIED (MM/DD/YY)
' rabbott 07/15/02 - Created
%>
<!--#INCLUDE FILE="lib.inc" -->
<%
' Disable buffering for media delivery
Response.Buffer = False
Dim connPhoto 'database connection
Dim setPhotos 'result set
Dim objMedia 'OrdImage object
Dim strSQL 'select statement
Dim strId 'photo id
Dim strColumn 'photo column indicator
'Get the input parameters
strColumn = Request.QueryString( "media" )
strId = Request.QueryString( "entry_id" )
' Define the SQL query to fetch the thumb-nail or
' full-size image from the database. Use a bind variable
' to improve performance.
If StrComp( strColumn, "thumb" ) = 0 Then
strSQL = "SELECT p.thumb, " & _
"to_char(p.thumb.getUpdateTime(), 'DY, MON DD, YYYY HH:MM:SS') " & _
"FROM photos p WHERE id = :photo_id"
Else
strSQL = "SELECT p.image, " & _
"to_char(p.image.getUpdateTime(), 'DY, MON DD, YYYY HH:MM:SS') " & _
"FROM photos p WHERE id = :photo_id"
End If
' Enable error handling so we can unbind from the parameters collection
On Error Resume Next
'Get a database connection
Set connPhoto = OraSession.GetDatabaseFromPool(2000)
' Fetch the media object
connPhoto.Parameters.Add "photo_id", strId, ORAPARM_INPUT, ORATYPE_NUMBER
Set setPhotos = connPhoto.CreateDynaset( strSQL, ORADYN_NOCACHE + ORADYN_READONLY )
connPhoto.Parameters.Remove( "photo_id" )
If setPhotos.EOF = True Then
Response.Status = HTTP_STATUS_NOT_FOUND
Else
Set objMedia = setPhotos(0).value
' Check update time if browser sent If-Modified-Since header
'
If CacheIsValid( setPhotos(1).value ) Then
Response.Status = HTTP_STATUS_NOT_MODIFIED
Else
' Set the mime type header and deliver the image to the browser.
SetLastModified( setPhotos(1).value )
Response.ContentType = objMedia.mimetype
ReadBlob objMedia.source.localData
End If
End If
' we're all done
Response.End
%>
As a struggling college student in the middle of an overwhelming Oracle DBA class, I would be eternally indebted for any help. Whatever happens, I really appreciate your even being willing to help.
Thanks, Larry 
Hi,
we have seen this behavior before with version 8 and have reported it. You may want to look to updates to OO4O. Perhaps updating to a version 9 CLIENT only install.
We have been able to make it go away sometimes by re-starting the web application.
Larry

Special Characters

Special character problem
We store xml-messages in a Oracle 9i Release 2 database in a column of type CLOB.
These messages can contain special characters like éêûä etcetera. These special characters get correctly displayed within TOAD and available applications on the database.
The problem arises when using a combination of VB.NET webservices and Oracle's ODP.NET (versie 9.2.0.7.0) to retrieve messages out of the database. At this moment the xml-messages get corrupted at retrieval and as a result it becomes a invalid XML that does not validate against the schema's (XSD's). This results in a error from the webservice instead of the result for the retrieve request.
The xml-messages also get inserted using the webservices, but like I already said everything looks good in TOAD. The webservices run on a Microsoft IIS webserver.
Is there anybody who knows where to search for the problem or better knows the solution ?
Regards,
Jørn Schurink 
The problem posted by Jørn is also discussed in this thread:
Re: ORA-31011: XML parsing failed for AL32UTF8
We've been able to reproduce the problem. It only occurs when using a stored procedure to retrieve XML data containing special characters.
For some reason tags after the special characters get "missing", which results into invalid XML.
Here follows information to reproduce the problem.
Versions
------------------------------------
Oracle 9.2.0.6
Oracle Client 10.1.0.2
ODP.NET 10.1.0.400 (also tried 9.2.0.4 and 9.2.0.7)
.NET Framework version 1.1 (with and without service pack 1 for Framework 1.1)
Table definition
------------------------------------
DROP TABLE TEST CASCADE CONSTRAINTS;
CREATE TABLE TEST
(
XMLELEM SYS.XMLTYPE
)
LOGGING
NOCACHE
NOPARALLEL;
Stored procedure
------------------------------------
-- "Set define off" turns off substitution variables.
Set define off;
CREATE OR REPLACE FUNCTION CECURE_CLIENT.FNC_GET_XML_TEST RETURN XMLTYPE IS
tmpVar XMLTYPE;
/******************************************************************************
NAME: FNC_GET_XML_TEST
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 4/3/2006 1. Created this function.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: FNC_GET_XML_TEST
Sysdate: 4/3/2006
Date and Time: 4/3/2006, 9:38:17 AM, and 4/3/2006 9:38:17 AM
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
SELECT XMLELEM INTO tmpVar FROM TEST where rownum = 1;
RETURN tmpVar;
END FNC_GET_XML_TEST;
/
XML DATA INSIDE TABLE
------------------------------------
<kks_transaction xmlns="http://schemaserver/schemas/1.0/">
<transaction_source_code>GAM</transaction_source_code>
<contract_request_nr>3003247</contract_request_nr>
<transactiontype_code>AKP</transactiontype_code>
<transaction_status>10</transaction_status>
<event_id>10000000235</event_id>
<request_registration_date>2006-02-24</request_registration_date>
<contract_start_date>2006-03-10</contract_start_date>
<contract_duration>1</contract_duration>
<action_code>KKS</action_code>
<card_start_date>2006-02-24</card_start_date>
<card_end_date>2008-03-09</card_end_date>
<contract_revenue>5300</contract_revenue>
<card_rail_plus>false</card_rail_plus>
<days_of_choice>false</days_of_choice>
<choice_of_customer>true</choice_of_customer>
<request_channel>WWW</request_channel>
<bankaccount>
<bankaccount_type>G</bankaccount_type>
<bankaccount_nr>1234567</bankaccount_nr>
<account_owner_name>F. Püna-Segura Closs</account_owner_name>
<account_owner_city>EDE GLD</account_owner_city>
<direct_debit>true</direct_debit>
</bankaccount>
<request_nr>3003247</request_nr>
<request_type>50</request_type>
<payment_amount>5300</payment_amount>
</kks_transaction>
.NET Code snippet (download project files -> http://www.nieuwegiessen.nl/work/ns/TestXMLType.zip)
------------------------------------
' Connection string
Dim dConnectionString As String = "User Id=test;Password=test;Data Source=testdb"
' Create connection
Dim dConnection As OracleConnection = New OracleConnection(dConnectionString)
Try
' Open connection
dConnection.Open()
' Create new Command
Dim dCommand As New OracleCommand
With dCommand
.CommandText = "FNC_GET_XML_TEST"
.CommandType = CommandType.StoredProcedure
.XmlCommandType = OracleXmlCommandType.None
.Connection = dConnection
End With
' Create output parameter
Dim dParameter As New OracleParameter
With dParameter
.ParameterName = "RETURN_VALUE"
.OracleDbType = OracleDbType.XmlType
.Direction = ParameterDirection.ReturnValue
End With
' Add parameter
dCommand.Parameters.Add(dParameter)
' Execute command
dCommand.ExecuteNonQuery()
' Cast result to OracleXmlType and save cast into xmlResultsOracle
Dim xmlResultsOracle As OracleXmlType = CType(dParameter.Value, OracleXmlType)
' Show results as string
Console.WriteLine(xmlResultsOracle.Value)
' Get XMLDocument
Dim xmlResultsDoc As XmlDocument = xmlResultsOracle.GetXmlDocument()
' Show XMLDocument
Console.WriteLine(xmlResultsDoc.InnerXml)
Catch ex As Exception
' An exception occured
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Source)
Finally
' Close connection
If dConnection.State = ConnectionState.Open Then
dConnection.Close()
End If
Console.ReadLine()
End Try 
An important issue I forgot to mention in the previous post is the character set used by the client/ server etc.
Client: WE8MSWIN1252
Server: WE8MSWIN1252

Issue when Showing different messages

Hi all,
we are trying to show different Web Messages which have some code between them (the code can take 1 or 2 minutes)
From Workbench there is no problem but if we execute it from a web script, only the second one is shown.
Has anybody addressed this issue? any workaround?
Thanks
     'Set the message value
     strMessage = "Process is being executed"
     
     If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
          'Win32 Client (Use Msgbox)
          MsgBox strMessage
     Else
          'Web Client (Set API action properties)
          RES.PlngActionType = 2                    '(See Enumeration: ScriptActionTypes)
          RES.PstrActionValue = strMessage
     End If
     
     ' CODE
     'Set the message value
     strMessage = "Process is being executed 2"
     
     If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
          'Win32 Client (Use Msgbox)
          MsgBox strMessage
     Else
          'Web Client (Set API action properties)
          RES.PlngActionType = 2                    '(See Enumeration: ScriptActionTypes)
          RES.PstrActionValue = strMessage
     End If 
Web messages only get displayed when the process has completed and the script returns control to the web client. You probably have both your messages output at this point but paged. 
Hi,
and would it be any other way of achieving what we want? therefore showing both messages (we don't care if it's in other format)
Thanks 
Why don't you write the messages to a text file and then display the text file at the end of the process. 
The fact is that we have a web script that calls a web service (synchronous) and we want the user to know the web service is being executed. Otherwise they may execute it again or something else.
So the idea is:
1. user clicks on the taskflow
2. a Message is shown so the user knows the process has started
3. the web service is executed
4. a message showing results
Thanks 
You're going to need to get creative. Have you thought about disabling the task flow execution while it is currently running? If your next question is, how do I do that; I'm going to challenge you to think about that one a little more and come back with ideas for input.

ODBC BI Server Bug - arithmetic operation resulted in an overflow

I am trying to write some really simple .NET code access the Oracle BI Server ODBC driver and it's not working at all.  I've connected fine, however it seems like anything I try to do related to getting database information spits up an error "arithmetic operation resulted in an overflow".Here is the code: Dim ConnectString As StringDim FactoryType As StringDim Factory As System.Data.Common.DbProviderFactoryDim Connection As System.Data.Common.DbConnection = NothingDim TablesData As System.Data.DataTable = NothingDim err As String = ""Dim nl As String = Chr(13) + Chr(10) Try    '-------------------------------------    ' Connect to the database via ODBC    '-------------------------------------    ConnectString = "DSN=BSODBC_7;uid=TheUser10;pwd=************"    FactoryType = "System.Data.Odbc"    Factory = System.Data.Common.DbProviderFactories.GetFactory(FactoryType)    Connection = Factory.CreateConnection    Connection.ConnectionString = ConnectString    Connection.Open()     '--------------------------------------------------    ' Request a list of tables from the database    ' ** Tried both with restrictions and without    '    ' ERROR on this line:    ' “Arithmetic operation resulted in an overflow.”    '--------------------------------------------------    TablesData = Connection.GetSchema("Tables")     '--------------------------------------------------    ' Show the list of tables on the screen in a grid    ' If it was successful.    '--------------------------------------------------    OnScreenGrid.AutoGenerateColumns = True    OnScreenGrid.DataSource = TablesData Catch ex As Exception    '-------------------------------------    ' Report the error    '-------------------------------------    err = ex.Message    If Not (ex.InnerException Is Nothing) Then        If Not (ex.InnerException.Message Is Nothing) Then            err = err + nl + nl + ex.InnerException.Message        End If    End If    MsgBox(err, MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, "Error") Finally    '-------------------------------------    ' Clean up and Close the DB Connection    '-------------------------------------    If Not (Connection Is Nothing) Then        Connection.Close()        Connection.Dispose()        Connection = Nothing    End If    End TryAny Thoughts?  Is this a known bug?  Is there a fix?
I doubt on lineOnScreenGrid.DataSource = TablesDatainstead of array as TablesData try to take List object and assign it to OnScreenGrid.DataSourcejust in case check thisDataGridView.AutoGenerateColumns Property (System.Windows.Forms) I might be wrong but just check it 
Thanks, it seems the error occurs in multiple places when NULLs exist in the data.  This seems to be a known bug.  Nice.
cool, since you know the reason close the post

OIP-04148 unable to obtain a free database object from the pool

Sheer Newbie here...
I downloaded and installed ordaspphoto.zip, and followed the README file to the letter. When I try to run the program, I get an "Oracle Automation error '800a01b8" - "OIP-04148 Unable to obtain a free database object from the pool".
I presume it is referring to my Global.asa file's connection pool. Here it is:
<!-- Declare the OO4O automation object -->
<OBJECT RUNAT=Server
SCOPE=APPLICATION
ID=OraSession
PROGID="OracleInProcServer.XOraSession">
</OBJECT>
<!-- And the type library
Edit the file path for your system
-->
METADATA TYPE="TypeLib"
FILE="C:\Oracle\ORAPO\bin\oip8.tlb"
<SCRIPT LANGUAGE=VBScript RUNAT=SERVER>
'Initialize application
Sub Application_OnStart()
' Connection pool parameters
Dim initSize ' initial number of pool connections
Dim maxSize ' max connections
Dim timeout ' session timeout in seconds
Dim service ' database service name
Dim userPass ' user/password pair
' Edit these for your database
initSize = 2
maxSize = 5
timeout = 300
service = "photoAlbum"
userPass = "system/manager"
' Create the connection pool
OraSession.CreateDatabasePool initSize, _
maxSize, _
timeout, _
service, _
userPass, _
0
End Sub
Sub Application_OnEnd()
' Release the connection pool
OraSession.DestroyDatabasePool()
End Sub
</SCRIPT>
I thought that my biggest worry would be a TNS name resolution problem, but apparently I am getting into the db, it's just not finding a free object "from the pool".
Is there a correction I need to make to that APPLICATION_OnStart() function?
You should look at the contents of the web server log to look for error messages
that are generated during application startup. A guess is that you does not have
the service configured properly and it can't create the connections. You could
try running one of the demo applications in the OO4O directory to see if he has
his system configured properly.
You should also not use the SYSTEM account.

Categories

Resources