Re: SQL OLEDB Provider problem
- From: "PullWood" <a.tirabosco@xxxxxxxxxxxxx>
- Date: Thu, 26 May 2005 13:31:46 +0200
Dear Stephen,
I first want to thank you for your proof answer. The code I use in C++ and
is the following :
THROW_ERR( m_piRecordSet.CoCreateInstance(CLSID_CADORecordset) );
THROW_ERR( m_piRecordSet->put_Source(varSQL) );
THROW_ERR( m_piRecordSet->putref_ActiveConnection(m_piConnection) );
THROW_ERR( m_piRecordSet->put_CursorLocation(adUseServer) );
THROW_ERR( m_piRecordSet->Open(vNull, vNull, adOpenKeyset,
adLockPessimistic, adCmdText) );
and I want to underline that using the SQL ODBC Driver I don't have any
problem, but only with the SQLOLEDB driver.
I've verified, as you suggested, what happens using the ServerSide cursor:
When I open the recordset in run-time mode (using the debug compiled
process) without Visual C++ as a normal process.
the rs.open give me back a LockType 1 (ReadOnly) ad a cursor type 0
(forwardonly)
But if I debug the same process inside DevStudio it works and it returns the
requested locktype and Cursor type...very strange!!
How can I get a recordset updatable with a serverside cursor on this
query...is there anyway?
And why the choosing of the locktype depends on the type of query we are
doing using a server side cursor, it's quite strange to me, because on other
queries this doesn't happen?
Note: the query returns more or less 50.000 records, if this can help.
Thank's a lot again.
Andrea
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:e0Ox21VYFHA.3712@xxxxxxxxxxxxxxxxxxxxxxx
> > 2 - When using SQL OLEDB Provider along with ADO I got several strange
> > things:
> >
> > The CursorLocation Param:
> > Using a Client Side Cursor I've No Problem, but perfomances are really
> > worst.
>
> Several things:
>
> (i) Be aware that ADO coerces CursorTypes and LockTypes.
> You may do RecordSet.Open() with certain types and they are not the real
> CursorType and LockType.
> ADO will choose something available. For client-sided cursors, all cursors
> are opened as adOpenStatic no matter what is requested. And for
> client-sided, all adLockPessimistic are opened as adLockBatchOptimistic.
> After a successful RecordSet.Open() you can print out the CursorType,
> LockType to see what you really got.
>
> (ii) For client-sided cursors, the statement rs.Open() won't return until
> all records are built on the client side in memory. And if that is a drain
> in terms of memory due to size of tables and/or multiple joins, Windows
will
> be page swapping on the client machine. That is quite different from
> server-sided cursors where the Server where SQL Server is installed takes
> the strain. rs.Open() may return with only a few records in whatever size
> cache you have set up in client memory. Much less memory intensize.
>
> There is nothing wrong with client-sided cursors. They can be very useful.
> But I would not use them for huge queries.
> You need to learn the different strengths/weaknesses of each cursor type.
>
> > Using a Server Side Cursor I got a strange behavement:
> >
> > When I use a CursorType = adLockOptimistic in those queries the
outcoming
> > recordset doesn't support updating at all.
>
> Depends on what you did.
> I don't believe CursorType has anything to do with it.
> It is to do with the method used to create the Recordset.
>
> Was it
> (a) rs = con.Execute();
> (b) rs = cmd.Execute();
> (c) rs.Open()
>
> where rs is RecordSet Object, cmd a Command Object, con a Connection
Object.
>
> For (a) and (b), the recordset will always be either ForwardOnly,ReadOnly
or
> Static,ReadOnly depending on the underlying Connection objects
> CursorLocation property. For (a) & (b) you cannot get a non-ReadOnly
cursor
> returned, period. If you wish for a different combination of CursorType,
> LockType, you have to use a Command Object with connection and Recordset
> Object with NO connection and do rs.Open() with the Command Object as
> Source.
>
> For (c) you can specify all combinations of CursorType and LockType. SQL
> Server will change them depending on CursorLocation and whether a Table
has
> indexes (and maybe JOINs come into this picture - I have not checked).
>
> > when Using a CursorType = adLockPessimistic in the same queries, not
> working
> > with the adLockOptimistic Cursor, work...but only when I debug with
> > VisualStudio6 Opened. When using the same debug process(is a service) in
> > run-time mode (without visual studio opened) it fails for each update
when
> > putting value in the field object.
>
> No idea what you did in terms of code.
> The above does not tell me what you did.
> If you have a Recordset, I would strongly suggest you print out/display
> CursorType and LockType.
> If LockType is adLockReadOnly, there is no way the Recordset can be
updated.
>
> > That with an an error saying the the
> > provider or the cursor type do not support updating on that recordset.
>
> Depending on the code executed, that may well be right.
>
> > The
> > error given is the Ox800A0CB3
> > and the query example where I get this problem is :
> > "SELECT * FROM DB_Titoli_RiskMap WHERE Date_Agg IN (SELECT max(Date_Agg)
> > FROM DB_Titoli_RiskMap ) ORDER BY ID_ISIN"
>
> Yes, but you do not show the code you executed.
> That is what matters, not the query above.
>
> Cheers
>
> Stephen Howe
>
>
.
- References:
- SQL OLEDB Provider problem
- From: PullWood
- Re: SQL OLEDB Provider problem
- From: Stephen Howe
- SQL OLEDB Provider problem
- Prev by Date: RE: Accent insensitive dataview.rowfilter
- Next by Date: Re: Best practices in ADO
- Previous by thread: Re: SQL OLEDB Provider problem
- Next by thread: Accent insensitive dataview.rowfilter
- Index(es):
Relevant Pages
|
|
