<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">We have lift off!<br>
<br>
I grafted the birdseed library into SQLite3.pir, played musical chairs
with the DBDI package, and connected to SQLite3 through Rakudo Perl 6.
Did run into some HLLCompiler errors when using thing .next() iterator,
though. That's why there is only a single $rs.next() call.
"while($rs.next())" triggered some statement parsing errors. Got research that...<br>
<br>Seems like DBDI eq JDBC though. :)<br><br>Here's some transcript:<br><br>
<span style="font-family: courier,monaco,monospace,sans-serif;">C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>sqlite3 test.db</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">SQLite version 3.5.4</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Enter ".help" for instructions</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">sqlite> .sc</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">CREATE TABLE foo(bar,baz);</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">sqlite> select * from foo;</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">1|123</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">2|Thingy</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">sqlite> .q</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>type mytest.p6</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">use DBDI;</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'Grabbing connection...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">my $conn = DBDI::DriverManager.getConnection("dbdi:SQLite3:test.db", "", "");</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'Creating a statement...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">my $stm = $conn.createStatement();</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'Getting a resultset...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">my $rs = $stm.executeQuery("select baz, bar from foo");</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'Fast forward one...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">$rs.next();</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'Get a value...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">my $data = $rs.getCol('baz');</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">print 'And the value is...';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say $data;</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">say 'w00t!';</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>perl6 mytest.p6</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Grabbing connection...</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">get_db_handle: got here</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Creating a statement...</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Getting a resultset...</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">get_statement_handle: got here</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Fast forward one...</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">Get a value...</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">And the value is...123</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<span style="font-family: courier,monaco,monospace,sans-serif;">w00t!</span><br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<br style="font-family: courier,monaco,monospace,sans-serif;">
<br>
T<br><br>-- Todd Presta<br>
-- http://www.asciiville.com<br><br>--- On <b>Tue, 7/21/09, Todd Presta <i><toddpresta@sbcglobal.net></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Todd Presta <toddpresta@sbcglobal.net><br>Subject: [Thousand-oaks-pm] P6 Hackathon - Word from the Parrot SQLite3 Trenches...<br>To: "perlmongers" <thousand-oaks-pm@mail.pm.org><br>Date: Tuesday, July 21, 2009, 11:16 AM<br><br><div id="yiv1700618239"><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font-family: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; font-size: inherit; line-height: inherit; font-size-adjust: inherit; font-stretch: inherit; -x-system-font: none;" valign="top">Ok. I might be able to sleep tonight.<br><br>Even with Parrot 1.4 out, still getting build errors when trying to use ncigen for the SQLite3 extension.<br><br>On another front, the NCI
functionality within Parrot seems to choke whenever requesting SQLite database connection handle and prepared statement handle no matter what type of PMC is passed as a parameter during the SQLite dynamic library function calls from within Parrot PIR. Using either Pointer, CPointer, UnManagedStruct, ManagedStruct, or ResizablePMCArray either segfaults or returns an erroneous value.<br><br>I decided to go deep and write some C code, basically wrapping both the sqlite3_open and sqlite3_prepare functions in the SQLite3 dynamic library and returning the pointers as the function return values rather than as a reference parameters.<br><br>This following PIR snippet calls my dynamic
library (birdseed), the SQLite3 dynamic library, and obtains the last column value of a row returned from a prepared statement.<br><br><span style="font-family: courier,monaco,monospace,sans-serif;">.sub main :main</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> .local pmc sqllib, bslib</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> .local pmc fn_dbh, dbh, fn_sth, sth, fn_close, fn_step, fn_final, fn_col_t</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> bslib = loadlib "birdseed"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> sqllib = loadlib "sqlite3"</span><br style="font-family:
courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> $S0 = "select * from dummy"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_dbh = dlfunc bslib, "get_db_handle", "pt"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_sth = dlfunc bslib, "get_statement_handle", "ppt"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_step = dlfunc sqllib, "sqlite3_step", "ip"</span><br
style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_final = dlfunc sqllib, "sqlite3_finalize", "ip"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_col_t = dlfunc sqllib, "sqlite3_column_text", "tpi"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> fn_close = dlfunc sqllib, "sqlite3_close", "ip"</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> dbh = fn_dbh("dummy.db") </span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family:
courier,monaco,monospace,sans-serif;"> sth = fn_sth(dbh, "select * from dummy")</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> </span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> $I1 = fn_step(sth)</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> say $I1</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> $S0 = fn_col_t(sth, 1)</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> say $S0</span><br style="font-family:
courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> $I2 = fn_final(sth)</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> say $I2</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> $I3 = fn_close(dbh)</span><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;"> say $I3</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family: courier,monaco,monospace,sans-serif;"><span style="font-family: courier,monaco,monospace,sans-serif;">.end</span><br style="font-family: courier,monaco,monospace,sans-serif;"><br style="font-family:
courier,monaco,monospace,sans-serif;">Here is the resulting output from Parrot<br><br><span style="font-family: courier,monaco,monospace,sans-serif;">C:\t\dv\parrot\PARROT~1\birdseed>parrot birdseed.pir<br>get_db_handle: got here<br>get_statement_handle: got here<br>100<br>This is tag 1<br>0<br>0</span><br><br>Basically, the line that reads "This is tag 1" is the second column of the first row returned from the table "dummy." The lines beginning with "get_" are being printf'd from my library. 100 is SQLITE_ROW (that is a row ready for plucking), and the 0's are SQLITE_OK.<br><br>So the trick now is to determine if the SQLite3.pir under <parrot src tree>/etc/SQLite3 can be retrofitted with the "potential" birdseed dynamic library and then be recognized by <parrot src tree>/etc/SQLite3/DBDI/Driver/SQLite3.pm.<br><br>More on this later ...<br><br>"Er uh, we're still doing the project, right?" :)<br><br><br><br>T<br><br><br><br>-- Todd
Presta<br>
-- http://www.asciiville.com</td></tr></tbody></table></div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>Thousand-oaks-pm mailing list<br><a ymailto="mailto:Thousand-oaks-pm@pm.org" href="/mc/compose?to=Thousand-oaks-pm@pm.org">Thousand-oaks-pm@pm.org</a><br><a href="http://mail.pm.org/mailman/listinfo/thousand-oaks-pm" target="_blank">http://mail.pm.org/mailman/listinfo/thousand-oaks-pm</a></div></blockquote></td></tr></table>