Re: nVarchar limitations using OLEDB provider

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.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
    ... 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)
  • Re: nVarchar limitations using OLEDB provider
    ... not nvarcharor greater using a clientside cursor. ... SqlCeResultSet instead of the OleDb provider. ... The issue may actually lie on the client code and not on the ...
    (microsoft.public.sqlserver.ce)
  • Re: nVarchar limitations using OLEDB provider
    ... Yet another limitation. ... With other OLEDB providers my Delphi programs works fine. ... First you select an Ado/Oledb provider using AdoDriverSelect. ... issue may actually lie on the client code and not on the provider. ...
    (microsoft.public.sqlserver.ce)
  • Re: nVarchar limitations using OLEDB provider
    ... you select an Ado/Oledb provider using AdoDriverSelect. ... as long as there is a OleDB provider. ... I have just tested your SQL with one of my clients (OLE DB, ... lie on the client code and not on the provider. ...
    (microsoft.public.sqlserver.ce)