Re: [Chicken-users] Big integers as statement parameters in sql-de-lite

[Kon Lovett]

On Jul 22, 2013, at 3:26 PM, Matt Gushee m...@gushee.net wrote:

 Hi, chickenists--
 
 I am working on an application that stores data in a SQLite3 database,
 and am currently using the sql-de-lite egg to interface with the DB. I
 have a few fields that represent dates, and I have defined their
 datatype as INTEGER.
 
 However, when I attempt to execute a statement such as:
 
  INSERT INTO articles (node_id, title, created_dt) VALUES (?, ?, ?);
 
 with one of these large integers bound to the third parameter, I get
 an error because apparently the value is too large. The values in
 question are obtained in the following manner:
 
  (time-seconds (date-time SRFI-19-DATE-OBJECT))
 
 ... so a typical result is a number like 1291678156, which is a bignum.
 
 Is there a way I can use these numbers as numbers in sql-de-lite, or
 do I have to convert them to something else?

(SQLite3 does have datetime functions for use in queries but assumes a string 
or UNIX timestamp.)

Do you need the range a SRFI-19 datetime provides? Maybe an epoch based 
approach, like provided by the posix module.

 
 --
 Matt Gushee
 
 ___
 Chicken-users mailing list
 Chicken-users@nongnu.org
 https://lists.nongnu.org/mailman/listinfo/chicken-users


___
Chicken-users mailing list
Chicken-users@nongnu.org
https://lists.nongnu.org/mailman/listinfo/chicken-users

Re: [Chicken-users] Big integers as statement parameters in sql-de-lite

[Jim Ursetto]

It's not that the values are too large, it's that srfi 19 represents them as 
bignums, which is not currently supported by sql-de-lite. However, they work as 
floats (they will be converted to integers before being passed to the database, 
due to the column type).  Even on 32-bit systems, we fully support handling 53 
bit integers in the database in this way.

The easiest way to do this is by calling exact-inexact on the result to force 
it to floating point. Such a small value need not be a flonum on 64-bit system, 
but it will still work correctly, and it's the easiest way to ensure it is not 
a bignum.  You can confirm the value was stored as an integer by using the 
typeof() SQL function, e.g. select typeof(date) from foo;

Or you can just store the UNIX timestamp obtained from the posix unit, which 
should be a lot easier ;)

Jim

On Jul 22, 2013, at 18:18, Matt Gushee m...@gushee.net wrote:

 Hi, Kon--
 
 On Mon, Jul 22, 2013 at 4:34 PM, Kon Lovett konlov...@gmail.com wrote:
 
 Do you need the range a SRFI-19 datetime provides? Maybe an epoch based 
 approach, like provided by the posix module.
 
 No, I don't need the range. In fact, my project is a lightweight
 blogging platform, and the date-times in question are creation and
 modification times for the content, which will normally be displayed
 to site visitors--possibly in localized formats; and other values
 having to do with authentication and sessions, which will normally be
 invisible. So I think it is safe to assume that all values that will
 ever be used will fall within a couple of decades beginning with 2013.
 
 I'm not sure if I need to use SRFI-19. For some reason I thought that
 it would be preferable from the POV of  localized formatting and time
 zone conversions, but looking again at the docs it doesn't seem like
 that is necessary.
 
 But I notice that the posix date/time functions work with values
 representing seconds, much like the values that are not working for
 me--except that in posix they are floats instead of integers. So maybe
 they won't cause errors? I'll give that a try.
 
 --
 Matt Gushee
 
 ___
 Chicken-users mailing list
 Chicken-users@nongnu.org
 https://lists.nongnu.org/mailman/listinfo/chicken-users

___
Chicken-users mailing list
Chicken-users@nongnu.org
https://lists.nongnu.org/mailman/listinfo/chicken-users