Thanks Peter Coding outside of SQLite is easy - it's doing it with just SQLite/SQL that I was after :(
Cheers 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 15 January 2015 at 00:27, Peter Aronson <pbaron...@att.net> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
