If you're accessing SQLite from your own program, adding a hextoint function
would be pretty easy. Even if you were using sqlite3, defining your own
extension isn't that hard (and is documented on the SQLite website to some
extent). That would allow you to do most of the things you've been asking
about. Here's a rough cut at one (untested, and somewhat platform dependent):
/***********************************************************************
*
* S_hextoint_function -- Convert a hex number string starting with 0x to int.
*
***********************************************************************/
static void S_hextoint_function (sqlite3_context *ctx,
int num_values,
sqlite3_value **values)
{
const char *input_string;
char *error_string;
sqlite3_int64 value;
long long llvalue;
int converted;
size_t string_length;
/* The integer value of a NULL is NULL. */
if (sqlite3_value_type (values[0]) == SQLITE_NULL) {
sqlite3_result_null (ctx);
return;
}
/* Get the hex string and make sure it starts with 0x. */
input_string = (const char *)sqlite3_value_text (values[0]);
if ((const SE_WCHAR *)NULL == input_string) {
sqlite3_result_error_nomem (ctx);
return;
}
string_length = strlen (input_string);
if (strlen < 3 || '0' != input_string[0] ||
('x' != input_string[1] && 'X' != input_string[1])) {
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
}
/* Convert the hex string. */
converted = sscanf (input_string,"%llx",&llvalue);
if (1 != converted)
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
}
value = (sqlite3_int64)llvalue;
/* Return the integer value. */
sqlite3_result_int64 (ctx,value);
}
And, if in your own program, you'd just execute
sqlite3_create_function (hdbc,
"hextoint",
-1,
SQLITE_ANY,
globalfunc_info,
S_hextoint_function,
NULL,
NULL);
After connecting, but before executing your SQL.
Peter
On Wednesday, January 14, 2015 4:32 PM, Paul Sanderson
<sandersonforens...@gmail.com> wrote:
>
>
>Thanks all
>
>I am running the latest version :)
>
>I am just getting back to this and have a related problem
>
>I have a table
>
>create table (base int, hex text)
>
>and I want to create a trigger such that if hex is updated (or a new
>row inserted) with a text string in the form 0x12345abcd this value is
>converted into an integer and copied to base.
>
>I have tried various methods such as
>
>CREATE TRIGGER hex_trig after insert on dates
>when (select hex from dates where hex is not null)
>begin
>update dates set base = cast(new.hex as int);
>end
>
>but so far have drawn a blank
>
>Can this be done?
>
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>-Forensic Toolkit for SQLite
>http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>processing made easy
>
>
>
>On 8 January 2015 at 00:33, Richard Hipp <d...@sqlite.org> wrote:
>> On 1/7/15, Paul Sanderson <sandersonforens...@gmail.com> wrote:
>>> Evening all
>>>
>>> I dont think this can be done, but would love to be corrected. I have
>>> a column with integer dates stored in hex format so 1234567890 is
>>> stored as 49962d2
>>>
>>> Obviously
>>> DateTime(1234567890, 'unixepoch')
>>>
>>> work OK, but what I would like to do is something like
>>>
>>> DateTime(0x49962d2, 'unixepoch')
>>
>> This should work fine, provided you are using SQLite 3.8.6 or later.
>> Support for hexadecimal literals was added in version 3.8.6. Release
>> date for 3.8.6 was 2014-08-15, so this is not the version running on
>> your phone. :-\
>>
>>
>>>
>>> or
>>>
>>> DateTime(HexToInt(0x49962d2), 'unixepoch')
>>>
>>> Is this possible? Not a problem if not, but would be nice.
>>>
>>> Thanks
>>>
>>>
>>> Paul
>>> www.sandersonforensics.com
>>> skype: r3scue193
>>> twitter: @sandersonforens
>>> Tel +44 (0)1326 572786
>>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>>> -Forensic Toolkit for SQLite
>>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>> processing made easy
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>
>> _______________________________________________
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
