RE: not Boolean - TEXT and WideString

Hi Danny

The error throws in design time and run time.

It is a Delphi error and it thinks the field is a blob of Memo type.

I’m changing TEXT to VARCHAR and upgrading existing installations on first run of new software version.

It’s taken me 3 days to realise this. I was hoping for a quick switch like the Boolean one.

Thanks for all your help.

Kind regards

Ian

Yeah I didn’t give it much of a chance of working but sometimes it helps to exclude things.  That would have been too easy!  I also assumed you probably had no control over the MSSQL db schema.  I know nothing about Delphi, but do you think the Delphi IDE could be embedding something in the code it produces that causes the incompatibility?  I  was not real clear on when the error msg is produced, but perhaps you could write a quick C or plain Pascal test pgm to do a simple PG open DB operation and if it opens ok, using the MSSQL table do a single read operation to prove the incompatibility is in the DBs data types, not some other introduced factor.  Other than that, it sounds like you will have to do some ETL on the estim table if this is a conversion job.  If NewUser needs to access the DB estim in live production, then obviously ETL isn’t an option.

BTW, where is the “expecting: Memo actual: WideString” in the error message coming from, the Delphi runtime’s interpretation of the ODBC state and error codes?  To my knowledge neither DB has data types that use that terminology.  MS Access has a MEMO but no TEXT data type, but MSSQL has no MEMO data type.  So, it makes me think that perhaps whatever is producing the message does not actually understand the TEXT data type or at lease it does not comprehend the differences in definitions.  In other words, if it is expecting MEMO, the incompatibility is between its definition of MEMO and TEXT – or what it refers to as WideString, not between the way the two DBs define TEXT.  Is that possible or just my lack of knowledge, which certainly is possible.  If it is the runtime, then it is as if the runtime thinks MSSQL is an MS Access DB.

Hi Danny

Thank you for responding so quickly.

Yes I have tried Varchar in PG but when I do that, my Delphi software fails for MSSQL users as the Field is expecting Memo not WideString.

There must be a difference in the Field types because if I delete and redefine the Table.Field in Delphi, the type is given automatically by the Delphi IDE.

I could change both PG and MSSQL to VARCHAR but that means restructuring users with large established databases using Text.

Thanks

Ian

Have you tried changing your column on the PG table to VARCHAR without a length qualifier to see if that works?  I think TEXT in each of the two DBs have different practical max size limits which might account for the incompatibility error.  Theoretically, there should be no difference in the data types TEXT between the two DBs, nor between TEXT and VARCHAR without a length qualifier.  Perhaps there is a difference in the table metadata for the columns in each table for the different lengths or other attributes, as noted in your screen shot, and that triggers the incompatibility.  It might be worth a shot if you haven’t tried it.

Just following up on my Text WideString Memo problem.

Text as LongVarChar does not seem to work.

From: Roger De Four [mailto:radfinancialsystems@gmail.com]
Sent: 27 July 2020 19:00
To: ian@ces-ltd.co.uk
Cc: pgsql-odbc@postgresql.org
Subject: Re: Boolean