trying to decode data (exporting Abs to MySQL)

Question

I have database table:

DROP TABLE translation_en_lt;
CREATE TABLE translation_en_lt (
  id INTEGER,
  lt_translation WIDEMEMO BLOBBlockSize 102400 BLOBCompressionAlgorithm ZLIB BLOBCompressionMode 9
);

Which was generated using AbsManager (Absolute database manager, Delphi). It seem to be outdated however, and syntax is invalid for current MySQL. Then I have this data entry:

INSERT INTO translation_en_lt (lt_translation) values (MimeToBin('cABhAHMAaQBzAGw='));

The value for this row, as displayed correctly in the AbsManager, is pasislinkti. However, base64_decode('cABhAHMAaQBzAGw=') returns p�a�s�i�s�l [base64_encode('pasislinkti') returns cGFzaXNsaW5rdGk=].

How do I get 'pasislinkti' from MimeToBin('cABhAHMAaQBzAGw=')? Does it have anything to do with the encoding, zlib (WIDEMEMO BLOBBlockSize 102400 BLOBCompressionAlgorithm ZLIB BLOBCompressionMode 9)?

I got comment saying:

14:24 VoVo64: guy: looks as if the base64 encoded data is actually UCS-2 little endian. decoding cABhAHMAaQBzAGw= to HEX results in 70 00 61 00 73 00 69 00 73 00 6c

But I have already tried iconv('UTF-16', 'UTF-8', base64_decode('cABhAHMAaQBzAGw=')) which resulted in:

Notice: iconv(): Detected an incomplete multibyte character in input string in /var/www/ssl/pp.php on line 2 pasis

I have also tried: mb_convert_encoding($u, 'UCS-2LE', 'UTF-8'), mb_convert_encoding($u, 'UCS-2', 'UTF-8'); neither didn't work.

Answer 1

These are Base64 strings:

cABhAHMAaQBzAGw= -> pasisl
cGFzaXNsaW5rdGk= -> pasislinkti

I think your data is being truncated somewhere. You should look for a MimeToText() function in your database system. I'd try to dump the data out to CSV and load that in MySQL using LOAD DATA INFILE. You will probably have to rewrite the table schemas by hand. The BLOBBlockSize.... appears to be for compression of this data. Your new table will take a little more space or you can look into using MySQL's Compress() function.

Something like this should work:

CREATE TABLE translation_en_lt (
  id INTEGER, 
  lt_translation MEDIUMTEXT, 
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8