On Wed, Jun 17, 2020 at 02:24:20AM -0500, Jeff Gray wrote: > I may be misunderstanding, but is the example in the link declaring a sqlite > column of type datetime? I didn't think you could do that.
SQLite's typing is different from most other SQL engines. See https://sqlite3.org/datatype3.html. According to that document datetime is a valid column type. Indeed AFAICT any column type is allowed as long as it conforms to SQLite's naming requirements. When writing an instance of DateAndTime, Pharo-SQLite stores it as a string in the database. When reading, if the database indicates that the actual type of the value fetched is a string, Pharo-SQLite checks the column type declaration and turns the string back into a DateAndTime instance if the declared SQL column type is one of SQLite3Library dateTimeTypes. > I just created a person table with four text columns and wrote this in my > playground: > > |conn sql bindings| > conn := SQLite3Connection on: 'C:\Users\JeffGray\test.db'. > conn open. > sql := 'insert into person (active, created, first_name, last_name) values > ("Y", ?, "Jeff", "Gray")'. > bindings := OrderedCollection new. > bindings add: DateAndTime now. > conn execute: sql with: bindings. > conn close. I added table creation SQL with exactly one declared type of datetime to your example (slightly rewritten): | conn sql bindings | conn := SQLite3Connection openOn: '/tmp/dt.db'. [ conn execute: 'drop table person'. conn execute: 'create table person (active, created datetime, first_name, last_name)'. sql := 'insert into person (active, created, first_name, last_name) values ("Y", ?, "Jeff", "Gray")'. conn execute: sql value: DateAndTime now. ] ensure: [ conn close ]. Reading it back gives a DateAndTime instance. Previous mail I mentioned pluggablity. It turns out when one can declare any column type one has pluggability already. Below example declares the column 'created' as type 'unixtime' and writes the value 'DateAndTime now asUnixTime'. | conn sql bindings | conn := SQLite3Connection openOn: '/tmp/dt.db'. [ conn execute: 'drop table person'. conn execute: 'create table person (active, created unixtime, first_name, last_name)'. sql := 'insert into person (active, created, first_name, last_name) values ("Y", ?, "Jeff", "Gray")'. conn execute: sql value: DateAndTime now asUnixTime. ] ensure: [ conn close ]. Reading it backs gives an integer. The application programmer, knowing the column type declaration is unixtime, can then turn that into a DateAndTime instance. Pierce
