Re: [sqlite] query help with inline conversion of hex data
Well yes I agree the stored format isn't ideal, but unfortunately I have no control over that. These are hard drive serial numbers and that is the format that Microsoft WMI spits them out in. It's possible that the Spiceworks application could be modified to decode those hex strings it gets back from WMI before it saves it in the database, but again, that isn't in my control either. :( It's disappointing there isn't a simple "unhex()" function to do the reverse of the already existing function. That would be a very elegant solution to this issue, as I am so close. I will investigate the dynamic extension thing tomorrow. I don't have any experience with that, so I'm not sure if it would work but it's worth looking into. In the worst case scenario I could add PHP into the mix and open the SQLite databases directly to do a query + host processing. I just hate to break out of the confines of the Spiceworks application and it's built in reporting capabilities because I was hoping to share my finished report back with their community as it would be very useful to a lot more folks. Thanks, Jim On Wed, Apr 18, 2012 at 5:47 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 18/04/12 14:39, Jim Sanders wrote: > > I found that, but I need essentially the reverse of that. The data > > stored in SQL is 3061626364 and I need to convert it back to 0abcd > > (from your example) > > The correct fix is to get them to stop storing strings that way :-) > Strings can and should be stored as strings. > > You would need to register a user defined function with SQLite that takes > the hex encoding and returns a string. There is no other solution I can > think of as you ruled out a 'host' language. > > It is possible to make dynamically loaded extensions that can provide the > function although it will also require SQLite to have been called to > enable them which is unlikely. > > If your final output can be html then you could output the field as is, > and have some Javascript that finds them and converts to text. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk+PNowACgkQmOOfHg372QSCwgCgp9jYnBJ09zhrNNg6DYy9pBbI > kuEAoKl0VFB5eghuYe6+14Xx6dO0cigE > =4WcZ > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query help with inline conversion of hex data
I found that, but I need essentially the reverse of that. The data stored
in SQL is 3061626364 and I need to convert it back to 0abcd (from your
example)
On Wed, Apr 18, 2012 at 5:35 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 18/04/12 14:30, Jim Sanders wrote:
> > But I can't figure out a way do this dynamically for all rows.
>
> There is a builtin hex() function.
>
> sqlite> create table foo(bar); insert into foo values('0abcd');
> sqlite> select hex(bar) from foo;
> 3061626364
>
> This applies when databases are in the most likely utf8 encoding. In
> utf16 encoding each character is two bytes(*). 'pragma encoding' will
> tell you which is in use.
>
> (*) Unicode is more complicated than that.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk+PM6AACgkQmOOfHg372QRv0wCghYRb3wBoTwKyMj6NTfuzFNw6
> +RYAn3gj8vo0PEFJph1wnMH0bPZwkKDr
> =mtot
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] query help with inline conversion of hex data
I am trying to write a query to a SQLite database (Spiceworks) in which I
need to transform some data as part of the SELECT statement.
The query I *want* to use looks like this (includes some pseudo code):
SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,
d.service_pack_major_version,
pd.model as HDModel,
--- check for hex string with leading zero (30) characters
CASE WHEN pd.serial like "303030%" THEN
LTRIM(X'pd.serial', '0')
-- check for hex string with leading space (20) characters
WHEN pd.serial like "202020%" THEN
LTRIM(X'pd.serial')
-- check for any other serial with leading zero characters
WHEN pd.serial like "00%" THEN
LTRIM(pd.serial, '0')
-- fall back to just presenting whatever is in the column
ELSE
pd.serial
END as HDSerial,
cast(pd.firmware as TEXT) as 'HD Firmware',
cast((pd.size / 10) as NUMERIC) as SizeGB,
pd.failure_prediction as 'SMART Enabled',
pd.status as 'SMART Status'
FROM devices d
INNER JOIN physical_disks pd ON (d.id = pd.computer_id)
WHERE SizeGB > 20 AND d.name like "0039%"
ORDER BY d.model, d.name ASC;
Everything in that query works perfectly for me except the X'pd.serial'
lines. I want to put the value of the pd.serial field into the X'...'
format and be returned the decoded hex data. I can manually do it one by
one and get the data I want:
sqlite> select X'3030303030303030313131343330423137454342';
111430B17ECB
But I can't figure out a way do this dynamically for all rows. I'm hoping
there is just a bit of syntax I am missing or another function or method I
should be using. I see lots and lots of documentation about using that
X'...' format, but always in the examples they are static one-off values,
and never as part of a larger SELECT query.
For what it's worth, I did discover that I can concatenate a string to
create the X'...' format I want, but there doesn't seem to be anyway to
actually evaluate that into the converted data. (instead I just get back
the whole X'3030303030303030313131343330423137454342' as a text string.
The concatenation method I used is:
('X''' || pd.serial || '''')
Unfortunately, I don't have the ability to just grab the raw data out of
SQL and then post-process it with PHP or another language for this specific
task (which would be much easier for me). I'm trying to keep this as a
built-in report for Spiceworks, and that limits me to only what a SQL query
can spit out.
Any guidance offered is appreciated!
Thanks,
Jim Sanders
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
