Even if you are using sqlExecute use bind variables. You should get about a
10x performance improvement and you will lower the CPU requirements and
resource usage on the server. You need to close cursors. I think
sqlExecute does open a cursor.
You are probably doing something like
sqlExecute("insert into mytable(col1,col2,...) values( 1,43,'hi',...)"
repeat.
That needs bind variables. You are forcing a hard parse for each statement
like that. Unscalable. Also you should use the array interface if you are
inserting multiple rows. Much more efficient.
Jim
"M" <ihatespam.0.a101888_at_spamgourmet.com> wrote in message
news:rFScd.104894$BI5.24790_at_fe2.news.blueyonder.co.uk...
> Hi Jim,
>
> First of all THANK YOU for your insight!
>
> Binding variables might make sence if I was openening a recordset then
doing
> various opperations on it, but I am not. All I am doing is firing execute
> sql statements at the database, so I'm not really sure how you would bind
> varaibles under these circumstances.
>
> Originally I wrote my own wrapper class for ODBC, but I came accross
another
> wrapper class written by someone else, which appeared to be MUCH better
> written than my quick and dirty class. Anyway I had a look at what I was
> doing in my old class and what the new wrapper class was doing when it did
> an SQLExecute. What I found was that the new wrapper class did not seem to
> tidy up in the way I would have expected (possible for other good
reasons).
> Anyway I have added an additional method using the code from my original
> wrapper class, which tidies up correctly after the SQLExecute method has
> been called.
>
> I probable need to do some more testing to make sure everything is still
OK,
> but what I have done, seems to have got shut of the problem, and I am
> getting what I want, at least for now! :-))
>
> The analogy with allocating memory seems to be an interesting one,
something
> I shall keep in mind.
>
> Anyway MANY THANKS, although what you said didn't initially make a great
> deal of sence to me, it did at least point me in the right direction.
>
> Cheers,
>
> Martin
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:wAzcd.197890$wV.73603_at_attbi_s54...
> >
> > "M" <ihatespam.0.a101888_at_spamgourmet.com> wrote in message
> > news:_2xcd.79699$ay5.9643_at_fe1.news.blueyonder.co.uk...
> >> Hi Folks,
> >>
> >> I have got an C++ application that is using Oracle as the backend
> >> database, using ODBC drivers and BeginTransaction, Commit & Rollback.
> >> What
> > I
> >> do is send loads of Update SQL Statements to the database and when the
> > user
> >> is ready they commit the changes. The problem I have got, is that when
a
> >> large number of records are involved eg. 100 I get the following error:
> >>
> >> ODBC "ORA-01000: maximum open cursors exceeded"
> >>
> >> Having done some Googling it appears that the maximum default
number
> > of
> >> cursors (presumable 1 cursor / update SQL statement) is only set to 50.
> > The
> >> only way to get round this seems to be to modify open_cursors=50 in
> >> INITORCL.ORA to some other number.
> >>
> >> Is there a way to modify this number at run time (i.e.
> >> Programmatically)?
> >>
> >> Any help would be most appreciated.
> >>
> >> I thought the whole point of using an Oracle database is so that it
> >> could process LARGE numbers of records! 50 seems to kind of defeat the
> > whole
> >> object of the exercise! This just seems to be TOTALLY BIZARRE to me!
> >>
> >> Martin
> >>
> >>
> > You can change the parameter, but it is a static parameter. It is a
limit
> > per session not database wide. (each session can have at most x number
of
> > open cursors). You might have an poorly written application and raising
> > the
> > limit will just delay the problem for a little while. (Like haveing
memory
> > leaks in an application, you can add more RAM but you would just be
> > pissing
> > into a bigger pool, not solving the problem.)
> >
> > Here are my guesses at what is going on:
> > 1. If you are not using bind variables (and you should use bind
variables)
> > then you are opening a cursor for each insert into a table. (and never
> > closing it) Don't this is like a memory leak. At hre very least you
> > should
> > close the cursor when you are done and if you are not going to reuse it.
> > 2. You are opening cursors, not closing them and not reusing them. (read
> > the
> > application programmer's guide)
> > 3. You should be using bind variables and reusing the cursor.(for the
same
> > statement with different bind values) Open the cuursor once only, and
re
> > use it again and again. Close when exiting the application. Then you
can
> > set your number once and not worry about it.
> > 4. If you use bind variables and are doing a lot of inserts, updates,
> > selects, deletes etc. on a table then use the array interface. Much
> > faster.
> >
> > Jim
> >
> >
>
>
Received on Tue Oct 19 2004 - 08:52:02 CDT
