Invisible data in MySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
DavJans
Posts: 250
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Invisible data in MySQL

Post by DavJans » Wed Mar 23, 2016 11:36 pm

I've been trying to figure this one out for a while now but thought maybe Id be able to find the answer here.

When I query data from another program we use I get a lot of invisible character's in front of the actual data.

with livecode I can copy one character worth of this invisible data and then use a replace " <invisible> " with ""

Curiously I can not paste the copied character outside of livecode, can't even search google for it.

but I cant use things like select * from table where column = '1'

I have to use Like %1% but this also gives me 11 and 12 and 111 and so on.

Has anyone else had any experience with something like this?

BTW Select HEX(column) gives me this --> 01010101010101010101010101010101010101010132313530 for example, that's only 1 record.
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm"

AxWald
Posts: 390
Joined: Thu Mar 06, 2014 2:57 pm

Re: Invisible data in MySQL

Post by AxWald » Thu Mar 24, 2016 12:46 pm

Hi,

CharToNum(MyInvisibleChar) or
ByteToNum(MyInvisibleChar) maybe?

Is the invisible data always the same? Maybe some filler to get fixed-lenth fields? Then you could use:

Code: Select all

char offset(NumToChar(MyInvisibleCharAsNum))+1 to len(MyData) of MyData
to get the start of the real data received, and fill again with a repeat:

Code: Select all

repeat for (MyDesiredFieldLength - len(MyData))
     put NumToChar(MyInvisibleCharAsNum) before MyData
end repeat
for outgoing data?

Have fun!

Edit: This is untested pseudo code and will eat your machine if used without consideration!
Livecode programming until the cat hits the fan ...

DavJans
Posts: 250
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: Invisible data in MySQL

Post by DavJans » Fri Mar 25, 2016 4:30 pm

Thank you.

I was able to get the answer from the company that created the database.

"Those are padding characters that are inserted into the main mark, piece mark, drawing #, sequence, and lot # fields in order to achieve the desired sorting behavior depending on the input type settings. For a query like that we need to join to the ProductionControlJobs table to get the settings and then use the _FS_MARK_STR function (a user-defined function we install in the database) to get the plain-text value."
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm"

AxWald
Posts: 390
Joined: Thu Mar 06, 2014 2:57 pm

Re: Invisible data in MySQL

Post by AxWald » Sat Mar 26, 2016 1:04 pm

Hi,
DavJans wrote:"Those are padding characters that are inserted [...] in order to achieve the desired sorting behavior depending on the input type settings. For a query like that we need to join to the ProductionControlJobs table to get the settings and then use the _FS_MARK_STR function (a user-defined function we install in the database) to get the plain-text value."
Spooky! I smell primeval evil, unspeakable atrocities from times long forgotten - the foul odor of dBase! Where's the garlic, the crucifix and the silver bullets?

Well, sometimes we have to swallow such toads. Life ain't easy.
It's up to you now to decide if to use their way, or to analyze the data to find out if you can handle it yourself.
I'd rather use the second way if possible, working with the raw data & avoiding any hidden traps that may lurk in the code of such coders - provided you only need to READ the data.

If you need to WRITE, it's always good custom not to do it without consent from the other party, and to do it in way they approve. (This can save you a lot of trouble!)

Have fun,
and should there be more questions, feel free to ask!
Livecode programming until the cat hits the fan ...

Post Reply

Return to “Databases”