Text (uni)coding issue

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Text (uni)coding issue

Mike Kerner via use-livecode
I have a database on a LiveCode server that I use to track user  
interactions in a LiveCode app. It has gotten too big to query in  
LiveCode without timeout errors (the JSON gets mangled because there  
are now several thousand records), so I'm trying to generate a text  
file on the server and download that instead.

I can get the text file to generate on the server, but when I try to  
download it to my LiveCode app (to eventually save on the end-users  
computer) the text is in some weird format. I assumed this was some  
wierd unicode issue and tried to textDecode it, but can't seem to  
figure out what format it actually is so that just gives me text in a  
different weird format ("UTF-8", "UTF-16", and "MacRoman" don't seem  
to work).

My code on the LiveCode Server seems straight forward:
   put "SELECT * FROM promosRedeemed" into tSql
   put revDataFromQuery(TAB, "|", sDbId, tSql) into tRedemptions
   set linedelimiter to "|"
   set itemdelimiter to tab
   replace RETURN with " -=- " in tRedemptions // ACCOUNTS FOR LINE  
BREAKS IN DATA
   replace "|" with RETURN in tRedemptions
   put "redemption-log_" & the seconds & ".txt" into tFileName
   open file specialFolderPath("home") &  
"/public_html/server/apps/iot/logs/" & tFileName for write
   write tRedemptions to file specialFolderPath("home") &  
"/public_html/server/apps/iot/logs/" & tFileName
   close file specialFolderPath("home") &  
"/public_html/server/apps/iot/logs/" & tFileName


The first line of the text file when viewed in a web browser or text  
file downloaded directly (outside of LC) is plain text and looks as  
expected:
294 Wednesday, July 13, 2016 8:36 15 3 MacOS 10.11.5 -=-  
MacBookPro11,3 -=- 0,0,1920,1200 -=- 39.892219,-85.974632

But when I fetch that URL in LiveCode using...
  put url (tURLtoDownload) into tFullRedemptionList
  put tFullRedemptionList into url ("binfile:Full-IOT_TSM_RedemptionList.xls")

...the first line (and all other lines basically) looks like this:
ã‰Ω◊rÎ:∂(˙Ï˝<un›ó≥<7rp≠ªÎ:Áú˝‚bîsíø˛$@BH[≤<Â∂ª´´zyA¿»L∞ˇF¸ø! ÿD¡÷˙ïx‚¸Óψ&˝G˛¡‡∏dXíÇIAˇц†Í?ˇ’Ø°¸o  
õÄrNÄâ¸ÔhMıôı0fi‹˝Ç?˛!≈ø¬ Áˇê+è7Å˛e≈ïSPˇ®ˇ‘˝êx~(ßPø5?Ñ–ˇKÍ˝%eSô_ˇŸƒnÿQ?Œ—bSHòˇπ˜áN·w6)¸?TiÛ•ˇá≤¢NÕ!–g(Ù
Ä˙ ‘
®[ ùïàÎïuá»YäÅ^ZCbà›ïX؃5+IπR*ä”Î≤f%uW


I know I've done similar tasks before (pretty sure this is code I  
borrowed from other projects I've done) but can't figure out why this  
text is so garbled. Tried in 9.0.0dp7 and 8.1.6 with the same result.  
Any ideas what I'm doing wrong?

--Andrew Bell



_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Text (uni)coding issue

Mike Kerner via use-livecode
Any reason why you're using binfile instead of file if the text isn't
encoded?


On Sat, Aug 26, 2017 at 8:34 AM, Andrew Bell via use-livecode <
[hidden email]> wrote:

> I have a database on a LiveCode server that I use to track user
> interactions in a LiveCode app. It has gotten too big to query in LiveCode
> without timeout errors (the JSON gets mangled because there are now several
> thousand records), so I'm trying to generate a text file on the server and
> download that instead.
>
> I can get the text file to generate on the server, but when I try to
> download it to my LiveCode app (to eventually save on the end-users
> computer) the text is in some weird format. I assumed this was some wierd
> unicode issue and tried to textDecode it, but can't seem to figure out what
> format it actually is so that just gives me text in a different weird
> format ("UTF-8", "UTF-16", and "MacRoman" don't seem to work).
>
> My code on the LiveCode Server seems straight forward:
>   put "SELECT * FROM promosRedeemed" into tSql
>   put revDataFromQuery(TAB, "|", sDbId, tSql) into tRedemptions
>   set linedelimiter to "|"
>   set itemdelimiter to tab
>   replace RETURN with " -=- " in tRedemptions // ACCOUNTS FOR LINE BREAKS
> IN DATA
>   replace "|" with RETURN in tRedemptions
>   put "redemption-log_" & the seconds & ".txt" into tFileName
>   open file specialFolderPath("home") & "/public_html/server/apps/iot/logs/"
> & tFileName for write
>   write tRedemptions to file specialFolderPath("home") &
> "/public_html/server/apps/iot/logs/" & tFileName
>   close file specialFolderPath("home") & "/public_html/server/apps/iot/logs/"
> & tFileName
>
>
> The first line of the text file when viewed in a web browser or text file
> downloaded directly (outside of LC) is plain text and looks as expected:
> 294     Wednesday, July 13, 2016 8:36   15      3       MacOS 10.11.5 -=-
> MacBookPro11,3 -=- 0,0,1920,1200 -=-  39.892219,-85.974632
>
> But when I fetch that URL in LiveCode using...
>  put url (tURLtoDownload) into tFullRedemptionList
>  put tFullRedemptionList into url ("binfile:Full-IOT_TSM_Redempt
> ionList.xls")
>
> ...the first line (and all other lines basically) looks like this:
> ã        ‰Ω◊rÎ:∂(˙Ï˝ <un›ó≥<7rp≠ªÎ:Áú˝‚bîsí ø˛ $@B H[≤<Â∂ª´´zyA   ¿»
> L∞ˇF¸ø! ÿ D¡÷˙ ï x‚¸Óψ&˝G˛¡ ‡   ∏dXí ÇIAˇ ц† Í?ˇ ’Ø°¸o  õÄr NÄâ¸Ôh M
> ıôı0fi‹˝ Ç? ˛!≈ø¬   Áˇ ê +è7Å˛e ≈ ïSP ˇ ®ˇ‘˝êx~(ß Pø5?Ñ–ˇKÍ˝%eS ô_ ˇ ŸƒnÿQ?
> Œ — bSHò  ˇ π˜á N·w6)¸?T iÛ •ˇá≤¢NÕ !–g(Ù
> Ä˙ ‘
> ®[      ùïàÎïu á»YäÅ^ZCbà›ïX؃5+IπR*ä ”Î ≤f%uW
>
>
> I know I've done similar tasks before (pretty sure this is code I borrowed
> from other projects I've done) but can't figure out why this text is so
> garbled. Tried in 9.0.0dp7 and 8.1.6 with the same result. Any ideas what
> I'm doing wrong?
>
> --Andrew Bell
>
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode




--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Text (uni)coding issue

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
Howdy Andrew

There’s a few things to deal with here.

- you need to know your database encoding (let’s assume UTF-8)

- you need to get values out of your database in a way that gives you the binary data rather than strings that the engine has assumed  were in native encoding. Use:

local tCursor
put "SELECT * FROM promosRedeemed" into tSql
put revQueryDatabase(sDbId, tSql) into tCursor
local tResults
repeat until revQueryIsAtEnd(tCursor)
      repeat for each item tColumn in revDatabaseColumnNames(tCursor)
           local tValue
           get revDatabaseColumnNamed(tCursor, tColumn, “tValue”)
           put textDecode(tValue,”UTF-8”) into tValue
           replace return with “ -=- “ in tValue  // ACCOUNTS FOR LINE BREAKS IN DATA
           put tValue  & tab after tResults
      end repeat
      put return into the last char of tResults
      revMoveToNextRecord tCursor
end repeat
revCloseCursor tCursor

- you need to write this file in an encoding you know so you can decode it when you download:

put "redemption-log_" & the seconds & ".txt" into tFileName
put textEncode(tResults, “UTF-8”) into url (“binfile:” & specialFolderPath("home") & "/public_html/server/apps/iot/logs/" & tFileName)

- you need to read the file and decode from the encoding you know it is in:

put textDecode(url (“binfile:” & <local path>), “UTF-8”) into tResults

Disclaimer: this code has just been written into an email client untested so may have some errors.

Cheers

Monte
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Text (uni)coding issue

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
> - you need to know your database encoding (let?s assume UTF-8)
>

I was still fairly green to mySQL when I first created this database,  
so I never changed the default. Looks like the collation is  
"utf8mb4_unicode_ci". Should that be changed to something else? The  
server charset is UTF-8 Unicode (utf8) [HostM]


> - you need to write this file in an encoding you know so you can  
> decode it when you download:
>
> - you need to read the file and decode from the encoding you know it is in:
>
> put textDecode(url (?binfile:? & <local path>), ?UTF-8?) into tResults
>
> Disclaimer: this code has just been written into an email client  
> untested so may have some errors.

There were some errors (I think from email translations), but I think  
I understand what you're trying to do. The data still seems to be in a  
weird format.

--Andrew Bell


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Text (uni)coding issue

Mike Kerner via use-livecode

> On 27 Aug 2017, at 11:44 am, Andrew Bell via use-livecode <[hidden email]> wrote:
>
> There were some errors (I think from email translations), but I think I understand what you're trying to do. The data still seems to be in a weird format.

Hmm… did you encode the data correctly as you put it in the db?

Cheers

Monte
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Text (uni)coding issue

Mike Kerner via use-livecode
In reply to this post by Mike Kerner via use-livecode
>
> Hmm? did you encode the data correctly as you put it in the db?
>

The data was transmitted from device to server using HostM's MySQL  
HTTPS API Solution 1.1.3 @  
https://www.hostm.com/tutorials/livecode/api-mariadb-mysql (amazing  
tool and web host).

It looks like they are sending as "utf-8" (though for a short time in  
a few versions it was implementing "base64" according to their change  
log). Is there any sort of universal format stripper? Like a "convert  
to plain text" option.

--Andrew Bell


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode