Solved

OraSession Object

Posted on 2003-11-16
5
1,143 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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