Re: Now and then wrong data is inserted in oracle

An example would go a long way towards allowing people here to figure out what's wrong. However, lacking that, here are my thoughts:

You say the data is "wrong". How wrong, exactly? Are some of the rows resulting from the operation in error, or does the whole thing seem fouled up?

It is unlikely that it is an Oracle or ODBC problem. My guess would be that

1. You have a race condition in your file retrieval code.
2. You have some unitialized data that is being re-used in subsequent scans

If bad data is manifesting itself as completely messed up results, or as incomplete results, I would guess it would be #1. The most common problem in this kind of application (where a client app waits for a file to appear, then processes & sends it), is that you app is picking up the file before it is completely written out. If you are scanning a directory and waiting for the file to appear, you can easily run into this problem. Typically, the way we've gotten around this problem is to have the file-dropping process copy the file to a staging directory on the same disk, then once the copy is done, use "mv" (if using unix) or "move" (if using windows) to put the file into the scanned directory. The other way is to have the file-dropping program drop a second "terminal" file that the scanning program detects, and once it detects it, it looks for the corresponding real file (For example, after dropping file foo.txt, you drop a file called DONE_foo.txt, which your scanner would interpret to mean "now process file foo.txt").

If your bad data just shows a limited number of screwed up items, it's probably because your scanning app is re-using global variables from a previous file and not re-initializing them. Since Visual Basic automatically sets all variables to some initial value (such as 0 or ""), it is easy to forget to initialize them. But if you are using global (or module-level) variables as scratch space, you may be polluting your data in subsequent runs. Make sure that you're initializing all non-local variables that you use before processing a file. This all assumes that your scanning app runs continuously and may process many files in its lifetime; if you are running the file processor fresh for every file, it is less likely that this is a problem.

-BbT

jeffreybultinck_at_hotmail.com (Jeffrey) wrote in message news:<6eca5fca.0412020236.a08c148_at_posting.google.com>...
> VB 6.0
> Oracle version 8.1.7
> Webserver OS: Windows 2000
>
> The problem is very simple: we are loading data from an XML file in an
> oracle database. The data processed in the database is sometimes
> different from what we have in the XML file. This is occuring once in
> a while. The VB component is scheduled in coldfusion via an asp page
> every 10 minutes. There is a check if the component is already
> running, there can be no overlap! The asp pages, component and XML
> files are on the webserver.
> I have ran through the code step by step for the "corrupt" files but
> when I do that it is processed just fine. I have ran already a lot of
> other test cases. I've even reloaded all the files we received for one
> day in our development, test and production environment all processed
> ok. I am not able to simulate the problem. I am not saying it is an
> oracle issue. I can just give you the facts we are dealing with. It
> could be either VB, Oracle, the connection between the webserver (XML
> files are stored here) and the database server or a combination. I
> really don't know.
> Anyway already thanks for your friendly advise!
Received on Thu Dec 02 2004 - 10:24:19 CST