Re: nVarchar limitations using OLEDB provider

Erik,

I get the same results that you do using your sample code as a starting
point. I agree that it's odd that you can SELECT nvarchar columns <= 127 but
not nvarchar(128) or greater using a clientside cursor. Since clientside
cursors do work otherwise and UPDATE statements also work fine for
nvarchar(128), that looks like a bug to me. I should point out that using
the OleDb provider for SQL Compact Edition the way we are doing isn't
officially supported in any case, although it does mostly work as expected.

It appears to me that the bottom line is this: if you need to write an app
that allows you to display and update SQL Compact Edition data in a grid,
you're really better off writing that app in a .NET language so you can use
SqlCeResultSet instead of the OleDb provider.

--
Ginny Caughey
Device Application Development MVP


"Erik Visser" <evisser@xxxxxxxxxxxxxxxxx> wrote in message
news:OxgALrhEHHA.3596@xxxxxxxxxxxxxxxxxxxxxxx
Miro,
Yet another limitation. The problem is you can only have a readonly
locktype.
If you need to update a row, you should build an updatequery.

hth
Erik

if I use OLEDB provider for SQLCE in my Delphi programs I cannot edit
data in a grid too.
With other OLEDB providers my Delphi programs works fine.

Here is my error message "Access violation at address 77C37FD4 in module
'msvcrt.dll'"
if I use one of adOpenKeyset, adOpenDynamic, adOpenStatic (Cursor type
constants)

I can't use LockType other than ReadOnly.
I can't use CursorLocation other than adUseServer

Please help!

Miro

"Erik Visser" <evisser@xxxxxxxxxxxxxxxxx> pí¹e v diskusním pøíspìvku
news:ul7OCcUEHHA.3520@xxxxxxxxxxxxxxxxxxxxxxx

If this is a bug or a limitation or anything, I do not know. I do know
other
databases / OleProviders that support a clientside cursor, do not have
this limitation. But I have no problem in using a specific
cursorlocation on a mobile device, as the file is local anyway.

This was not the most clever remark.

1) On a device I won't be using the OLEDB provider.
2) On the desktop it sure is a limitation, as the serverside cursor is
forward only so I cannot view data in a grid.

regards,

Erik



Erik Visser
Vulcan.NET DAD



"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> schreef in
bericht news:OKhwzANEHHA.3668@xxxxxxxxxxxxxxxxxxxxxxx
Hi Erik,

Please email me your VO code and I'll see if I can determine what is
happening. I'm guessing that the string you're sending to Execute
would also fail using SQL Server Mangement Studio.

--
Ginny Caughey
Device Application Development MVP


"Erik Visser" <evisser@xxxxxxxxxxxxxxxxx> wrote in message
news:%23VFckDHEHHA.4256@xxxxxxxxxxxxxxxxxxxxxxx
João,

About the client. I have a written a query analyzer that does this.
First you select an Ado/Oledb provider using AdoDriverSelect() . Next
step it build the connection. It shows a textcontrol where you can
write a query to be executed.
The query is passed to an Adocommand with if any, a AdoRecordSet as
result. If the result is a recordset, a grid if used to display it.
This tool I use for several years now, and most of the times I use it
to work with Jet or MS SqlServer. but DB2 works fine too and so do
others, as long as there is a OleDB provider.

So with this tool as front end, i can tes the behaviour of all kind
of different databases using OLEDB.
The queries I showed in my previous post, work fine with (the OLEDB
providers of) Jet, MSDE and SqlServer 2005 .
They just do not work with Microsoft.SQLSERVER.MOBILE.OLEDB.3.0.

The errormessage is, translated:
At a operation which exists from several steps, errors have appeared.
check all status values
This error is thrown at Adocommand:Execute() and the debuggers tells
me no statusvalues to check...

The tool is written in Visual Objects. As I do not know you (VO
programmers 'all know eachother' <g>) , I assume you do not use this
language very often, but i think you can make up what is going on
here:

cText := AllTrim(SELF:oDCSQL:TextValue)
ocmd := AdoCommand{}
ocmd:ActiveConnection := oConn
ocmd:CommandText :=cText
oRS:= ocmd:Execute( @n, NIL,NIL ) //this row throws the error

Erik
( VO UGP The Netherlands)



"João Paulo Figueira" <joao.figueira@xxxxxxxxxxxxxxxxxxxxxxxxx>
schreef in bericht
news:3d207d03257ff8c8de2040f08e70@xxxxxxxxxxxxxxxxxxxxx
Erik,

As a matter of fact, the OLE DB provider does not impose the
limitations you report. I have just tested your SQL with one of my
clients (OLE DB, not .NET) and the faulty SELECT statement works.
The issue may actually lie on the client code and not on the
provider. When you implement an OLE DB client you can decide on lots
of parameters, especially on how you bind the column data through an
accessor. If this code is not correctly implemented you may see some
strange errors. I have been using OLE DB client code in Windows CE
platforms for a few years now, and I have seen some strange
situations, mostly when the code I wrote missed some subtle detail.
I have also seen some bugs in the provider and developed (and
published) workarounds for them:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=7234&atb.perform(details)=&
http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=6750&atb.perform(details)=&

Rephrasing my question: What client application are you using? This
may actually be the culprit.

Can you translate the error message (or provide the error numbers)?

Regards,
João Paulo Figueira
Device Application Development MVP

João ,

There are no such limitations.

I do not like this kind of comment . Perhaps you do not know about
it,
or perhaps there is a bug. But the limitation is there, at least in
my
situation.

How are you accessing these columns?

SELECT * FROM LAYOUT

So if LAYOUT has a column LOOKUP_QUERY nVarchar(127) (or
smaller )
I get
the result I want.
If I change the width of LOOKUP_QUERY to 128 or higher, OLEDB gives
me
an
error.
Try this ((make sure you have a OLEDB connection, not the .NET
Framework Data Provider for SQL Server Mobile Edition!!!!

ALTER TABLE YourTable ADD Test NVarchar(128)
GO
SELECT * FROM YourTable //Error
GO
SELECT NAME, ADDRESS FROM YourTable .// OK, works fine
GO
SELECT NAME, ADDRESS, TEST from YourTable // error
GO
ALTER TABLE YourTable ALTER COLUMN Test NVarChar(127)
GO
SELECT * FROM YourTable //OK, works fine
GO
SELECT NAME, ADDRESS FROM YourTable .// OK, works fine
GO
SELECT NAME, ADDRESS, TEST from YourTable // OK, works fine
GO
I have tried several widths for the column to find out at what
point
te problem starts. I also tried different tables / different table
structures, and the result is the same, a width of 128 or higher
results in an error. The errormessage is in Dutch, so that would
not
be very helpful i guess.

regards,

Erik

Regards,
João Paulo Figueira
Device Application Development MVP
Hi,
When I use the Microsoft.SQLSERVER.MOBILE.OLEDB3.0 provider to
connect
to a
SDF file, , i cannot open table with one or more nVarchar columns
designed
wider than 127 chars.
Is this a known / documented limitation?
tia,
Erik


















.

Relevant Pages

  • Re: nVarchar limitations using OLEDB provider
    ... but not nvarcharor greater using a clientside cursor. ... is IMHO what OLEDB is designed for: ... Unless of course this OLEDB provider is not supported at all..... ... The issue may actually lie on the client code and not on the ...
    (microsoft.public.sqlserver.ce)
  • Re: nVarchar limitations using OLEDB provider
    ... If you use a clientside cursor on the connection, you are not able to use a ... you select an Ado/Oledb provider using AdoDriverSelect. ... as long as there is a OleDB provider. ... lie on the client code and not on the provider. ...
    (microsoft.public.sqlserver.ce)
  • Re: nVarchar limitations using OLEDB provider
    ... On a device I won't be using the OLEDB provider. ... On the desktop it sure is a limitation, ... issue may actually lie on the client code and not on the provider. ...
    (microsoft.public.sqlserver.ce)
  • Re: MIDAS/Datasnap Alternative
    ... DDCAppservice is an I/O Completion Ports ... efficient way of developing server applications. ... Increasing the client base does not typically require ... component for each logical data provider. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: nVarchar limitations using OLEDB provider
    ... Device Application Development MVP ... First you select an Ado/Oledb provider using AdoDriverSelect. ... as long as there is a OleDB provider. ... issue may actually lie on the client code and not on the provider. ...
    (microsoft.public.sqlserver.ce)