Home modules.gotpike.org
Username: Password: [Create Account]
[Forgot Password?]

Modules

ADT
Database
GTK2
GUI
IP
PiJAX
Public
Sql
Stdio
Subversion
System
Tools
Xosd
lua
v4l2
wx

Recent Changes

Public.ZeroMQ 1.1
Public.Template.Mustache 1.0
Public.Protocols.XMPP 1.4
Sql.Provider.jdbc 1.0
Public.Storage.Manta 1.0

Popular Downloads

Public.Parser.JSON2 1.0
Public.Parser.JSON 0.2
GTK2 2.23
Public.Web.FCGI 1.8
Public.Parser.XML2 1.48


Module Information
Sql.Provider.jdbc
Viewing contents of Sql_Provider_jdbc-1.0/README

This is a module providing support for connecting to data sources using JDBC.

This module requires that your Pike have Java support enabled (visible via pike --features).
You must also have the desired java driver in your CLASSPATH.

Note: SQL URLs as used by Pike for configuring database connections cause some friction with 
JDBC connection strings, especially those with embedded slashes, such as for H2. Take the
following H2 database connection string:


jdbc:h2:/path/to/mydb

If we try to use this within a pike SQL url we get strange behavior:

jdbc://jdbc:h2:/path/to/mydb

The first part tells pike to use the JDBC sql driver, and then it tries to parse the rest into
the server and database portions. In this example, it ends up mangling the path, which isn't what
we want. There are 2 ways to get around this:

1. Escape any special characters that Pike might be parsing on

The problem in this example is the slash, so by escaping slashes, we avoid any problems:

jdbc://jdbc:h2:\/path\/to\/mydb

2. Move the jdbc url out of the SQL url and into the host portion

The JDBC sql driver assumes the "hostname" field contains the jdbc connection string, so we can create 
a SQL URL that doesn't have a host portion:

jdbc://user:pass@
or
jdbc://user@
or even just
jdbc://

And then we can pass the jdbc connection string as the second argument to Sql.Sql(). The user and password
can be real (the ones you want to use), or they can be wholly made up, as you can override them by passing
the real user and password as the 4th and 5th arguments to Sql.Sql().

Example:

object s = Sql.Sql("jdbc://", "jdbc:h2:./test", "SA", "SA");



```
$ CLASSPATH=./h2-1.4.196.jar /usr/local/pike/8.0.462/bin/pike
Pike v8.0 release 462 running Hilfe v3.5 (Incremental Pike Frontend)
> object s = Sql.Sql("jdbc://", "jdbc:h2:/tmp/test", "SA", "SA");
> s->server_info();                           
(1) Result: "H2/1.4.196 (2017-06-10)@jdbc:h2:."
>  s->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES");
(2) Result: ({ /* 1 element */
                ([ /* 1 element */
                  "COUNT(*)": "29"
                ])
            })
```
Prepared (Compiled) Statements

JDBC allows a query to be prepared or compiled. This is desirable because many 
applications use a small number of distinct queries when considered as a template,
and compiling the query allows the database engine to re-use the partially executed 
query many times, thus saving considerable computation. When using compiled queries,
the variable parts of the query are identifed by query parameters, which are bound
at the time the compiled query is executed. The two common types of binding are 
a) positional, where the placeholder is a '?', and binding is performed based on the 
placeholder number within the query and b) named, where the placeholder is given a 
name or identifer in the form of :identifier, and then replacements are made by name.
Named parameters are helpful as they provide context within the query and also 
facilitate queries that use the same variable in multiple positions. 

JDBC supports positional parameters natively; the Pike JDBC provider provides a layer
that allows named parameters to be used. When a query statement is compiled using the 
Pike JDBC provider, named parameters are converted to positional parameters and at
the point of query execution (when the binding parameters are provided), the named
parameters are inserted at the proper spots. You may use named or positional parameters
interchangeably, though it is recommended that you not use a combination within a given
statement, as transposition errors are more likely to be made during development.

JDBC supports a wide range of parameter bindings, and at this time, only a relatively 
small subset are automatically supported by the Pike provider. If you need to bind a 
parameter that is not automatically handled, you may retrieve the JDBC statement 
object and use the Java methods to perform the specific bindings you require.

Using positional parameters:

```
> object s = Sql.Sql("jdbc://", "jdbc:h2:/tmp/test", "SA", "SA");
> s->query("CREATE TABLE foo (a integer not null primary key auto_increment, b char(4))");
> object q = s->compile_query("SELECT * FROM FOO WHERE A=?");
> s->query(q, ([1: 125])); // JDBC parameters start at 1
> s->query(q); // we've already bound the paramenters so we can reuse or replace 
               // them (in whole or partially) if desired.
```

Using named parameters:

```
> object s = Sql.Sql("jdbc://", "jdbc:h2:/tmp/test", "SA", "SA");
> s->query("CREATE TABLE foo (a integer not null primary key auto_increment, b char(4))");
> object q = s->compile_query("SELECT * FROM FOO WHERE A=:id");
> s->query(q, ([":id": 125]));
> s->query(q); // we've already bound the paramenters so we can reuse or replace 
               // them (in whole or partially) if desired.
```

gotpike.org | Copyright © 2004 - 2019 | Pike is a trademark of Department of Computer and Information Science, Linköping University