Comments (13)
I'm going to need more of a working example of the failure -- here's what I tried:
use DB::SQLite;
my $s = DB::SQLite.new(filename => 'this.db');
$s.execute('create table person_tbl ( last text )');
my $last = "O'Connor";
my $sth = $s.db.prepare('insert into person_tbl (last) values (?)');
$sth.execute($last);
say $s.query('select * from person_tbl').hashes;
It prints out this:
({last => O'Connor})
from raku-dbsqlite.
Curt that looks good and I probably made another mistake. I'll try again.
I really like this module--much easier to use than others I've tried.
I won't be able to reply for a while.
UPDATE
It was my error in an insert statement early in my program development. Your module works great! Sorry for the bad call.
from raku-dbsqlite.
Strange, but the error I get is:
DEBUG: key = <|oconnor|michael|[email protected]|>
NOTE: found person key '|oconnor|michael|[email protected]|' already entered.
DEBUG: key = <|o'connor|patrick|[email protected]|>
near "connor": syntax error
in method prepare-nocache at /usr/local/rakudo.d/share/perl6/site/sources/E0C8745F742CD981123D6FBD6B9BE68BA67C0A70 (DB::SQLite::Connection) line 17
in method prepare at /usr/local/rakudo.d/share/perl6/site/sources/31DAC65B428E3BE7CAFC630257CA1C4190EE6AC3 (DB::Connection) line 45
in method query at /usr/local/rakudo.d/share/perl6/site/sources/31DAC65B428E3BE7CAFC630257CA1C4190EE6AC3 (DB::Connection) line 50
in method query at /usr/local/rakudo.d/share/perl6/site/sources/A79D993708E8048217C956D0437DC98D02AF9B83 (DB) line 23
in block <unit> at ./load-db-sql.p6 line 151
I'm trying to golf it down a bit, using two names.
When I dump the person table I get:
sqlite> .dump person
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE person (
key VARCHAR(255) PRIMARY KEY UNIQUE NOT NULL,
last VARCHAR(255) NOT NULL,
first VARCHAR(255) NOT NULL,
notes VARCHAR(255)
);
INSERT INTO person VALUES('|oconnor|michael|[email protected]|','OConnor','Michael',NULL);
COMMIT;
When I dump the email table I get:
sqlite> .dump email
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE email (
email VARCHAR(255) NOT NULL,
notes VARCHAR(255),
status INTEGER,
person_id VARCHAR(255) NOT NULL,
CONSTRAINT key PRIMARY KEY (email, person_id)
);
INSERT INTO email VALUES('[email protected]',NULL,NULL,'|oconnor|michael|[email protected]|');
COMMIT;
If I remove the apostrophe from the keys all works well. and the apostrophe in the last name shows properly and is taken care of as expected.
from raku-dbsqlite.
Print the exact arguments to both prepare()
and execute
just before you call them.
from raku-dbsqlite.
Okay, it will be a while, though. Thanks for looking at this.
from raku-dbsqlite.
I would immediately suspect a mismatch in the placeholders and arguments.
DBIish
uses its own placeholders and parses the statements itself checking all of that. This takes time, but allows it to provide better error messages. It also prevents it from using all the fancy placeholder options that sqlite offers.
DB::SQLite
just passes things through to sqlite exactly as you specify them without parsing or validation. This is the fastest and most efficient, and allows you the complete power of sqlite, at the cost of obscure error messages if you get it wrong.
We made different tradeoffs.
from raku-dbsqlite.
I have carefully checked the code for the prepare and execute statements:
my $sthP = $db.prepare('insert into person (key, last, first) values (?, ?, ?)');
Execution:
$sthP.execute($key, $last, $first);
where, from a debugging "note" just before the failed execute, I get:
$key = <|o'connor|patrick|[email protected]|>
$last = <O'Connor>
$first = <Patrick>
Note if I remove the apostrophe from the offending key, the program runs fine and the tables have the proper entries including the apostrophe in Patrick O'Connor's last name.
from raku-dbsqlite.
I've golfed all down to a single, short script and it shows no problem--looks like MY problem, but I'll keep this open until I isolate the bug.
from raku-dbsqlite.
Getting closer to the problem, but I still don't know exactly what triggered the failure. I had a non-prepared select query in a loop between prepared insert queries to determine the existence of a person before attempting an insert. The apparent fix was to properly determine person row existence before any attempted insert.
I'm still debugging though so maybe I can suggest a doc change to help db novices.
from raku-dbsqlite.
That's ok, but use a different handle for the different queries. Either use $s.query()
to get a new handle each time, or call $s.db
for each purpose.
from raku-dbsqlite.
Curt, I am getting a "handle" on the problem, but could you give an example of the canonical way to check for the presence of a unique key in a table with DB::SQLlite? Reason I ask is when I do that with a prepared query and execute it I get a good response (either Nil or 1), but when I try a dynamic query at the same place with $s.db or $s it gives that weird error shown above.
from raku-dbsqlite.
I dunno -- maybe:
$s.query('select 1 from foo where key = ?', $value).value
from raku-dbsqlite.
From my experiments I see I have been using formats for non-prepared queries that are double-quoted strings with embedded interpreted args which resulted in my problems.
Using the in-place prepared form (as your example above) with the ? place holders and adding the args separately in the same Perl 6 statement works reliably as DB::SQLite handles apostrophes in strings properly behind the scenes.
Bottom line: Let DB::SQLite be your friend and use it correctly!
from raku-dbsqlite.
Related Issues (16)
- Cannot locate native library 'libsqlite3.so': libsqlite3.so: cannot open shared object file: No such file or directory on Ubuntu 18.10 HOT 2
- DB::SQLite doesn't like IntStr placeholders HOT 2
- doc error in Result.pm6 HOT 1
- provide a way to properly hot back up a db [FEATURE REQUEST]
- sqlite3 column names "oid" and "_rowid_" are not returned in a results hash HOT 2
- add query method .values HOT 2
- Use of Nil in numeric context in DB::SQLite::Statement line 44 HOT 14
- Out of memory when invoking several times .hashes HOT 6
- How to open a db in readonly mode? HOT 4
- The module fails to install due to hard-coded NativeLibs version HOT 1
- Rakudist test fails
- Native lib name sqlite3 vs sqlite HOT 6
- In memory database support broken
- Frequent "Database is locked" errors HOT 4
- can't use a named bind parameters with execute
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from raku-dbsqlite.