I'm just testing out my http://navaid.com/ applications with SQLite. In
the past, my scripts have worked with MySQL and PostgreSQL with minimal
changes, but I'm getting two problems with SQLite in perl DBD::SQLite:
- insert the value "0E9" (and other variables that would look like numbers
if they were in numeric fields) into a varchar(10) field, and it gets
converted to the numeric equivalent. The identical syntax worked fine
with MySQL and PostgresSQL. The schema looks like:
sqlite> .schema waypoint
CREATE TABLE waypoint (
id varchar(10) default NULL,
datasource_key varchar(20) default NULL,
type varchar(30) default NULL,
name varchar(100) default NULL,
address varchar(120) default NULL,
state char(3) default NULL,
country char(2) default NULL,
latitude double default NULL,
longitude double default NULL,
declination double default NULL,
datasource smallint(6) default NULL,
elevation double default NULL,
main_frequency varchar(9) default NULL,
ispublic tinyint(1) default NULL,
chart_map smallint(6) default NULL,
tpa smallint(6) default NULL
);
CREATE INDEX waypoint_datasource on waypoint(datasource);
CREATE INDEX waypoint_id on waypoint(id);
CREATE INDEX waypoint_type on waypoint(type);
sqlite>
and is inserted with
$wptInsertStmt = $conn->prepare(qq{
INSERT INTO waypoint
(id, datasource_key, type, name, address,
state, country,
latitude, longitude, declination,
elevation, main_frequency, datasource,
ispublic, chart_map)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?)});
$wptInsertStmt->execute($id, $datasource_key, $type,
$name, $address, $state, $country,
$latitude, $longitude, $declination, $elevation,
$main_frequency, $datasource, $isPublic, $chart_map)
or die $wptInsertStmt->errstr;
print "Inserted $type: $id\n";
- The second problem appears to be a problem with self-locking. I'm
inserting a "mapping" into a table. I have a query active to find ids
that require mapping, and then I try to find the current lowest
unused "mapping" value, and insert it. But when I do, the open query
seems to have locked the database against itself, and I get an error:
DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c
line 401 at /config_backup/navaid.com//perl/DBLoad_lite.pm line 547.
The code in question:
my $selectStmt = $conn->prepare(
"SELECT distinct(a.id) " .
"FROM waypoint a " .
"LEFT JOIN id_mapping b " .
"ON a.id = b.id " .
"WHERE b.id is null");
my $insertStmt = $conn->prepare(
"INSERT " .
"INTO id_mapping(id, pdb_id) " .
"VALUES (?,?)");
$selectStmt->execute() or die $selectStmt->errstr;
while (my @row = $selectStmt->fetchrow_array)
{
my ($id) = @row;
print "new ID: $id\n";
$maxNumber = nextId($maxNumber);
print "inserting $id, $maxNumber\n";
$insertStmt->execute($id, $maxNumber++); #<-- this is line 547
}
nextID does the following:
while (1)
{
$isTaken->execute($maxNumber);
if (@row = $isTaken->fetchrow_array)
{
print "$maxNumber is already taken\n";
$maxNumber++;
}
else
{
last;
}
}
return $maxNumber;
Any ideas?
--
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
If the automobile had followed the same development as the computer a
Rolls Royce would today cost $100, get a million miles per gallon and
explode once a year killing everybody inside. - Robert Cringley (InfoWorld)
