Обсуждение: Re: Can't get more than 255 chars out of memo field in Acc

> -----Original Message-----
> From: Benjamin Scherrey [mailto:scherrey@proteus-tech.com]
> Sent: 27 November 2002 20:24
> To: Tom Lane; Jeff Eckermann
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Can't get more than 255 chars out of memo
> field in Acc
>
> I can't seem to figure
> out what settings are necessary to get Access to recognize
> this as a memo field.
>
> create table "Table-MCMProgram 2000" (
> "ID" serial unique not null ,
> "Date Opened" date null ,
> "Name" varchar ( 50 ) not null ,
> "Account#" integer not null ,
> "Date of Birth" varchar ( 50 ) null ,
> "Attending MD" varchar ( 50 ) null ,
> "Health  Plan Category" varchar ( 50 ) not null ,
> "County" varchar ( 50 ) null ,
> "Case Status" varchar ( 50 ) not null ,
> "UR Notes" text null )
>
>     Access treats all the varchar and text fields as text
> in my ODBC link. I just need "UR
> Notes" to act like a good old memo field once again so I can
> get more than 255 chars in it.

I just tried linking this table with:

psqlODBC 07.02.0004
PostgreSQL 7.3rc1/Cygwin
Access XP

And it works perfectly using a DSN with all default settings:

ID                Number
Date Opened            Date/Time
Name                Text
Account#            Number
Date of Birth        Text
Attending MD        Text
Health Plan Category    Text
County            Text
Case Status            Text
UR Notes            Memo

Regards, Dave.

Ah Ha! I created a new DSN with PostgreSQL+(Beta) and it correctly interpreted the UR Notes as
a memo field. Went back with another new DSN using PostgreSQL+Unicode(Beta) and it would
not take it as a memo field. Presently I don't think the Unicode is critical for my app but I think its
something that should be corrected if possible.

Thanx VERY much for your help,

    Ben Scherrey

11/27/2002 5:10:21 PM, "Dave Page" <dpage@vale-housing.co.uk> wrote:
>I just tried linking this table with:
>
>psqlODBC 07.02.0004
>PostgreSQL 7.3rc1/Cygwin
>Access XP
>
>And it works perfectly using a DSN with all default settings:
>
>ID                Number
>Date Opened            Date/Time
>Name                Text
>Account#            Number
>Date of Birth        Text
>Attending MD        Text
>Health Plan Category    Text
>County            Text
>Case Status            Text
>UR Notes            Memo
>
>Regards, Dave.

I take it back- I'm completely screwed:

    I dunno why but the non-Unicode drivers keeps displaying #Name as the content of all
my fields. Only the Unicode driver shows my content but only the Unicode driver seems unable to
recognize the memo field correctly. This really throws my project into big problems. I don't
understand what the #Name stuff is all about. Any ideas?

    Ben Scherrey


11/27/2002 5:37:30 PM, Benjamin Scherrey <scherrey@proteus-tech.com> wrote:

>Ah Ha! I created a new DSN with PostgreSQL+(Beta) and it correctly interpreted the UR Notes as
>a memo field. Went back with another new DSN using PostgreSQL+Unicode(Beta) and it would
>not take it as a memo field. Presently I don't think the Unicode is critical for my app but I think its
>something that should be corrected if possible.
>
>Thanx VERY much for your help,
>
>    Ben Scherrey
>
>11/27/2002 5:10:21 PM, "Dave Page" <dpage@vale-housing.co.uk> wrote:
>>I just tried linking this table with:
>>
>>psqlODBC 07.02.0004
>>PostgreSQL 7.3rc1/Cygwin
>>Access XP
>>
>>And it works perfectly using a DSN with all default settings:
>>
>>ID                Number
>>Date Opened            Date/Time
>>Name                Text
>>Account#            Number
>>Date of Birth        Text
>>Attending MD        Text
>>Health Plan Category    Text
>>County            Text
>>Case Status            Text
>>UR Notes            Memo
>>
>>Regards, Dave.

Benjamin Scherrey wrote:
>
> Ah Ha! I created a new DSN with PostgreSQL+(Beta) and it
> correctly interpreted the UR Notes as
> a memo field. Went back with another new DSN using > PostgreSQL+Unicode(Beta) and it would
> not take it as a memo field.

Did you check the *Text As LongVarChar* DSN option ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

11/27/2002 7:07:39 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>Benjamin Scherrey wrote:
>>
>> Ah Ha! I created a new DSN with PostgreSQL+(Beta) and it
>> correctly interpreted the UR Notes as
>> a memo field. Went back with another new DSN using > PostgreSQL+Unicode(Beta) and it
would
>> not take it as a memo field.
>
>Did you check the *Text As LongVarChar* DSN option ?
>
>regards,
>Hiroshi Inoue
>    http://w2422.nsk.ne.jp/~inoue/

Yep - all configurations are identical except for the driver.

    Ben Scherrey

Benjamin Scherrey wrote:
>
> 11/27/2002 7:07:39 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
> >Benjamin Scherrey wrote:
> >>
> >> Ah Ha! I created a new DSN with PostgreSQL+(Beta) and it
> >> correctly interpreted the UR Notes as
> >> a memo field. Went back with another new DSN using > PostgreSQL+Unicode(Beta) and it
> would
> >> not take it as a memo field.
> >
> >Did you check the *Text As LongVarChar* DSN option ?
> >
> >regards,
> >Hiroshi Inoue
> >       http://w2422.nsk.ne.jp/~inoue/
>
> Yep - all configurations are identical except for the driver.

Hmm strange.
Here, if I turn on the *Text As LongVarChar* DSN option
I see a memo field and if I turn off the option I see
a text field.
Do you relink the table after you changed DSN options ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

11/27/2002 8:48:29 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>Hmm strange.
>Here, if I turn on the *Text As LongVarChar* DSN option
>I see a memo field and if I turn off the option I see
>a text field.
>Do you relink the table after you changed DSN options ?
>
>regards,
>Hiroshi Inoue

    Yeah - I redo everything from scratch each time to ensure that its a fresh connection. Are
you using the Unicode driver when this happens for you? FWIW - we've still got Access 97 here
but it recognizes memo fields correctly with the other two drivers. What version of Access are you
running?

    thanx & later,

        Ben Scherrey

Benjamin Scherrey wrote:
>
> 11/27/2002 8:48:29 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
> >Hmm strange.
> >Here, if I turn on the *Text As LongVarChar* DSN option
> >I see a memo field and if I turn off the option I see
> >a text field.
> >Do you relink the table after you changed DSN options ?
> >
> >regards,
> >Hiroshi Inoue
>
>         Yeah - I redo everything from scratch each time to ensure
>     that its a fresh connection. Are
> you using the Unicode driver when this happens for you?

Yes.

> FWIW - we've still got Access 97 here
> but it recognizes memo fields correctly with the other two drivers.
> What version of Access are you running?

I'm using Access 2000 here.
OK could you send me the Mylog debug output ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

> -----Original Message-----
> From: Hiroshi Inoue
>
> Benjamin Scherrey wrote:
> >
> > 11/27/2002 8:48:29 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
> > >Hmm strange.
> > >Here, if I turn on the *Text As LongVarChar* DSN option
> > >I see a memo field and if I turn off the option I see
> > >a text field.
> > >Do you relink the table after you changed DSN options ?
> > >
> >         Yeah - I redo everything from scratch each time to ensure
> >     that its a fresh connection. Are
> > you using the Unicode driver when this happens for you?
>
> Yes.
>
> > FWIW - we've still got Access 97 here
> > but it recognizes memo fields correctly with the other two drivers.
> > What version of Access are you running?
>
> I'm using Access 2000 here.
> OK could you send me the Mylog debug output ?

I got Mylog output from Benjamin and examined it. Certainly he
turns on *Text As LongVarChar* option and the log seems to be
almost the same as the one I get here using Access 2000.
Maybe the difference comes from the difference of Access version.
Unfortunately Access 97 doesn't seem to understand UNICODE.

regards,
Hiroshi Inoue

:-( Thanx for looking into this Hiroshi. Its odd that the only Unicode
problem that Access 97 would have is not being able to tell that a
text field is a memo field - because everything else seems to work
fine. Regardless - is there anyway we can figure out why, when
displaying the table data, the non-Unicode driver ends up with a
bunch of 'Name?' strings rather than the data? If either of these
problems was corrected I'd be able to deliver this project. As it is
- upgrading them to a new version of Access is the last thing they
(or I) want to do.

    best regards,

        Ben Scherrey


On Sunday 01 December 2002 08:06 am, Hiroshi Inoue wrote:
> I got Mylog output from Benjamin and examined it. Certainly he
> turns on *Text As LongVarChar* option and the log seems to be
> almost the same as the one I get here using Access 2000.
> Maybe the difference comes from the difference of Access version.
> Unfortunately Access 97 doesn't seem to understand UNICODE.
>
> regards,
> Hiroshi Inoue

Benjamin Scherrey wrote:
>
> :-( Thanx for looking into this Hiroshi. Its odd that the only Unicode
> problem that Access 97 would have is not being able to tell that a
> text field is a memo field - because everything else seems to work
> fine. Regardless - is there anyway we can figure out why, when
> displaying the table data, the non-Unicode driver ends up with a
> bunch of 'Name?' strings rather than the data?

There seems to be spaces in your table or column names.
Would anything change with removing those spaces ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Hiroshi -

    Thanx very much for looking into this. Attached is the mylog file and the sql code the
create the table. Its a big nasty table to be sure and will only display data when viewed with the
Unicode driver. Also - I tried the 0005 driver but when it came to listing the tables for selection all I
got was a bunch of 'p'. Looks like the took the 'p' from public_TABLENAME so I cannot link
anything with the Unicode driver under 0005.

    The problem we're trying to fix is getting my text fields to be recognized as memo fields
under Access 97. The non-Unicode drivers do this but they can't display the data (I think because
they choke on the odd field names perhaps?). Let me know if there's anything else you want me to
try.

    My settings are as follows:

Postgres 7.3b5 under Linux compile options '--with-perl' '--with-python'

PostgresSQL + Unicode (Beta) 7.02.00.04
    KSQO, Recognize Unique Indexes, Use Declare/Fetch,Parse Statements, MyLog, LF<->
CR/LF, Updatable Cursors, Row Versioning, Disallow Premature : checked

    Unknown sizes = Maximum
    Text as LongVarChar
    Bools as Char
    Max Varchar: 254
    Max LongVarChar: 8190
    Cache Size: 100
    SysTable Prefixes: dd_;
    Int8 As: default
    Protocol: 7.X,6.4+
    OID Options: Show Column & Fake Index checked


    many thanx,

        Ben Scherrey

11/27/2002 9:15:59 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:

>Benjamin Scherrey wrote:
>>
>> 11/27/2002 8:48:29 PM, Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>> >Hmm strange.
>> >Here, if I turn on the *Text As LongVarChar* DSN option
>> >I see a memo field and if I turn off the option I see
>> >a text field.
>> >Do you relink the table after you changed DSN options ?
>> >
>> >regards,
>> >Hiroshi Inoue
>>
>>         Yeah - I redo everything from scratch each time to ensure
>>     that its a fresh connection. Are
>> you using the Unicode driver when this happens for you?
>
>Yes.
>
>> FWIW - we've still got Access 97 here
>> but it recognizes memo fields correctly with the other two drivers.
>> What version of Access are you running?
>
>I'm using Access 2000 here.
>OK could you send me the Mylog debug output ?
>
>regards,
>Hiroshi Inoue
>    http://w2422.nsk.ne.jp/~inoue/
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>