Board index » delphi » Best way to store and retrieve Memo fields

Best way to store and retrieve Memo fields

Hi all,

I have a database (access 2000) which has a memo field in it.
If I wish to store say the text from a memo component into that field, I
(usually) use the assign method.
eg. ...paramByName('memField').assign(mem_action.lines)
or.... fieldByName('memField').assign(aStringList) (to read the info in
from the record)...

Is this the best way to store the information into the field??  I
haven't had a problem with it in the past (I usually use Interbase
databases), but I get a consistent error these days with Access 2000 of

...EDBEngineError with message 'General SQL error.
[Microsoft][ODBC Microsoft Access Driver]String data, right truncated
(null)'...

I'm using an ODBC connection as you can tell, so is this where my
problem lies??
I don't normally use this type of connection...
I can supply the ODBC config stuff if required.

TIA

Jason

 

Re:Best way to store and retrieve Memo fields


Quote
>...EDBEngineError with message 'General SQL error.
>[Microsoft][ODBC Microsoft Access Driver]String data, right truncated
>(null)'...

>I'm using an ODBC connection as you can tell, so is this where my
>problem lies??

There is a compatibility problem with BDE and MS newer ODBC driver in
calculating field length.  My guess is this is the problem you are running into
This is the Tech alert Borland has issued.

Borland has issued the following tech alert that covers this problem
*************************************************
Delphi Tech Alert
Published: 7/28/99
*************************************************

You have been sent this message because you have
subscribed to the Developer Support Delphi
mailing list. This periodic newsletter provides
information about Borland Delphi.

Table of Contents
=================================================
I. Current Tech Alerts
II. How to Subscribe to this Newsletter
III. How to Stop Receiving this Newsletter
-------------------------------------------------

I. Current Tech Alerts
=================================================
Product: Delphi/BDE
Version: 4.0/5.0
Description: Problems with Newest MS Access driver
Symptoms: "Record Key Deleted" or "Size Mismatch" error.

Steps to Reproduce:
Installing the newest version of the MS Access
ODBC driver, version 4.00.3711.08, can cause these
errors. This new version comes with Windows 98
release 2, Access 2000, the Microsoft Data Access
Components (MDAC) and perhaps other sources.
The error is happening because Microsoft has made a
change in the driver regarding the length of fields,
and now the BDE is expecting the field length to be
twice as long as it actually is. For example, a
field length may be 30 in the Access database, but
the BDE reports it to be 60.

Microsoft documentation specifies that the length,
in bytes, of data transferred (the buffer size) on
an SQLGetData, SQLFetch, or SQLFetchScroll operation
shall be the same as the COLUMN_SIZE for binary
and character data. This is not in fact the case
with the new driver; the buffer is actually larger
than COLUMN_SIZE.

The BDE does not look at COLUMN_SIZE because for
numerical data it may not be the same as the
buffer size. Instead the BDE looks at the buffer
size which now has the erroneous value.

Workarounds:
1. Use the Delphi native driver instead of ODBC.
2. Use an older version of the Access ODBC driver.
3. In Delphi 5: Use ADO components to access the
   MS JET database engine or to access ODBC drivers.
   Accessing the ODBC driver this way is not
   optimal, but does bypass the BDE which will
   avoid the problem.
------------------------------------------------
  Home > C++ > Database > Database Engines > Article

 Question and Answer Database

     FAQ: FAQ4779B - Character fields from Access ODBC connection are reported
as being double their true size
Category: Database (ODBC)
Platform: All-32Bit
 Product: All-CBuilder, C++Builder1.0, C++Builder3.x, C++Builder4.x,
Delphi2.x, Delphi3.x, Delphi4.x, VdBase7.x,

Question:

How come when I use Microsoft's latest ODBC driver for Access the BDE
returns the field size for character fields as being double their actual size?

Answer:

This problem occurs because the BDE calls SQLCOLUMNS and the looks at
BUFFER_LENGTH
to determine the size of a column. With this driver BUFFER_LENGTH is returned as

double what the COLUMN_SIZE is.

Here is what Microsoft used to document what BUFFER_LENGTH should return:

The length in bytes of data transferred on an SQLGetData, SQLFetch, or
SQLFetchScroll operation if SQL_C_DEFAULT is specified. For numeric data, this
size may be different than the size of the data stored on the data source.
This value is the same as the COLUMN_SIZE column for character or binary data.

Here is what Microsoft nows documents what BUFFER_LENGTH should return:
The length in bytes of data transferred on an SQLGetData, SQLFetch, or
SQLFetchScroll operation if SQL_C_DEFAULT is specified. For numeric data, this
size may be different than the size of the data stored on the data source.
This value might be different than COLUMN_SIZE column for character data.

Inprise has verified this problem to exist in versions 4.00.3711.08 and
4.00.4202.00 of the Access ODBC driver. Inprise has contacted Microsoft
and they have confirmed this new change in the ODBC specification. Inprise
is currently evaluting how to resolve this issue in a future release that
does not effect other ODBC drivers adversely.

Errors that may be causes by problem are "Record/Key Deleted" and "Size
Mismatch".

The workarounds to this problem are:
1. Use an older version of the ODBC driver.
2. Use the BDE Access Native driver.
3. Use ADO with Delphi 5.

7/28/99 10:46:47 AM

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads