[SOLVED] Fields being Truncated lazarus / 16-Databases ---------------------------------------------------------------------- Stag76 · 2012-10-11 11:35 I've been using Lazarus v0.9.30 and ZEOS-6.6.6-Stable to extract data in a migration project from FireBird 1.5 DataBases for a year without problem, but, after updating to Lazarus v1.0 and ZEOS 7.0.1-Beta, I find that Varchar fields are being truncated when they are read from the FireBird DataBase. The truncation varies from 1 to 3 or 4 right side characters as far as I can make out. Has anybody else seen this? ---------------------------------------------------------------------- ludob · 2012-10-11 11:41 Are there any UTF8 non ASCII characters in the VarChar fields? ---------------------------------------------------------------------- Stag76 · 2012-10-11 11:56 No, they're just text. An example is 'Street' is truncated to 'Stree', and 'AN' truncated to 'A'. ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 12:29 Can you give me a simple table and a little testcase for that? The 6.6.6 di determine the field-size oversized but it might be possible that i made a mistake for that case.  Is that field a VARYING char field? ZeosDevTem ---------------------------------------------------------------------- Stag76 · 2012-10-11 12:52 I have a table (548 rows) of FireBird 1.5 fields, defined as Varchar (eight) . (If I use the number 8 in brackets it translates to a face). These are all being truncated to 2 characters. eg 'ANDRDE' is truncated to 'AN'. I can extract the Data to a SpreadSheet if that helps. ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 13:07 Nope i don't need a script. Hmm this is looking like a version issue to me. I've testet these modifications with FB2.5 where the Server returns Varchar(40)*SizeOf(ChracterSetChar)+SizeOf(ChracterSetChar) which means i got 123 Bytes for UTF8 as result for all SQL_VARYING types. To determine the ColumnDisplaySize i go that way backwards. Can you test the latest \testing branch before reacting by coding? It might be possible we've bundled a wrong rev. to this package... Edit: What i don't know is: How does FB1.5 behave in this case? ---------------------------------------------------------------------- Stag76 · 2012-10-11 13:18 How do I get the latest testing branch to test it? ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 13:20 http://zeos.firmos.at/viewtopic.php?t=841 ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 13:40 Sorry was a deprecated urL. Have a look here: http://zeos.firmos.at/viewtopic.php?t=3431 ---------------------------------------------------------------------- Stag76 · 2012-10-11 13:48 Sorry...this is beyond my capabilities. ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 17:46 I'll download a FB1.5 and test it and give you a reply the next two days. But if that was a bug of me than is now way around to use the SVN instead of the Beta-package. Best regards, Michael ---------------------------------------------------------------------- Stag76 · 2012-10-11 19:38 Thanks very much. In the meantime, I'll try to work through the testing branch download. ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 19:54 Added a VirtualBox with Vista32, Installed Lazarus 1.0.2 and FPC 2.6 (download package of SF.NET) Installed FireBird1.5 and FlameRobin 0.9.2(to see a different behavior) Downloaded and installed the Zeos-7.0.1-Beta package also from SF.NET Created a DataBase with Charset Unicode_FSS and a simple table with some varchar fields in same CharSet as the DB uses with different sizes to verify the issue. Made a simple Grid-App and:::: No issues to see.  Now this makes me curious. Where are the differences?  Edit: Some more Details: Database created with flamerobin. Have 3 VARCHAR fields, size 7,8,9 AND i can use the full size. No values are trunked. Had a mistake on my previous post: Field.Size(90) := Varchar(30)*Bytes/Char(3->Unicode_FSS) Which Charset did you use before? Can you see diffenrence with my settings? ---------------------------------------------------------------------- Stag76 · 2012-10-11 20:59 I'll duplicate what you have done. My FireBird 1.5 Databases are ones that were migrated (5 years ago) from Interbase. The only difference I can see is that they have CharSet = NONE. ---------------------------------------------------------------------- Stag76 · 2012-10-11 21:20 I'm downloading Lazarus 1.0.2-fpc-win32 to re-install. Which Version of ZEOS is recommended? Will 6.6.6-Stable work with this version of Lazarus, or will I have to use a later one? ---------------------------------------------------------------------- EgonHugeist · 2012-10-11 21:36 Ok i'll verify the problem with charset 'NONE' tomorrow evening. (sorry no time right now). I have an idea: type 'NONE' into the TZConnection.ClientCodePage property or use an equal TZConnection.Properties.Values['Codepage'] := 'NONE'. THEN i determine 1Byte/Char instead of 3! I made a little patch for Lazarus: IF the Lazarus user does not choose a ClientCodePage(CharacterSet) then i use an UTF8 compatible one for Lazarus (To avoid looping FAQ/reports of wrong written chars). Nothing prefents a Lazarus user to type in the correct Charset manually. Charset 'NONE' can't be converted to 'UTF8' with firebird except you use the converter functions.. To your question: 6.6.x won't work with FPC2.6 UP. You need Zeos7. Allways a good adress is the \trunk of the repositories. Can you test my suggestion first? ---------------------------------------------------------------------- Stag76 · 2012-10-11 21:44 I'm also out of time for a few hours. I'm partway through the re-installation...have Lazarus installed, but still to do ZEOS. I'll test your suggestion and get back to you ASAP. Many Thanks. ---------------------------------------------------------------------- EgonHugeist · 2012-10-12 09:09 Any progress? I'll make a patch for the CharSet 'NONE' and do not allow to change this CharSet. FireBird is also not able to convert any Chars to this charset ---------------------------------------------------------------------- Stag76 · 2012-10-12 09:20 I have got my laptop into a state where no version of ZEOS will compile or install, so I will have to re-install Windows and load up all the Software again.  I guess it is a result of installing and removing different versions and combinations. Probably be a couple of days. Thanks for your help ---------------------------------------------------------------------- Zoran · 2012-10-12 10:01 Quote from: Stag76 on October 12, 2012, 11:20:55 amI have got my laptop into a state where no version of ZEOS will compile or install, so I will have to re-install Windows and load up all the Software again.  I guess it is a result of installing and removing different versions and combinations. Probably be a couple of days. Thanks for your help Don't you think that reinstalling operating system is a very extreme measure for not being able to compile Zeos components? Before you do such a thing, please try to make sure all older versions of zeoslib are removed from your disk, then open and compile Zeos packages one by one in this order: 1. zcore, 2. zplain, 3. zparsesql, 4. zdbc, 5. zcomponent. For each of these packages make sure that you opened the package from the correct folder. Then install the last package (zcomponent). Recompile Lazarus and see if everything is okay. I guess, Lazarus looks for some of the packages in some folder where previous version of zeos is and then try to combine these packages of different versions. This happened to me some time ago. If that is the case, what I suggested will help. If not, well... go on reinstalling Windows. ---------------------------------------------------------------------- Zoran · 2012-10-12 10:25 I don't have this old version of Firebird to test, but I know that Firebird did not have utf8 charset before version 2.0. To make it worse, utf8 was alias to some buggy UNICODE_FSS encoding. Please see the footnote in this page: http://www.firebirdsql.org/file/ documentation/reference_manuals/reference_material/html/langrefupd25- charsets.html#ftn.d0e56367 It says: QuoteIn Firebird 1.5, UTF8 is an alias for UNICODE_FSS. This character set has some inherent problems. In Firebird 2, UTF8 is a character set in its own right, without the drawbacks of UNICODE_FSS. I suppose that telling to FB 1.5 server that client encoding is utf8 might lead to some strange results... Anyway, Firebird team stopped the support for FB 1.5 years ago... You should really start making plans about upgrading. Take a look at this too: http://www.firebirdfaq.org/faq72/ And see also the last sentence in this page: http://www.firebirdfaq.org/faq81/QuoteAnd make sure you use at least Firebird 2.0, which has excellent UTF8 support. ---------------------------------------------------------------------- EgonHugeist · 2012-10-12 19:43 Ok i've testet it with charset 'NONE'. If Zeos know this CodePage then this issue is solved. (Which doesn't mean i won't think about a better solution.) FireBird has two problems (as far as i know): You can't change the Client-Charset if you're connected and they have there issues with char convertations. BUT Firebird is also my favourite of the free DBMRS. I propose you test my suggestion with charsert 'NONE' in the ClientCodePage property AND/or you make a backaup and update this deprecated server. This time use the UTF8 collation because Zoran is right the old UNICODE_FSS is a known trouble-maker and charset 'NONE' is not convertable. Best regards, Michael ---------------------------------------------------------------------- Stag76 · 2012-10-12 20:59 Hi Zoran, I compiled the packages as you said, and each one stopped at line 84 in unit zClasses, which says tContainedObject = Class(tAggregatedObject, IInterface), and gives the error No matching Implementation for Interface method "IUnknown QueryInterface(constref,tGuid,out):Longint,StdCall;"found This is ZEOSDBO-7.0.0-alpha. The project I'm working on is a data migration to move from FireBird 1.5 to SQL Server. ---------------------------------------------------------------------- Stag76 · 2012-10-12 23:11 I've downloaded and successfully installed ZEOSLIB-7.0.1-beta....looks like I was trying to install the wrong version I'll go back to testing the Character Set. ---------------------------------------------------------------------- Stag76 · 2012-10-12 23:40 I've changed the TZConnection ClientCodePage Property to 'NONE' and it is no longer truncating the data. All looks to be correct. Thank you both for your prompt and accurate responses