Solved

OraSession Object

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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 information …
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/…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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