Posts filed under ‘Database’

DTS is ignoring my failure workflow

Recently, I  created a simple DTS that sends a email when a task failed. The email is an ActiveX program that reads the email config from an INI file and is linked to the other task via a failure workflow link. So when the task fails, the email task is activated. I then attempt to simulate a failure by RAISERROR. Much to my surprise, the entire task halted instead and the error task is never called.

In my case, the problem turns out to be a configuration issue. If you right-click on the DTS package and get “Package Properties”. Click on the Logging tab, uncheck the “Fail package on the first error”. If this option is checked, the entire DTS stops before the error task can run.

October 1, 2007 at 2:39 pm 1 comment

Notes while working on SQLite with Ruby on Rails

I have been going through the Ruby on Dave Thomas’s Agile Web Development with Rails on Puppy. Because MySQL is rather large, I chose to instead use sqlite3 for the database instead. Here are the changes I made.

Pointing the Application to the Right Database

In a rails project, the config/database.yml file controls which database the application access and the database adapter it uses. There should be a section for development, test, and production.

By default, you’ll see the following section:

adapter: mysql
username: root
host: localhost

We need to change 2 lines. The first being the adapter, which we chnage from mysql to sqlite3. The second line we change is the location of the database, which we specify using a relative path from the project root. The username, password, and host field is unused. Sqlite database is a file, not a server. You should make sure that the user you will use to access sqlite3 have read/write access to the database file.

adapter: sqlite3
database: db/depot_development.db

When you create the database, make sure you use the exact name (don’t for example create depot_development when your file references depot_development.db).

Creating the table

Just like MySQL, you write a script to create the database tables. Usually, we create a database for each stage of the project, so when you have the project depot, we will create depot_development, depot_test, and depot_production.

In MySQL, you would log into as mysql admin and create 3 databases and grant the developer permission to the database. Because sqlite3 database are basically flat files, you can just create the database file and make sure that the developer has read/write access to the file.

The product table is created by the following script:

id int not null auto_increment,
title varchar(100) not null,
description text not null,
image_url varchar(200) not null,
price decimal(10,2) not null,
primary key (id)

If you try to run this in sqlite3, it will fail. Unfortunately, sqlite3 isn’t all that great with error messages, since it didn’t generated any. You have to rewrite the script to:

id integer primary key
title varchar(100) not null,
description text not null,
image_url varchar(200) not null,
price decimal(10,2) not null

In sqlite3, you can declare a field of int, but the primary key field only works on the type of integer, so you have to use the data type of integer instead of int. A field that is integer with primary key is automatically incremented. There is no need to say “not null” because primary key fields cannot be null.

Of course, I did not know this and got complaints that the id filed is null.

Confusing Error Message

Suppose you using the web application you created and add an item, but get the following error:

SQLite3::SQLException: SQL logic error or missing database: INSERT INTO products ("image_url", "date_available", "price", "title", "description") VALUES('', '2007-05-25 06:10:00', NULL, '', '')

You could get this message if you can’t access the database or you have violated a database rule. In the above example, I had attempted to insert a null value into a field
that is not null.

Basically, the error messages are not very descriptive.

There is no Now() in Sqlite3

In one of the examples, the following query is executed:

select * from products where date_available >= now()

This query will bomb in sqlite3 because there is no now() function. You have to change the syntax to:

select * from products where date_available >= date('now')

Keep in mind that date(‘now’) returns UTC. If you want to return the local time, use date(‘now’, ‘localtime’)

May 29, 2007 at 8:47 am 2 comments


April 2020

Posts by Month

Posts by Category