Solved

Oracle 9i not saving from ASP classic

Posted on 2011-03-11
18
679 Views
Last Modified: 2012-05-11
Hello fellow experts,

We're having a problem with a client that's definitely puzzled me and I'm out of ideas.

We have an application developed using ASP classic that we normally use with SQL Server, but we do support Oracle 9i, it usually works fine even if we're not experts on Oracle but this week a client chose to migrate the system and it stopped working.

The workflow is pretty much the following:
1. IIS delivers some ASP classic pages that display questionnaires and the like. This is the usual HTML/JavaScript stuff.
2. Once the user finishes answering the test, the answers are saved in a "raw data" table, and sets the status as "not-processed". This works fine and the data is saved properly.
3. Here's where the tricky stuff starts to happen, after the raw answers are saved, ASP calls a (previously registered) DLL (written in VB6) to process the raw data and does two things:
3.1 Writes the "processed" values in a new table. (This fails)
3.2 AFTER 3.1, it sets the status as "processed" in the "raw data" table. (This works fine!, so the DLL is working OK)

So I have one theory, and that is that whatever is happening in the DLL isn't being commited (and never is), although UPDATES work fine.

The odd (or one of the odd) thing is that on the previous server with the "exact same"  configuration (according to the client, I honestly don't believe it), the same system, same files, same everything worked OK.

I'm not sure if there's a way to configure Oracle 9i to commit all the time, maybe the Batch AutoCommit Mode on the ODBC? (we're using an old connection string and this just came to mind).

We even modifed the DLL and added the lines
conn.BeginTrans
conn.Execute (SomeQuery)
conn.CommitTrans

And nothing happened.

More data: server is a Windows Server 2008, IIS 7, Oracle 9i.

Any ideas?

Thanks in advance.
0
Comment
Question by:Antonio Estrada
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 11

Expert Comment

by:kbirecki
ID: 35114105
I don't have any experience with oracle, but I use ms sql, and the way I'd approach this if to modify the dll to write a text log of the sql commands being sent to the oracle server, then manually execute them while logged into an interactive query window with the same user account that the dll uses.  This verifies the query being executed and that the user account has necessary permissions, both of which are important to verify (maybe the user account would be more important to verify if this is on a new sever with the same software.)   That's my first idea.
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35114155
One other idea is to separate the dll from the application in a test environment where it is given known working sample commands and see how it works then.  In my experience, it is best to break these kinda of problems down by splitting into manageable components until you know exactly whee the problem is (kind of like the old binary sort algorithm) but in this case, is the problem in the dll or the communication with oracle?  If it is in the communication with oracle, is the problem on the sending side or receiving side, and so on.
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35114215
Thanks for the quick response, kbirecki.

We actually do have a "debug" option on the DLL that writes to some file but our client was reluctant into giving write access to the C: root folder (that's where we dump them), I also agree with it being a good idea so I guess we can change the destination folder so they agree to give us permission.

Sadly I can't access the server over the weekend but I'll definitely try this. Meanwhile I'll wait for some other ideas to rack up, again thanks!

-V
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35119843
May I ask what exactly was upgraded? Oracle version and or db OS?

I m not sure but  the normal practise is to upgrade on the test server and execute the scenarios before prod migration with obviously limited privs. Also u should have a full acces on test sever to debug and correct issues  
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35123444
Hello ajexpert,

Yeah well, our client didn't see it that way... they were adamant to migrate and the only thing we have is a remote access through TeamViewer that's made available to us per request.

Our application works fine with Oracle 9, we haven't tested with newer versions because like I said in my original post, it's not our niche market. That said, they installed the Oracle 10 client on the new server and it obviously didn't work. After we asked them to simply install the Oracle 9 client, it worked ok (also after giving permissions to the oracle folder).

As far as I know, the DB server wasn't changed at all, but our client has withdrawn information from us before so I'll keep that in mind too.

-V
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35134072
Hello everyone,

After doing some testing on the server, and finally getting the logs to work, I could prove that the DLL is working fine, all the statements are being executed (otherwise the log fails) and still no sign of them on the system.

What I think it is, is that the IIS process is being executed by one user (Network Service) and perhaps the DLL is using another user or maybe another instance of the same user (is that possible?)... so anyway, the data is not being saved, although I assume that if I could "keep" the DLL user session "alive", I could see the changes in the DB.

Is there any way I can check that?

-V
0
 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 200 total points
ID: 35134272
I'm thinking that whether or not IIS and your DLL are using the same or different user accounts does not matter, as long as the IIS app is passing the info to the DLL and the DLL is confirmed to have receive it.  There should be no problem with using two different user accounts.  In fact, this is common, many services use the same account.  As far as I know, there aren't "instances" of a user; if both IIS and the DLL use the same user account, it's like two users authenticating with the same acct.   The real question in this area is does the account used for the DLL have the necessary SQL permissions on the Oracle server?  One way to test this is to log into an SQL IDE as that user and execute the exact command from your log tha tthe DLL shouldbe executing and see if they work - that would tell you if there is a SQL command problem.  

Another way is to trace the SQL activity on the Oracle server.  On MSSQL it's called "SQL Server Profiler" (not sure what it is on Oracle, but they will have a tool like this.)  It basically traces all SQL commands to the server, in as much detail as you could ever want.  You can set it to filter on a specific db and a specific user account used by the DLL so that you limit what you are watching, assuming you have a dedicated Oracle db user account for the DLL.

That's at a high level in the OSI model.  You could also go to a lower level and use a network sniffer like Wireshark (free but *very* powerful) or a similar commercial package to trace the exact traffic flowing out of and in to the server running the DLL.  Given that it sounds like the client is pretty tight on allowing access, this may be more difficult to get their permission to do.  But with the understanding of how these tools work, you can be very certain of exactly what is going on.  You see the exact conversation between servers - is the client not authenticating to the db server, are the SQL commands even leaving the DLL server, are they reaching the db server, is the server responding/acknowledging or not, is the network playing a role in the problem, or something else?  All of this and more can be determined with tools like this.  Wireshark needs some understanding to interpret the results, but some commercial apps, like Network Observer (a commercial network monitoring tool that I use, but I'm no expert), is extremely helpful because it will collect and show you whole conversations in an easy-to-view format.

The first two options are by far going to be the easiest to try first and I'd say these techniques help me identify 90% of the problems I've had.  If you are spending a lot of time troubleshooting this, you might want to consider finding an Oracle expert to subcontract to help find the issue.  You don't have to explain to the client who they are if you don't want to, and (this is a key reason why I do this) you will learn a lot from them so that you know how to do this kind of troubleshooting in the future.

(Did you see that light buld go on?)  I just had another idea.  Port ping.  I had one once that you can ping a certain port on a remote server (not ICMP).  I'll try to find it.
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35134377
I just realized a quick and dirty way to test if a port is responding is using telnet.  I just tested it against my MS SQL server on port 1493.  Don't expect any response, just look for a lack of failure to connect.  In my case, a successful verification of a response on a port is a blank screen, while a failure is an outright message that telnet could not open a connection to the host on the specified port.  One article that discusses the ports Oracle uses is http://www.dbasupport.com/oracle/ora9i/connecting3.shtml.

The simple DOS command I used is "telnet IP portNumber" (you can use IP or serverName, i.e. "telnet SQLServer 1493".  1493 is the MSSQL port for data communications.  Oracle may be 1521.  I got a blank screen when I telnet'd to 1493, but an outright failue at 1499, a port the SQL server does *not* respond on.

This would really just be a way to verify the Oracle server is responding to db traffic from the server the DLL is running on in case there are any network issues preventing it.
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35134751
Hello kbirecki,

Thanks a lot for the deep input you've provided so far, Wireshark definitely sounds as a very good idea and I'll try to lure the client into giving in, but it'll be tough haha.

The Oracle expert definitely sounds like the best idea though, 'cause right now I'm just guessing and testing wild theories based on hunches and nothing concrete.

About pinging the server, I'm not sure if that could be the issue, given that I can successfully connect using SQL*Plus, and even the IIS application works fine (and successfully communicates with the server), and both the DLLs and IIS are in the same server.

I'll try these options and see if any does the trick, otherwise... well I think the Oracle expert would be the way to go...

Again, thanks a lot, I'll keep you posted.

-V
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35134898
are you able to do a simple select query to any of the Oracle 9i table from the ASP page via the DLL and display the data? this is to test for the DLL connection to the database...
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35134932
Hello OP_Zaharin,

Well, I haven't tried that specifically, but ASP saves on the "raw data" table the results and sets its processed status to zero, then the DLL reads that table and tries to process it, the SQL command (INSERT INTO processed VALUES(1,'processed results');) actually appears in the log generated by the DLL, so it can successfully read the database.

-V
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35134971
i would like to clarify the syntax to (INSERT INTO processed VALUES (1,'processed results'):
1-i assume that processes have only two fields?
2-you can also take out the ';' from the syntax
3-can you share with us the open database codes for conn?
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35135036
Well, it was just random stuff I added there, the raw data usually goes here:

TestAnswers
-----------------
UserId (integer)
TestId (integer)
aDate (datetime)
Answers (varchar(4000))
Status (smallint)

TestAnswers saves the actual answers the user selected, as in 'ABDACDC' or '1244523212', "Status" can be either 0 or 1, and denotes if the answers have been processed and saved in the HistoricUserTest table, when the user saves the results, it's set to 0 (not processed) and after the DLL reads and processes them, it's set to 1.

HistoricUserTest
--------------------
UserId (integer)
TestId (integer)
pDate (datetime)
Results (varchar(4000))

This table is pretty straightforward, it simply has the processed values after the DLL executes the algorithm.

This is the actual code:

sqlstmt = "INSERT INTO HistoricUserTest (UserId,TestId,pDate,Results) VALUES (" & UserId & "," & TestId & "," & DateTimeCondition(Date) & ",'" & results & "');"

"Results" is a string generated by the algorithm, and according to the log it's done alright, actually, if I use the statement in SQL*Plus or Toad or whatever, it works fine, so it's not malformed.

The open code is actually executed on ASP, and the "conn" is sent to the DLL as a public property.

The connection string is this:

Application("conn_string") = "DRIVER={Oracle en OraHome92};SERVER=" & Application("conn_servername") & ";UID="& Application("conn_username") &";PWD="& Application("conn_password") & ";DATABASE=" & Application("conn_dbname") & ";DBQ=" & Application("conn_dbname")

The open code is this:

  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open conn_string, conn_username, conn_password

And it gets sent to the DLL here:

    set obj = CreateObject("WebImportAXv11.WebImport")
    set obj.DB = conn

"conn" is a global object

Hope this helps, thanks for the input!

-V
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 300 total points
ID: 35135336
thank you for the details explanation Vulturous - it get much clearer now.

what i can suggest is that you put error trapping on top of the insert module to capture the error and save it to a log file (might be the error is not captured in case you are using on error resume next).

Assuming the codes look like the below:
on error goto errTrap

sqlstmt = "INSERT INTO HistoricUserTest (UserId,TestId,pDate,Results) VALUES (" & UserId & "," & TestId & "," & DateTimeCondition(Date) & ",'" & results & "');"

conn.BeginTrans
conn.Execute (sqlstmt)
conn.CommitTrans

Exit Sub
errTrap:
' put either display to a messagebox or insert to a log file

MsgBox Err.Number & ":" & Err.Description & " in " & Err.Source

End Sub
0
 
LVL 18

Author Comment

by:Antonio Estrada
ID: 35151695
Hello OP_Zaharin,

Sorry for not getting back to you yesterday, was busy with some other stuff. I didn't remember if I had or not an "error handling" on the code, and there it is, so I'll add a debug for the errors and see if anything comes there.

I'll let you know...

-V
0
 
LVL 18

Assisted Solution

by:Antonio Estrada
Antonio Estrada earned 0 total points
ID: 35160511
Sooo, the problem is finally solved.

Although the DLL receives the connection object fully and doesn't open the DB again, we check the ConnectionString of the object to see if it includes the word "Oracle", if it does, then it changes certain bits of formatting or the BeginTrans and CommitTrans instructions, otherwise it doesn't execute them.

Turns out that for whatever reason it wasn't working on this specific server, and the ConnectionString was pretty much empty, and it never knew it was Oracle.

So we changed the ASP code and the DLL to include a public property named "dbtype" and send the string in there. Once we did that, everything worked fine.

Thanks for all the help everyone!

-V
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162432
great V :)
0
 
LVL 18

Author Closing Comment

by:Antonio Estrada
ID: 35187458
Thanks for the help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now