More ODBC Nightmares and Migranes

[namliam]

I have a nice application running for a while now... which does an awfull lot of ODBC calls to an Oracle database... All is fine and well...

Lets set the scene...

I am connecting using something like:

    Set wrkODBC = CreateWorkspace("myODBC", User, PW, dbUseODBC)
    Set con = wrkODBC.OpenConnection("Connection1", , , _
        "ODBC;DSN=" & DSNT & _
            ";UID=" & UserT & _
            ";PWD=" & PW & _
            ";SERVER=" & Server & _
            ";")

Then building the query i want to run on the connection
set qry = con.CreateQueryDef("", mySQL)

Then loading the query into a recordset...
Set rst = qry.OpenRecordset

and then handling it ... no problems to mention off (except maybe .... this one which i still have not been able to track down )

Anyway, now i would love to store the information for additional use into my db. Opening another recordset and adding the data to the table. Except now after about 30 or so records the ODBC Connection plonks out,
for no appearent reason, timeout is set to 0, query is 100% OK

If i restart from the point i left off the application continues, but after another 30 or so it crashes again.... When i kill the saving of the ODBC data it works fine... I have checked, double checked and triple checked.... and am now finaly at a loss...

Could it be a problem with opening and closing to many recordsets?? I am thinking as i am writing this... anybody know??

Anybody have any other ideas?

BR & TIA

 

[namliam]

Nobody in the house have any advanced knowledge on ODBC to Oracle?

 

[namliam]

I see your point Pat tho i need to retrieve multiple records, posibly even store the information localy, and check them against certain parameters.

I have tried doing SQL only but no such luck... So its not a matter of wanting but rather a matter of having to...
When running the full desired SQL the fastest i got was an average of near 6 secs response time from Oracle. The way i have it set now i have it under 1 second including the full local checks... (The oracle DB i can do nothing about , it is third party)

I am not sure tho that the problem is ODBC specific, i am working with 4 or 5 recordsets having to open and close 3 or 4 everytime... I am guessing at this moment its some kind of memory thing... as i am creating temp querys set qry = con.CreateQueryDef("", mySQL)
I have been succesfull at rigging some error trapping, disconnecting and reconnecting and requerying the oracle database, tho its a pain.... but for now i will have to live with it.

Just as the other problem (linked in my original thread started) the app has been working fine for a long long loooooong time (over 2!!!! years) and all of a sudden this pops up...
I even went to the DBA: Did you change anything anywhere anytime the last week?
Answer (offcourse): No, its been running perfectly all year. All we had to do was run our backups... bla bla bla...
When i know for a fact they had to do a couple of interventions to prevent hard crashes and do atleast 2 restores... A pain those DBA's

Tho (as with the other problem) i seem to have found a workable workaround I find it totaly unacceptable and will try and keep digging away at this (and other stuff) to find the tru cause of my problems. Any ideas you (or anyone else) have are totaly appriciated...

BR

 

[namliam]

The strange thing about this is that we have been doing up to 10,000 checks a day without problems, even slowing (what i would expect as well) did not happen. Only now that the number have dwindled to max 1500 a day (for the time beeing) this has pupped its ugly head...

I guess i could do with a few less openings and closing if i made some recordsets global. What are your thoughts on global recordsets?

Regards

 

[namliam]

Have the DBA admitt to something like this? Impossible!

Stubburn pigheaded **********************************

Anyway I think i have the problem tackled 'for now'

Thanx for your input, just one last thing. How do you feel about making public recordsets to keep from opening and closing in different modules....
That way one could open the recordset once use it to append and change. Then at the very end close the rst.... Would that help? or cause even more bloat due to the public thing?

Regards

P.S. * I censored myself to keep from getting banned from the forum.

 

[namliam]

I have been testing my public recordset and have found that (with the error trappin on lost connection OFF)

1) On just about any crash the recordset stays the recordset, approachable and updatable.
2) It crashes less often, but still crashes.

My conclusion, some one has changed something somewhere, and I am betting DBA. But he says no, and he (still) is god all mighty in this company so i will have to live with it.

The reconnecting trap works (for now) costing me minutes extra, but heck... It is still a 1.000 times faster than if i would do it by hand.So who cares right?

If you should ever find anything on recordsets lossing connection and stuff, i would appriciate a post or a PM on where to find it (outside of the regular MS and such articles i have not been able to find any with new information. Other than "a recordset creates overhead" "A recordset may leak memory" etc...

Thanx for you thoughts and effort into this...

The Mailman

 

[MrTibbs]

Oracle 30 record limit ....

WARN: I am not an Oracle DBA but I do have Access 97 interrogating an Oracle 8i database via ODBC.
There are options as somewhere in the ODBC setup that says 'only return X records' at a time. This usually has no effect because the query returns multiple 30 record chunks until all expected records are transferred. 30 records sounds suspiciously like hitting this or a similar limit. Perhaps it became a problem after these interventions.

 

[namliam]

And so i learn another thing... Thanx for the input, but they are 30 seperate queries, intermittenly running recordset even over the local database.

I cannot really see a connection unless there might be some limit to how many query's you can run within a minute (or something like that), Running something like a 100 (old times before these problems) 1, 2 or 3 record queries a minute ... I cannot imagine that beeing much of a problem... can you?

I appreciate any info, so keep it comming... In hopes of one time figuring out what the nice mr DBA did, so i can rub his nose in it

Regards

 

[namliam]

I have not found anything new yet, just trying to get some attention maybe someone else is now online who knows a tidbit?!

Thanx