Solved

OraSession Object

Posted on 2003-11-16
5
1,134 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 250 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to show result of a NULL value at random? 33 60
If-Then-Else ASP problem 6 78
Select record with the most recent date 14 72
Add Rows on a Table 8 51
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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