nVarchar limitations using OLEDB provider

[Erik Visser]

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

[João Paulo Figueira]

Hello Erik,

There are no such limitations. How are you accessing these columns?

Regards,
João Paulo Figueira
Device Application Development MVP

[Erik Visser]

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

[João Paulo Figueira]

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)?

[Erik Visser]

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.f...@primeworks-mobile.xyz.com> schreef in
bericht news:3d207d03257ff8...@news.microsoft.com...

[Ginny Caughey [MVP]]

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" <evi...@filterspamwilg.nl> wrote in message
news:%23VFckDH...@TK2MSFTNGP04.phx.gbl...

[Erik Visser]

Hi Ginny, (and ohers )

Thank you, check you mailbox.
To the others. I have found the reproducable cause of the problem: the
cursor.
If you use a clientside cursor on the connection, you are not able to use a
nVarchar(>127)
With a serverside cursor it works all fine.
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.

Erik Visser
Vulcan.NET DAD

"Ginny Caughey [MVP]" <ginny.caug...@wasteworks.com> schreef in
bericht news:OKhwzANE...@TK2MSFTNGP06.phx.gbl...

[Erik Visser]

> 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

[Ginny Caughey [MVP]]

Thanks, Erik. I'll play with this and see what else I can find out.

--
Ginny Caughey
Device Application Development MVP

"Erik Visser" <evi...@filterspamwilg.nl> wrote in message

news:e2lmaDUE...@TK2MSFTNGP04.phx.gbl...

[Erik Visser]

I idd some more testing on this.
The SQLSERVER.MOBILE.OLEDB provider supports both Clientside and Serverside
cursors.
Using a Clientside cursor, only the OpenStatic cursor type is available.
This cursor can handle nVarchar column <= 127
Using a ServerSide Cursor, only the OpenForwardOnly cursor type is
available.
This cursor can handle all (?) nVarchar columns

hth,

Erik

"Erik Visser" <evi...@filterspamwilg.nl> schreef in bericht
news:eEINLl%23DHH...@TK2MSFTNGP06.phx.gbl...

[mibainfo]

Hi,

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" <evi...@filterspamwilg.nl> píąe v diskusním příspěvku
news:ul7OCcUE...@TK2MSFTNGP04.phx.gbl...

>
>> 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.caug...@wasteworks.com> schreef in
>> bericht news:OKhwzANE...@TK2MSFTNGP06.phx.gbl...
>>> 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" <evi...@filterspamwilg.nl> wrote in message
>>> news:%23VFckDH...@TK2MSFTNGP04.phx.gbl...

>>>> Joăo,

>>>> "Joăo Paulo Figueira" <joao.f...@primeworks-mobile.xyz.com> schreef

>>>> in bericht news:3d207d03257ff8...@news.microsoft.com...
>>>>> 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 ,

>>>>>>> Joăo Paulo Figueira

[Erik Visser]

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

[Ginny Caughey [MVP]]

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" <evi...@filterspamwilg.nl> wrote in message

news:OxgALrhE...@TK2MSFTNGP03.phx.gbl...

[Erik Visser]

Hi Ginny,

> 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.

Thanks fior the confirmation!

> 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.

I think I can work around the issue, now I know I do not do anything wrong
(that is, on this point <g>)
But I would not mind (...) if you report the bug on a convient moment.

> 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.

This I do not understand. If this is an unsupported use, what would be the
supported usage? The way we use it, is IMHO what OLEDB is designed for:
Unless of course this OLEDB provider is not supported at all.....<g>

<snip http://msdn.microsoft.com/data/learning/MDAC/>
What is OLEDB?
OLEDB was designed to provide a consistent developer experience and
performant data access across a variety of data sources-some of which may
not be databases at all. OLEDB leverages the C++ Active Template Libraries
(ATL) to provide power to the developer while abstracting many complexities
of COM programming. For an introduction to OLEDB programming, see the OLE DB
Programmer's Reference.
<snip>

<snip Wikipedia>
OLEDB is an API designed by Microsoft for accessing different types of data
stores in a uniform manner. It is a set of interfaces implemented using the
Component Object Model (COM); it is otherwise unrelated to OLE. It was
designed as a higher-level replacement for, and successor to, ODBC,
extending its feature set to support a wider variety of non-relational
databases, such as object databases and spreadsheets that do not
necesssarily implement SQL
</snip>

Erik

[Ginny Caughey [MVP]]

Hi Erik,

I agree that the point about support doesn't quite make sense. I thought I
read that in a Microsoft blog post, but now I'm not able to find it, so
either my memory is faulty or that has changed. This seems to be the closest
blog post at the moment:
http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/08/691894.aspx It
does point out that the version number in the OleDb provider string for SQL
Compact Edition may change in the future.

I'll report your findings concerning nvarchar(>127) in any case.

Thanks,

--
Ginny Caughey
Device Application Development MVP

"Erik Visser" <evi...@filterspamwilg.nl> wrote in message

news:O3bWd9kE...@TK2MSFTNGP02.phx.gbl...