Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

OraSession Object

Posted on 2003-11-16
5
Medium Priority
?
1,182 Views
Last Modified: 2008-02-01
A user will upload an Excel file and file.asp will parse the excel records and inserts the data in an ORACLE table.  I use the OraSession object that I defined in global.asa (see source codes below). Is there any other way of doing this without using the OraSession object.

--Global.asa
<OBJECT RUNAT=Server SCOPE=Application ID=OraSession PROGID="OracleInProcServer.XOraSession"></OBJECT>

--file.asp
                  Set rec1 = Server.CreateObject("ADODB.Recordset")
                  Set rec2 = Server.CreateObject("ADODB.Recordset")
                  Set rec3 = Server.CreateObject("ADODB.Recordset")
                  Set recC = Server.CreateObject("ADODB.Recordset")
                        
                  dim adoCn
                  dim adoRs
                        
                  Set adoCn = Server.CreateObject("ADODB.Connection")
                  Set adoRs = Server.CreateObject("ADODB.Recordset")

                  strExcelPath = strServer & "\" & strFileDir & "\" & sFilename
                  With adoCn
                        .Provider = "Microsoft.Jet.OLEDB.4.0"
                        .ConnectionString = _
                              "Data Source=" & strExcelPath & ";Extended Properties=Excel 8.0;Persist Security Info=False;"
                        .Open _
                              "Data Source=" & strExcelPath & ";Extended Properties=Excel 8.0;Persist Security Info=False;"
                  End With

                  if sheet_name <> "sheet1" then
                        strQuery = "SELECT * FROM [" & sheet_name & "$] "
                  else
                        strQuery = "SELECT * FROM [Sheet1$] "
                  end if
                        
                  With adoRs
                        Set .ActiveConnection = adoCn
                  End With

                  adoRs.Open strQuery, adoCn, adOpenKeyset

                  MyDb = "devdb01.sin.infineon.com"
                  MyUser = "user/password"
                  MyTb = "str_x_meeting"

                  dim OraDynaset
                        
                  'Create the OraDatabase Object by opening a connection to Oracle.
                  Set OraDatabase = OraSession.OpenDatabase(MyDb, MyUser, CInt(0))

                  'Create the OraDynaset Object.
                  StrSQL = "SELECT * FROM " & MyTb & " "
                  Set OraDynaset = OraDatabase.CreateDynaset(StrSQL, CInt(0))

                  FldCnt = OraDynaset.Fields.Count
                  intcnt = 1

                  sSql = "Select str_meeting_id_seq.nextval m_id from dual"
                  rec1.open sSql, con
                        meeting_id = rec1("m_id")
                  rec1.Close
                        
                  Do Until adoRs.EOF             
                        With OraDynaset
                              .AddNew
                              For Each adoFld in adoRs.Fields
                                    'Assigning value to the recNew Recordset for Insertion
                                    if adoFld.Name = "Issue/Information" then
                                          .Fields("Issue").value = checkUmlauts(adoRS("Issue/Information").value)
                                    elseif adoFld.Name = "Who" then
                                          .Fields("Assignee").value = checkUmlauts(adoRS("Who").value)
                                    elseif adoFld.Name = "Actions" then
                                          .Fields("Action").value = checkUmlauts(adoRS("Actions").value)
                                    elseif adoFld.Name = "Due Date" then
                                          if isdate(adoRS("Due Date").value) then
                                                .Fields("Due_Date").value = adoRS("Due Date").value
                                          else
                                                .Fields("Due_Date").value = ""
                                          end if
                                    else
                                          .Fields(adoFld.Name).value = adoRs(adoFld.Name).value
                                    end if
                              Next
                              .Update
                              sSqlUpd = "Update str_x_meeting set meeting_id =" & meeting_id & ",meeting_type_id=" & meeting_type_id
                              sSqlUpd = sSqlUpd & " ,subject='" & subject & "',meeting_date='" & formatdatetime(meeting_date,1)
                              sSqlUpd = sSqlUpd & "',organiser='" & users_id & "',users_id='" & users_id & "'"
                              if not(subtopic ="" or IsNull(subtopic)) then
                                    sSqlUpd = sSqlUpd & ",subtopic='" & subtopic & "'"
                              end if
                              sSqlUpd = sSqlUpd & ",item_id=" & intcnt & " where meeting_id is null and meeting_type_id is null"
                              cmd.commandtext = sSqlUpd
                              cmd.execute

                              sSql = "select upper(category) category from str_x_meeting where meeting_id=" & meeting_id
                              rec1.open sSql, con
                              if not( rec1.EOF or rec1.BOF) then
                                    catg = rec1("category")
                              end if
                              rec1.Close

                              sSql = "Select category_id from str_t_meeting_catg where meeting_type_id = " & meeting_type_id & " and UPPER(category)='" & ucase(checkUmlauts(catg)) & "'"
                              rec1.open sSql, con
                              if not rec1.EOF then
                                    sSqlUpd = "Update str_x_meeting set category_id = " & rec1("category_id") & " where meeting_id = " & meeting_id
                                    cmd.commandtext = sSqlUpd
                                    cmd.execute
                              end if
                              rec1.Close
                              intcnt = intcnt + 1
                              on error resume next
                        End With
                      'Moving to Next Record
                      adoRs.MoveNext
                  Loop
                  
0
Comment
Question by:shlikjohn
  • 2
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 750 total points
ID: 9761241
I think that you can create the object in the page instead:

Dim objOraSession
Set objOraSession=Server.CreateObject("OracleInProcServer.XOraSession")
0
 

Author Comment

by:shlikjohn
ID: 9761553

That was my first approach before but I got an error.
And that is the reason why I put the code in global.asa but anyways I will try it again and hope it will not get any error.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9761645
Totally unrelated, but are mixing DAO with ADO? And if so, out of curiosity, is that really necessary?

Anthony
0
 

Author Comment

by:shlikjohn
ID: 9761696

so how can you reference the worksheet in excel and treat it as a table?

have any good ideas?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9764007
>>have any good ideas? <<
Not sure if that question was directed at me, but I always have good ideas <g>

>>so how can you reference the worksheet in excel and treat it as a table?<<
I have no problem with the way you are opening the Excel spreadsheet, it is more with the way that you are opening the Oracle database.  I would use ADO, rather than DAO.  i suspect it would be far more efficient. ADO was designed for web based apps, DAO was not.

But again, this has nothing to do with your immediate problem and I would follow GreenGhost's suggestion.

Anthony
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question