[Partialy Resolved] SQLite and NOW()?

Archives Yii 1.0.x Discussions
1

I am using a free hosting service that support Yii partially. Though it does not support PDO extention of MySQL, I changed to use SQlite from MySQL when the application works fine for MySQL. Then, I have to change somewhat the definition of the table as follows.

Quote

CREATE TABLE `Offkai` (

  :

-  modified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

+  modified timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL

);

sqlite3 executed with no error, but I found there is an error saying 'CDbCommand はSQL文を準備できません。:SQLSTATE[HY000]: General error: 1 no such function: NOW'.

What is the good way to prepare auto timestamp function in SQLite, or is it a bug?

2

Never mind. I misunderstood that the time stamp is stored by the functionarity of MySQL, because it is defined as below.

Quote

-  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Actually, it is stored by the behavior. I modified the code,

Quote

- $this->Owner->{$this->created} = new CDbExp​ression('NOW()');                                                 
  • $this->Owner->{$this->created} = new CDbExp​ression('datetime('NOW')');

Though it is an incompatibility between SQlite and MySQL, it works fine now.

3

Not fully resolved yet…  :cry:

I have investigated the difference between MySQL and SQLite on insertion and modification by using each log.

Yii log for MySQL:

Quote

○INSERT INTO Sankasya (offkaicode, sankasya, password, honbun) VALUES (:_p0, :_p1, :_p2, :_p3)

○UPDATE Sankasya SET id=:_p0, offkaicode=:_p1, sankasya=:_p2, password=:_p3, honbun=:_p4, modified=NOW() WHERE Sankasya.id=163

It works fine.

Yii log for SQLite:

Quote

×INSERT INTO ‘Sankasya’ (“modified”, “offkaicode”, “sankasya”, “honbun”, “password”) VALUES (:_p0, :_p1, :_p2, :_p3, :_p4)

○UPDATE 'Sankasya' SET "id"=:_p0, "offkaicode"=:_p1, "sankasya"=:_p2, "password"=:_p3, "honbun"=:_p4, "modified"=datetime('NOW','localtime') WHERE 'Sankasya'."id"=175

It does not work at insertion, the column "modified" being 0.

It is very strange that Yii generates the SQL code using the column "modified" because there is no description in the rule of the model definition as below.

4

What's the difference between MySQL and SQlite?

5

I am not an expert on database but I have been using mysql and sqlite for quite a long time. One of my host has a very crappy mysql server so I have to use sqlite.

Sqlite does not have that many built-in functions like mysql. For example, I like the INET_ATON function in mysql but sqlite does not support this.

For your question, it works fine on my isp host and localhost with PHP5.2.9 and sqlite3 with "CDbExp​ression('NOW()')".  What is your php and sqlite version?

6

Hi moho,

I should have post the same article here as below.

http://www.yiiframew…4.html#msg14014

Since my code is stolen just from the cookbook (http://www.yiiframework.com/doc/cookbook/14/) and it should not apply to my application.

My application works fine after this modification, still is there a difference between MySQL and SQLite in terms of the "NOW" function.

MySQL:   new CDbExp​ression('NOW()');

SQLite:   new CDbExp​ression('datetime('NOW', 'localtime')');

OK, you don't see the difference. My envirionment is as below.

PHP 5.2.6

SQLite: 3.4.2

I just can check with the command line.

Quote

$ sqlite3

SQLite version 3.4.2

Enter ".help" for instructions

sqlite> select NOW();

SQL error: no such function: NOW

sqlite> select datetime('NOW');

2009-06-08 05:55:38

sqlite> select datetime('NOW', 'localtime');

2009-06-08 14:55:46

7

I would not use NOW() in my code because that depends on the RDBMS you are running. Just use



date("Y-m-s H:i:s", time());

do get the current date. If you have a "current_timestamp" as default or "on_update" value you do not need to assign it in your code.

8

Thanks Dave,

'date("Y-m-d H:i:s", time());' works fine for both.