14. Database Access

  Module Sql

    Description

      The SQL module is a unified interface between pike and all its supported databases. The parts of this module that is usuable for all normal uses is the Sql class and the sql_result class.

    Example

          string people_in_group(string group) {
              Sql.Sql db = Sql.Sql("mysql://localhost/testdb");
              return db->query("SELECT name FROM users WHERE "
                               "group=%s", group)->name * ",";
           }

    Variable NULL

      `Val.Null Sql.NULL`

    Deprecated
    See also

      Val.null

    CLASS Sql.sql_result

      Description

        Implements the generic result of the SQL-interface. Used for return results from SQL.sql->big_query().

      Variable master_res

        `mixed Sql.sql_result.master_res`

      Description

        The actual result.

      Variable index

        `int Sql.sql_result.index`

      Description

        This is the number of the current row. The actual semantics differs between different databases.

      Method create

        `void Sql.sql_result(mixed res)`

      Description

        Create a new Sql.sql_result object

      Parameter `res`

        Result to use as base.

      Method num_rows

        `int num_rows()`

      Description

        Returns the number of rows in the result.

      Method num_fields

        `int num_fields()`

      Description

        Returns the number of fields in the result.

      Method eof

        `int eof()`

      Description

        Returns non-zero if there are no more rows.

      Method fetch_fields

        `array(mapping(string:mixed)) fetch_fields()`

      Description

        Return information about the available fields.

      Method seek

        `void seek(int skip)`

      Description

        Skip past a number of rows.

      Parameter `skip`

        Number of rows to skip.

      Method fetch_row

        `int|array(string|int|float) fetch_row()`

      Description

        Fetch the next row from the result.

      Returns

        Returns an array with one element per field in the same order as reported by fetch_fields() . See the Sql.Sql class documentation for more details on how different data types are represented.

      Method fetch_json_result

        `string fetch_json_result()`

      Description

        Fetch remaining result as `JSON`-encoded data.

    CLASS Sql.msql

      Description

        Implements the glue needed to access the Msql-module from the generic SQL module.

      Inherit msql

        inherit Msql.msql : msql

    CLASS Sql.tds

      Description

        The TDS SQL-protocol.

        This protocol is used by Sybase and Microsoft's SQL-servers.

      Example

            Sql.Sql con = Sql.Sql("tds://user:pass@host/database");

      See also

        Sql.Sql()

      Method tds_error

        `void tds_error(string msg, mixed ... args)`

      Description

        Format and report an error.

      Method insert_id

        `int insert_id()`

      Description

        Fetch the identity of the last insert (if available).

        This performs the query `"SELECT @@identity AS insert_id"`.

      Returns

        Returns the identity of the last insert as an integer if available. Otherwise returns `0` (zero).

      Method server_info

        `string server_info()`

      Description

        Return a string describing the server.

      Method error

        `string error()`

      Description

        Return the last error (or possibly the last warning or informational message).

      Method create

        `void Sql.tds(string|void server, string|void database, string|void user, string|void password, mapping|void options)`

      Description

        Connect to a remote SQL server via the TDS protocol.

      Parameter `server`

        Server to connect to.

      Parameter `database`

        Database to connect to.

      Parameter `user`

        User to access as.

        An explicit domain may be specified by preceeding the user name with the domain name and a `'\\'`.

      Parameter `password`

        Password to access with.

        Usually accessed via Sql.Sql() .

      See also

        Sql.Sql()

      ENUM Sql.tds.Token

        Description

          Tokens that may occur in the answers from the server.

      ENUM Sql.tds.EnvType

        Description

          Environment types.

          Used by TDS_ENV_CHANGE_TOKEN

      ENUM Sql.tds.FieldType

        Description

          Field types.

      CLASS Sql.tds.Connection

        Description

          A connection to a TDS server.

        Variable socket

          `Stdio.File Sql.tds.Connection.socket`

        Description

          The actual TCP connection.

        Method send_packet

          `InPacket send_packet(Packet p, int flag, int|void last)`

        Description

          Send a packet to the TDS server.

        Note

          May only be called when the connection is idle.

        Returns

          If last is true an InPacket with the result will be returned.

        CLASS Sql.tds.Connection.InPacket

          Description

            An incoming packet from the TDS server.

        CLASS Sql.tds.Connection.Packet

          Description

            An outgoing packet to the TDS server.

      CLASS Sql.tds.compile_query

        Description

          A compiled query.

        Method create

          `void Sql.tds.compile_query(string query)`

        Description

          Compile a query.

        See also

          big_query()

      CLASS Sql.tds.big_query

        Description

          A query result set.

        Method fetch_row

          `int|array(string|int) fetch_row()`

        Description

          Fetch the next row from the result set.

        Returns

          Returns `0` (zero) if all rows have been returned.

          Otherwise returns an `array(string|int)` with one entry for each field. If the field is `NULL` the entry will be `0` (zero), otherwise the entry will contain a string representing the value.

        Method fetch_fields

          `array(mapping(string:mixed)) fetch_fields()`

        Description

          Fetch a description of the fields in the result.

        Returns

          Returns an array with a mapping for each of the fields in the result.

          The mappings contain the following information:

          - Standard fields:

            `"name" : string` | The name of the field.
            `"table" : string|void` | The name of the table (if available).

          - TDS-specific fields:

            `"nullable" : int(0..1)` | `1` if the field may contain `NULL`.
            `"writeable" : int(0..1)` | `1` if the field may be changed.
            `"identity" : int(0..1)` | `1` if the field is the identity for the row.
            `"column_size" : int` | Width of the field.
            `"timestamp" : int(0..1)` | Time stamp information for last change is available.
            `"column_prec" : int|void` | Precision of the field.
            `"column_scale" : int|void` | Scale exponent of the field.
            `"usertype" : int` | Internal use only.
            `"flags" : int`
            `"column_type" : int`
            `"cardinal_type" : int`
            `"varint_size" : int`

        Method create

          `void Sql.tds.big_query(string|compile_query query)`

        Description

          Execute a query against the database.

        Parameter `query`

          The query to execute. This can either be a string, or a compiled query.

        See also

          compile_query()

    CLASS Sql.postgres

      Description

        This is an interface to the Postgres (Postgres95, pgsql) database server. This module may or may not be available on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.

      Note

        This driver has been deprecated. You should use the more advanced driver Sql.pgsql to access PostgreSQL databases instead.

      Note

        Also note that this module uses blocking I/O to connect to the server. It is thread-safe, and so it can be used in a multithread environment.

        The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the Pike interpreter; some notice and warning notifications might are dumped on stderr.

        `"PGHOST"` | Sets the name of the default host to connect to. It defaults to `"localhost"`.
        `"PGOPTIONS"` | Sets some extra flags for the frontend-backend connection. do not set unless you're sure of what you're doing.
        `"PGPORT"` | Sets the default port to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver).
        `"PGTTY"` | Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing.
        `"PGDATABASE"` | Sets the default database to connect to.
        `"PGREALM"` | Sets the default realm for Kerberos authentication. I never used this, so I can't help you.

        Refer to the Postgres documentation for further details.

      See also

        Sql.pgsql , Sql.Sql , Postgres.postgres , Sql.postgres_result

      Inherit mo

        inherit Postgres.postgres : mo

      Method select_db

        `void select_db(string dbname)`

      Description

        This function allows you to connect to a database. Due to restrictions of the Postgres frontend-backend protocol, you always have to be connected to a database, so in fact this function just allows you to connect to a different database on the same server.

      Note

        This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)

      See also

        create

      Method error

        `string error()`

      Description

        This function returns the textual description of the last server-related error. Returns `0` if no error has occurred yet. It is not cleared upon reading (can be invoked multiple times, will return the same result until a new error occurs).

      See also

        big_query

      Method host_info

        `string host_info()`

      Description

        This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).

      Method reset

        `void reset()`

      Description

        This function resets the connection to the backend. Can be used for a variety of reasons, for example to detect the status of a connection.

      Note

        This function is Postgres-specific, and thus it is not available through the generic SQL-interface.

      Variable version

        `string Sql.postgres.version`

      Description

        Should you need to report a bug to the author, please submit along with the report the driver version number, as returned by this call.

      Method create

        `void Sql.postgres()`
        `void Sql.postgres(string host, void|string database, void|string user, void|string password, void|mapping options)`

      Description

        With no arguments, this function initializes (reinitializes if a connection had been previously set up) a connection to the Postgres backend. Since Postgres requires a database to be selected, it will try to connect to the default database. The connection may fail however for a variety of reasons, in this case the most likely of all is because you don't have enough authority to connect to that database. So use of this particular syntax is discouraged.

        The host argument can have the syntax `"hostname"` or `"hostname:portname"`. This allows to specify the TCP/IP port to connect to. If it is `0` or `""`, it will try to connect to localhost, default port.

        The database argument specifies the database to connect to. If `0` or `""`, it will try to connect to the specified database.

        The username and password arguments are silently ignored, since the Postgres C API doesn't allow to connect to the server as any user different than the user running the interface.

      Note

        You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.

        You don't need bothering about syncronizing the connection to the database: it is automatically closed (and the database is sync-ed) when the object is destroyed.

      See also

        Sql.pgsql , Postgres.postgres , Sql.Sql , postgres->select_db

      Method set_notify_callback

        `void set_notify_callback()`
        `void set_notify_callback(function f)`
        `void set_notify_callback(function f, int|float poll_delay)`

      Description

        With Postgres you can associate events and notifications to tables. This function allows you to detect and handle such events.

        With no arguments, resets and removes any callback you might have put previously, and any polling cycle.

        With one argument, sets the notification callback (there can be only one for each sqlobject).

        With two arguments, sets a notification callback and sets a polling cycle.

        The polling cycle is necessary because of the way notifications are delivered, that is piggyback with a query result. This means that if you don't do any query, you'll receive no notification. The polling cycle starts a call_out cycle which will do an empty query when the specified interval expires, so that pending notifications may be delivered.

        The callback function must return no value, and takes a string argument, which will be the name of the table on which the notification event has occurred. In future versions, support for user-specified arguments will be added.

      Note

        The polling cycle can be run only if your process is in "event-driven mode" (that is, if 'main' has returned a negative number).

        This function is Postgres-specific, and thus it is not available through the generic SQL-interface.

      FIXME

        An integer can be passed as first argument, but it's effect is not documented.

      Method create_db

        `void create_db(string db)`

      Description

        This function creates a new database with the given name (assuming we have enough permissions to do this).

      See also

        drop_db

      Method drop_db

        `void drop_db(string db)`

      Description

        This function destroys a database and all the data it contains (assuming we have enough permissions to do so).

      See also

        create_db

      Method server_info

        `string server_info()`

      Description

        This function returns a string describing the server we are talking to. It has the form `"servername/serverversion"` (like the HTTP protocol description) and is most useful in conjunction with the generic SQL-server module.

      Method list_dbs

        `array(string) list_dbs(void|string glob)`

      Description

        Lists all the databases available on the server. If glob is specified, lists only those databases matching it.

      Method list_tables

        `array(string) list_tables(void|string glob)`

      Description

        Returns an array containing the names of all the tables in the currently selected database. If a glob is specified, it will return only those tables whose name matches it.

      Method list_fields

        `array(mapping(string:mixed)) list_fields(string table, void|string wild)`

      Description

        Returns a mapping, indexed on the column name, of mappings describing the attributes of a table of the current database. If a glob is specified, will return descriptions only of the columns matching it.

        The currently defined fields are:

        `"has_rules" : int` |
        `"is_shared" : int` |
        `"owner" : string` | The textual representation of a Postgres uid.
        `"length" : string` |
        `"text" : string` | A textual description of the internal (to the server) type-name
        `"default" : mixed` |
        `"expires" : string` | The "relexpires" attribute for the table. Obsolescent; modern versions of Postgres don't seem to use this feature, so don't count on this field to contain any useful value.

      Method big_query

        `int|object big_query(object|string q, mapping(string|int:mixed)|void bindings)`

      Description

        This is the only provided interface which allows you to query the database. If you wish to use the simpler "query" function, you need to use the Sql.Sql generic SQL-object.

        It returns a postgres_result object (which conforms to the Sql.sql_result standard interface for accessing data). I recommend using query() for simpler queries (because it is easier to handle, but stores all the result in memory), and big_query() for queries you expect to return huge amounts of data (it's harder to handle, but fetches results on demand).

      Note

        This function can raise exceptions.

      See also

        Sql.Sql , Sql.sql_result

      Method streaming_query

        `int|object streaming_query(object|string q, mapping(string|int:mixed)|void bindings)`

      Description

        This is an alias for big_query() , since big_query() already supports streaming.

      See also

        big_query , Sql.Sql , Sql.sql_result

      Inherit pgsql

        inherit Sql.pgsql : pgsql

      Description

        Maps SQL-urls for `postgres://[user[:password]@][hostname][:port][/database]` onto `pgsql://[user[:password]@][hostname][:port][/database]`

        The reason this happens, is because Pike was compiled without libpq support, therefore Pike falls back to the faster, smaller memory footprint, more advanced and native PostgreSQL driver called Sql.pgsql .

      See also

        Sql.pgsql , Sql.Sql

    CLASS Sql.null

      Description

        The NULL Sql handler.

        This is an empty Sql handler typically used to test other functionality of the Sql module.

      Method quote

        `string quote(string s)`

      Returns

        `<font face='courier'>sprintf` ("quote(%q)", s).

      Method query

        `array(mapping(string:mixed)) query(string query, mapping(string:mixed)|void bindings)`

      Returns

        Returns an array with a single element:

        `"query" : string` | The query string before formating.
        `"bindings" : string` | `<font face='courier'>sprintf` ("%O", <font face='courier'>bindings ).
        `"formatted_query" : string` | The formatted query.

    CLASS Sql.pgsqls

      Description

        Implements SQL-urls for `pgsqls://[user[:password]@][hostname][:port][/database]`

        Sets the connection to SSL-mode, otherwise identical to Sql.pgsql .

      See also

        Sql.pgsql , Sql.Sql

      Inherit pgsql

        inherit Sql.pgsql : pgsql

    CLASS Sql.mysqls

      Description

        Implements SQL-urls for `mysqls://[user[:password]@][hostname][:port][/database]`

        Sets the connection to SSL-mode, and sets the default configuration file to `"/etc/my.cnf"`.

      FIXME

        Ought to load a suitable default configuration file for Win32 too.

      Note

        This connection method only exists if the Mysql-module has been compiled with SSL-support.

      Inherit mysql

        inherit Sql.mysql : mysql

    CLASS Sql.mysql

      Description

        This class encapsulates a connection to a MySQL server, and implements the glue needed to access the Mysql module from the generic SQL module.

        ## Typed mode

        When query results are returned in typed mode, the MySQL data types are represented like this:

        The NULL value

          Returned as Val.null .

        BIT, TINYINT, BOOL, SMALLINT, MEDIUMINT, INT, BIGINT

          Returned as pike integers.

        FLOAT, DOUBLE

          Returned as pike floats.

        DECIMAL

          Returned as pike integers for fields that are declared to contain zero decimals, otherwise returned as Gmp.mpq objects.

        DATE, DATETIME, TIME, YEAR

          Returned as strings in their display representation (see the MySQL manual).

          Calendar objects are not used partly because they always represent a specific point or range in time, which these MySQL types do not.

        TIMESTAMP

          Also returned as strings in the display representation.

          The reason is that it's both more efficient and more robust (wrt time zone interpretations) to convert these to unix timestamps on the MySQL side rather than in the client glue. I.e. use the `UNIX_TIMESTAMP` function in the queries to retrieve them as unix timestamps on integer form.

        String types

          All string types are returned as pike strings. The MySQL glue can handle charset conversions for text strings - see set_charset and set_unicode_decode_mode .

      Inherit mysql

        inherit Mysql.mysql : mysql

      Method set_unicode_encode_mode

        `int(0..1) set_unicode_encode_mode(int enable)`

      Description

        Enables or disables unicode encode mode.

        In this mode, if the server supports UTF-8 and the connection charset is `latin1` (the default) or `unicode` then big_query handles wide unicode queries. Enabled by default.

        Unicode encode mode works as follows: Eight bit strings are sent as `latin1` and wide strings are sent using `utf8`. big_query sends `SET character_set_client` statements as necessary to update the charset on the server side. If the server doesn't support that then it fails, but the wide string query would fail anyway.

        To make this transparent, string literals with introducers (e.g. `_binary 'foo'`) are excluded from the UTF-8 encoding. This means that big_query needs to do some superficial parsing of the query when it is a wide string.

      Returns

        `1` | Unicode encode mode is enabled.
        `0` | Unicode encode mode couldn't be enabled because an incompatible connection charset is set. You need to do `<font face='courier'>set_charset` ("latin1") or `<font face='courier'>set_charset` ("unicode") to enable it.

      Note

        Note that this mode doesn't affect the MySQL system variable `character_set_connection`, i.e. it will still be set to `latin1` by default which means server functions like `UPPER()` won't handle non-`latin1` characters correctly in all cases.

        To fix that, do `<font face='courier'>set_charset` ("unicode"). That will allow unicode encode mode to work while `utf8` is fully enabled at the server side.

        Tip: If you enable `utf8` on the server side, you need to send raw binary strings as `_binary'...'`. Otherwise they will get UTF-8 encoded by the server.

      Note

        When unicode encode mode is enabled and the connection charset is `latin1`, the charset accepted by big_query is not quite Unicode since `latin1` is based on `cp1252`. The differences are in the range `0x80..0x9f` where Unicode has control chars.

        This small discrepancy is not present when the connection charset is `unicode`.

      See also

        set_unicode_decode_mode , set_charset

      Method get_unicode_encode_mode

        `int get_unicode_encode_mode()`

      Description

        Returns nonzero if unicode encode mode is enabled, zero otherwise.

      See also

        set_unicode_encode_mode

      Method set_unicode_decode_mode

        `void set_unicode_decode_mode(int enable)`

      Description

        Enable or disable unicode decode mode.

        In this mode, if the server supports UTF-8 then non-binary text strings in results are automatically decoded to (possibly wide) unicode strings. Not enabled by default.

        The statement "`SET character_set_results = utf8`" is sent to the server to enable the mode. When the mode is disabled, "`SET character_set_results = xxx`" is sent, where `xxx` is the connection charset that get_charset returns.

      Parameter `enable`

        Nonzero enables this feature, zero disables it.

      Throws

        Throws an exception if the server doesn't support this, i.e. if the statement above fails. The MySQL system variable `character_set_results` was added in MySQL 4.1.1.

        An error is also thrown if Pike has been compiled with a MySQL client library older than 4.1.0, which lack the necessary support for this.

      See also

        set_unicode_encode_mode

      Method get_unicode_decode_mode

        `int get_unicode_decode_mode()`

      Description

        Returns nonzero if unicode decode mode is enabled, zero otherwise.

      See also

        set_unicode_decode_mode

      Method set_charset

        `void set_charset(string charset)`

      Description

        Changes the connection charset. Works similar to sending the query `SET NAMES <font face='courier'>charset` but also records the charset on the client side so that various client functions work correctly.

        charset is a MySQL charset name or the special value `"unicode"` (see below). You can use `SHOW CHARACTER SET` to get a list of valid charsets.

        Specifying `"unicode"` as charset is the same as `"utf8"` except that unicode encode and decode modes are enabled too. Briefly, this means that you can send queries as unencoded unicode strings and will get back non-binary text results as unencoded unicode strings. See set_unicode_encode_mode and set_unicode_decode_mode for further details.

      Throws

        Throws an exception if the server doesn't support this, i.e. if the statement `SET NAMES` fails. Support for it was added in MySQL 4.1.0.

      Note

        If charset is `"latin1"` and unicode encode mode is enabled (the default) then big_query can send wide unicode queries transparently if the server supports UTF-8. See set_unicode_encode_mode .

      Note

        If unicode decode mode is already enabled (see set_unicode_decode_mode ) then this function won't affect the result charset (i.e. the MySQL system variable `character_set_results`).

        Actually, a query `SET character_set_results = utf8` will be sent immediately after setting the charset as above if unicode decode mode is enabled and charset isn't `"utf8"`.

      Note

        You should always use either this function or the `"mysql_charset_name"` option to create to set the connection charset, or more specifically the charset that the server expects queries to have (i.e. the MySQL system variable `character_set_client`). Otherwise big_query might not work correctly.

        Afterwards you may change the system variable `character_set_connection`, and also `character_set_results` if unicode decode mode isn't enabled.

      Note

        The MySQL `latin1` charset is close to Windows `cp1252`. The difference from ISO-8859-1 is a bunch of printable chars in the range `0x80..0x9f` (which contains control chars in ISO-8859-1). For instance, the euro currency sign is `0x80`.

        You can use the `mysql-latin1` encoding in the Locale.Charset module to do conversions, or just use the special `"unicode"` charset instead.

      See also

        get_charset , set_unicode_encode_mode , set_unicode_decode_mode

      Method get_charset

        `string get_charset()`

      Description

        Returns the MySQL name for the current connection charset.

        Returns `"unicode"` if unicode encode mode is enabled and UTF-8 is used on the server side (i.e. in `character_set_connection`).

      Note

        In servers with full charset support (i.e. MySQL 4.1.0 or later), this corresponds to the MySQL system variable `character_set_client` (with one exception - see next note) and thus controls the charset in which queries are sent. The charset used for text strings in results might be something else (and typically is if unicode decode mode is enabled; see set_unicode_decode_mode ).

      Note

        If the returned charset is `latin1` or `unicode` and unicode encode mode is enabled (the default) then `character_set_client` in the server might be either `latin1` or `utf8`, depending on the last sent query. See set_unicode_encode_mode for more info.

      See also

        set_charset

      Method quote

        `string quote(string s)`

      Description

        Quote a string so that it can safely be put in a query.

      Parameter `s`

        String to quote.

      Method latin1_to_utf8

        `string latin1_to_utf8(string s)`

      Description

        Converts a string in MySQL `latin1` format to UTF-8.

      Method utf8_encode_query

        `string utf8_encode_query(string q, function(string:string) encode_fn)`

      Description

        Encodes the appropriate sections of the query with encode_fn . Everything except strings prefixed by an introducer (i.e. `_something` or `N`) is encoded.

      Method encode_time

        `string encode_time(int time, void|int date)`

      Description

        Converts a system time value to an appropriately formatted time spec for the database.

      Parameter `time`

        Time to encode.

      Parameter `date`

        If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.

      Method encode_date

        `string encode_date(int time)`

      Description

        Converts a system time value to an appropriately formatted date-only spec for the database.

      Parameter `time`

        Time to encode.

      Method encode_datetime

        `string encode_datetime(int time)`

      Description

        Converts a system time value to an appropriately formatted date and time spec for the database.

      Parameter `time`

        Time to encode.

      Method decode_time

        `int decode_time(string timestr, void|int date)`

      Description

        Converts a database time spec to a system time value.

      Parameter `timestr`

        Time spec to decode.

      Parameter `date`

        Take the date part from this system time value. If zero, a seconds-since-midnight value is returned.

      Method decode_date

        `int decode_date(string datestr)`

      Description

        Converts a database date-only spec to a system time value. Assumes 4-digit years.

      Parameter `datestr`

        Date spec to decode.

      Method decode_datetime

        `int decode_datetime(string timestr)`

      Description

        Converts a database date and time spec to a system time value. Can decode strings missing the time part.

      Parameter `datestr`

        Date and time spec to decode.

      Method big_query

        `Mysql.mysql_result big_query(string query, mapping(string|int:mixed)|void bindings, void|string charset)`

      Description

        Sends a query to the server.

      Parameter `query`

        The SQL query.

      Parameter `bindings`

        An optional bindings mapping. See Sql.query for details about this.

      Parameter `charset`

        An optional charset that will be used temporarily while sending query to the server. If necessary, a query

        SET character_set_client=charset

      is sent to the server first, then query is sent as-is, and then the connection charset is restored again (if necessary).

      Primarily useful with charset set to `"latin1"` if unicode encode mode (see set_unicode_encode_mode ) is enabled (the default) and you have some large queries (typically blob inserts) where you want to avoid the query parsing overhead.

    Returns

      A Mysql.mysql_result object is returned if the query is of a kind that returns a result. Zero is returned otherwise.

      The individual fields are returned as strings except for `NULL`, which is returned as UNDEFINED .

    See also

      Sql.big_query() , big_typed_query() , streaming_query()

    Method streaming_query

      `Mysql.mysql_result streaming_query(string query, mapping(string|int:mixed)|void bindings, void|string charset)`

    Description

      Makes a streaming SQL query.

      This function sends the SQL query query to the Mysql-server. The result of the query is streamed through the returned Mysql.mysql_result object. Note that the involved database tables are locked until all the results has been read.

      In all other respects, it behaves like big_query .

    See also

      big_query() , streaming_typed_query()

    Method big_typed_query

      `Mysql.mysql_result big_typed_query(string query, mapping(string|int:mixed)|void bindings, void|string charset)`

    Description

      Makes a typed SQL query.

      This function sends the SQL query query to the Mysql-server.

      The types of the result fields depend on the corresponding SQL types. They are mapped as follows:

      `Sql.Null` | The `NULL` value is returned as Sql.NULL .
      `int` | Integer values are returned as `int` values.
      `float` | Floating point values are returned as `float` values.
      `string` | All other SQL field types are returned as `string` values.

      In all other respects, it behaves like big_query .

    See also

      big_query() , streaming_typed_query()

    Method streaming_typed_query

      `Mysql.mysql_result streaming_typed_query(string query, mapping(string|int:mixed)|void bindings, void|string charset)`

    Description

      Makes a streaming typed SQL query.

      This function acts as the combination of streaming_query() and big_typed_query() .

    See also

      big_typed_query() , streaming_typed_query()

    Method is_keyword

      `int(0..1) is_keyword(string name)`

    Description

      Return 1 if the argument name is a mysql keyword that needs to be quoted in a query. The list is currently up-to-date with MySQL 5.1.

  CLASS Sql.pgsql

    Description

      This is an interface to the PostgreSQL database server. This module is independent of any external libraries. Note that you do not need to have a PostgreSQL server running on your host to use this module: you can connect to the database over a TCP/IP socket.

      This module replaces the functionality of the older Sql.postgres and Postgres.postgres modules.

      This module supports the following features:

      - PostgreSQL network protocol version 3, authentication methods currently supported are: cleartext and MD5 (recommended).

      - Streaming queries which do not buffer the whole resultset in memory.

      - Automatic binary transfers to and from the database for most common datatypes (amongst others: integer, text and bytea types).

      - Automatic character set conversion and native wide string support. Supports UTF8/Unicode for multibyte characters, and all single-byte character sets supported by the database.

      - SQL-injection protection by allowing just one statement per query and ignoring anything after the first (unquoted) semicolon in the query.

      - COPY support for streaming up- and download.

      - Accurate error messages.

      - Automatic precompilation of complex queries (session cache).

      - Multiple simultaneous queries on the same database connection.

      - Cancelling of long running queries by force or by timeout.

      - Event driven NOTIFY.

      - SSL encrypted connections (optional or forced).

      Check the PostgreSQL documentation for further details.

    Note

      Multiple simultaneous queries on the same database connection is a feature that none of the other database drivers for Pike support. So, although it's efficient, its use will make switching database drivers difficult.

    See also

      Sql.Sql , Sql.postgres , http://www.postgresql.org/docs/

    Method create

      `void Sql.pgsql()`
      `void Sql.pgsql(string host, void|string database, void|string user, void|string password, void|mapping(string:mixed) options)`

    Description

      With no arguments, this function initialises (reinitialises if a connection has been set up previously) a connection to the PostgreSQL backend. Since PostgreSQL requires a database to be selected, it will try to connect to the default database. The connection may fail however, for a variety of reasons; in this case the most likely reason is because you don't have sufficient privileges to connect to that database. So use of this particular syntax is discouraged.

    Parameter `host`

      Should either contain `"hostname"` or `"hostname:portname"`. This allows you to specify the TCP/IP port to connect to. If the parameter is `0` or `""`, it will try to connect to localhost, default port.

    Parameter `database`

      Specifies the database to connect to. Not specifying this is only supported if the PostgreSQL backend has a default database configured. If you do not want to connect to any live database, you can use template1 .

    Parameter `options`

      Currently supports at least the following:

      `"reconnect" : int` | Set it to zero to disable automatic reconnects upon losing the connection to the database. Not setting it, or setting it to one, will cause one timed reconnect to take place. Setting it to -1 will cause the system to try and reconnect indefinitely.
      `"use_ssl" : int` | If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will fallback to plain unencrypted.
      `"force_ssl" : int` | If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will abort.
      `"text_query" : int` | Send queries to and retrieve results from the database using text instead of the, generally more efficient, default native binary method. Turning this on will allow multiple statements per query separated by semicolons.
      `"cache_autoprepared_statements" : int` | If set to zero, it disables the automatic statement prepare and cache logic; caching prepared statements can be problematic when stored procedures and tables are redefined which leave stale references in the already cached prepared statements.
      `"client_encoding" : string` | Character encoding for the client side, it defaults to using the default encoding specified by the database, e.g. UTF8 or SQL_ASCII .
      `"standard_conforming_strings" : string` | When on, backslashes in strings must not be escaped any longer, quote() automatically adjusts quoting strategy accordingly.
      `"escape_string_warning" : string` | When on, a warning is issued if a backslash (\) appears in an ordinary string literal and standard_conforming_strings is off, defaults to on

      For the numerous other options please check the PostgreSQL manual.

    Note

      You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.

    See also

      Postgres.postgres , Sql.Sql , select_db() , http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=client+connection+defaults

    Method error

      `string error(void|int clear)`

    Returns

      The textual description of the last server-related error. Returns `0` if no error has occurred yet. It is not cleared upon reading (can be invoked multiple times, will return the same result until a new error occurs).

      During the execution of a statement, this function accumulates all non-error messages (notices, warnings, etc.). If a statement does not generate any errors, this function will return all collected messages from the last statement.

    Note

      The string returned is not newline-terminated.

    Parameter `clear`

      To clear the error, set it to `1`.

    See also

      big_query()

    Method host_info

      `string host_info()`

    Description

      This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).

    See also

      server_info()

    Method is_open

      `int is_open()`

    Description

      Returns true if the connection seems to be open.

    Note

      This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.

      For a more reliable check of whether the connection is alive, please use ping() .

    See also

      ping()

    Method ping

      `int ping()`

    Description

      Check whether the connection is alive.

    Returns

      Returns one of the following:

      `0` | Everything ok.
      `1` | The connection reconnected automatically.
      `-1` | The server has gone away, and the connection is dead.

    See also

      is_open()

    Method cancelquery

      `void cancelquery()`

    Description

      Cancels the currently running query.

    See also

      reload() , resync()

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method set_charset

      `void set_charset(string charset)`

    Description

      Changes the connection charset. When set to UTF8 , the query, parameters and results can be Pike-native wide strings.

    Parameter `charset`

      A PostgreSQL charset name.

    See also

      get_charset() , create() , http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=character+sets

    Method get_charset

      `string get_charset()`

    Returns

      The PostgreSQL name for the current connection charset.

    See also

      set_charset() , getruntimeparameters() , http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=character+sets

    Method getruntimeparameters

      `mapping(string:string) getruntimeparameters()`

    Returns

      Currently active runtimeparameters for the open session; these are initialised by the options parameter during session creation, and then processed and returned by the server. Common values are:

      `"client_encoding" : string` | Character encoding for the client side, e.g. UTF8 or SQL_ASCII .
      `"server_encoding" : string` | Character encoding for the server side as determined when the database was created, e.g. `"UTF8"` or `"SQL_ASCII"`.
      `"DateStyle" : string` | Date parsing/display, e.g. `"ISO, DMY"`.
      `"TimeZone" : string` | Default timezone used by the database, e.g. `"localtime"`.
      `"standard_conforming_strings" : string` | When on, backslashes in strings must not be escaped any longer.
      `"session_authorization" : string` | Displays the authorisationrole which the current session runs under.
      `"is_superuser" : string` | Indicates if the current authorisationrole has database-superuser privileges.
      `"integer_datetimes" : string` | Reports wether the database supports 64-bit-integer dates and times.
      `"server_version" : string` | Shows the server version, e.g. `"8.3.3"`.

      The values can be changed during a session using SET commands to the database. For other runtimeparameters check the PostgreSQL documentation.

    See also

      http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=client+connection+defaults

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method getstatistics

      `mapping(string:mixed) getstatistics()`

    Returns

      A set of statistics for the current session:

      `"warnings_dropped" : int` | Number of warnings/notices generated by the database but not collected by the application by using error() after the statements that generated them.
      `"skipped_describe_count" : int` | Number of times the driver skipped asking the database to describe the statement parameters because it was already cached.
      `"used_prepared_statements" : int` | Numer of times prepared statements were used from cache instead of reparsing in the current session.
      `"current_prepared_statements" : int` | Cache size of currently prepared statements.
      `"current_prepared_statement_hits" : int` | Sum of the number hits on statements in the current statement cache.
      `"prepared_portal_count" : int` | Total number of prepared portals generated.
      `"prepared_statement_count" : int` | Total number of prepared statements generated.
      `"portals_opened_count" : int` | Total number of portals opened, i.e. number of statements issued to the database.
      `"blocked_count" : int` | Number of times the driver had to (briefly) wait for the database to send additional data.
      `"bytes_received" : int` | Total number of bytes received from the database so far.
      `"messages_received" : int` | Total number of messages received from the database (one SQL-statement requires multiple messages to be exchanged).
      `"bytes_sent" : int` | Total number of bytes sent to the database so far.
      `"packets_sent" : int` | Total number of packets sent to the database (one packet usually contains multiple messages).
      `"reconnect_count" : int` | Number of times the connection to the database has been lost.
      `"portals_in_flight" : int` | Currently still open portals, i.e. running statements.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method setcachedepth

      `int setcachedepth(void|int newdepth)`

    Parameter `newdepth`

      Sets the new cachedepth for automatic caching of prepared statements.

    Returns

      The previous cachedepth.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method settimeout

      `int settimeout(void|int newtimeout)`

    Parameter `newtimeout`

      Sets the new timeout for long running queries.

    Returns

      The previous timeout.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method setportalbuffersize

      `int setportalbuffersize(void|int newportalbuffersize)`

    Parameter `newportalbuffersize`

      Sets the new portalbuffersize for buffering partially concurrent queries.

    Returns

      The previous portalbuffersize.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method setfetchlimit

      `int setfetchlimit(void|int newfetchlimit)`

    Parameter `newfetchlimit`

      Sets the new fetchlimit to interleave queries.

    Returns

      The previous fetchlimit.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method close

      `void close()`

    Description

      Closes the connection to the database, any running queries are terminated instantly.

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method reload

      `void reload()`

    Description

      For PostgreSQL this function performs the same function as resync() .

    See also

      resync() , cancelquery()

    Method resync

      `void resync()`

    Description

      Resyncs the database session; typically used to make sure the session is not still in a dangling transaction.

      If called while queries/portals are still in-flight, this function is a no-op.

      If called while the connection is in idle state, the function is lightweight and briefly touches base with the database server to make sure client and server are in sync.

      If issued while inside a transaction, it will rollback the transaction, close all open cursors, drop all temporary tables and reset all session variables to their default values.

    See also

      cancelquery() , reload()

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method select_db

      `void select_db(string dbname)`

    Description

      This function allows you to connect to a database. Due to restrictions of the Postgres frontend-backend protocol, you always have to be connected to a database, so in fact this function just allows you to connect to a different database on the same server.

    Note

      This function can raise exceptions if something goes wrong (backend process not running, insufficient privileges...)

    See also

      create()

    Method set_notify_callback

      `void set_notify_callback(string condition, void|function(int:void) notify_cb, void|int selfnotify, mixed ... args)`

    Description

      With PostgreSQL you can LISTEN to NOTIFY events. This function allows you to detect and handle such events.

    Parameter `condition`

      Name of the notification event we're listening to. A special case is the empty string, which matches all events, and can be used as fallback function which is called only when the specific condition is not handled. Another special case is _reconnect which gets called whenever the connection unexpectedly drops and reconnects to the database.

    Parameter `notify_cb`

      Function to be called on receiving a notification-event of condition condition . The callback function is invoked with `void notify_cb(pid,condition,extrainfo, .. args);` pid is the process id of the database session that originated the event. condition contains the current condition. extrainfo contains optional extra information specified by the database. The rest of the arguments to notify_cb are passed verbatim from args . The callback function must return no value.

    Parameter `selfnotify`

      Normally notify events generated by your own session are ignored. If you want to receive those as well, set selfnotify to one.

    Parameter `args`

      Extra arguments to pass to notify_cb .

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method quote

      `string quote(string s)`

    Returns

      The given string, but escapes/quotes all contained magic characters according to the quoting rules of the current session for non-binary arguments in textual SQL-queries.

    Note

      Quoting must not be done for parameters passed in bindings.

    See also

      big_query() , quotebinary() , create()

    Method quotebinary

      `string quotebinary(string s)`

    Returns

      The given string, but escapes/quotes all contained magic characters for binary (bytea) arguments in textual SQL-queries.

    Note

      Quoting must not be done for parameters passed in bindings.

    See also

      big_query() , quote()

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method create_db

      `void create_db(string db)`

    Description

      This function creates a new database (assuming we have sufficient privileges to do this).

    Parameter `db`

      Name of the new database.

    See also

      drop_db()

    Method drop_db

      `void drop_db(string db)`

    Description

      This function destroys a database and all the data it contains (assuming we have sufficient privileges to do so). It is not possible to delete the database you're currently connected to. You can connect to database template1 to avoid connecting to any live database.

    Parameter `db`

      Name of the database to be deleted.

    See also

      create_db()

    Method server_info

      `string server_info()`

    Returns

      A string describing the server we are talking to. It has the form `"servername/serverversion"` (like the HTTP protocol description) and is most useful in conjunction with the generic SQL-server module.

    See also

      host_info()

    Method list_dbs

      `array(string) list_dbs(void|string glob)`

    Returns

      An array of the databases available on the server.

    Parameter `glob`

      If specified, list only those databases matching it.

    Method list_tables

      `array(string) list_tables(void|string glob)`

    Returns

      An array containing the names of all the tables and views in the path in the currently selected database.

    Parameter `glob`

      If specified, list only the tables with matching names.

    Method list_fields

      `array(mapping(string:mixed)) list_fields(void|string table, void|string glob)`

    Returns

      A mapping, indexed on the column name, of mappings describing the attributes of a table of the current database. The currently defined fields are:

      `"schema" : string` | Schema the table belongs to
      `"table" : string` | Name of the table
      `"kind" : string` | Type of table
      `"owner" : string` | Tableowner
      `"rowcount" : int` | Estimated rowcount of the table
      `"datasize" : int` | Estimated total datasize of the table in bytes
      `"indexsize" : int` | Estimated total indexsize of the table in bytes
      `"name" : string` | Name of the column
      `"type" : string` | A textual description of the internal (to the server) column type-name
      `"typeoid" : int` | The OID of the internal (to the server) column type
      `"length" : string` | Size of the columndatatype
      `"default" : mixed` | Default value for the column
      `"is_shared" : int` | If the table has any indices
      `"has_index" : int`
      `"has_primarykey" : int` | If the table has a primary key

    Parameter `glob`

      If specified, list only the tables with matching names. Setting it to `*` will include system columns in the list.

    Method status_commit

      `string status_commit()`

    Returns

      The current commitstatus of the connection. Returns either one of:

      `idle`
      `intransaction`
      `infailedtransaction`

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method big_query

      `Sql.pgsql_util.pgsql_result big_query(string query)`
      `Sql.pgsql_util.pgsql_result big_query(string query, mapping bindings)`

    Description

      This is the only provided interface which allows you to query the database. If you wish to use the simpler Sql.Sql()->query() function, you need to use the Sql.Sql generic SQL-object.

      Bindings are supported natively straight across the network. Special bindings supported are:

      `":_cache" : int` | Forces caching on or off for the query at hand.
      `":_text" : int` | Forces text mode in communication with the database for queries on or off for the query at hand. Potentially more efficient than the default binary method for simple queries with small or no result sets. Note that this mode causes all but the first query result of a list of semicolon separated statements to be discarded.

    Returns

      A Sql.pgsql_util.pgsql_result object (which conforms to the Sql.sql_result standard interface for accessing data). It is recommended to use Sql.Sql()->query() for simpler queries (because it is easier to handle, but stores all the result in memory), and Sql.Sql()->big_query() for queries you expect to return huge amounts of data (it's harder to handle, but fetches results on demand).

    Note

      This function can raise exceptions.

    Note

      This function supports multiple simultaneous queries (portals) on a single database connection. This is a feature not commonly supported by other database backends.

    Note

      This function, by default, does not support multiple queries in one querystring. I.e. it allows for but does not require a trailing semicolon, but it simply ignores any commands after the first unquoted semicolon. This can be viewed as a limited protection against SQL-injection attacks. To make it support multiple queries in one querystring, use the :_text option.

    See also

      big_typed_query() , Sql.Sql , Sql.sql_result , Sql.Sql()->query() , Sql.pgsql_util.pgsql_result

    Method streaming_query

      `object streaming_query(string q, void|mapping(string|int:mixed) bindings)`

    Description

      This is an alias for big_query() , since big_query() already supports streaming of multiple simultaneous queries through the same connection.

    See also

      big_query() , big_typed_query() , Sql.Sql , Sql.sql_result

    Method big_typed_query

      `object big_typed_query(string q, void|mapping(string|int:mixed) bindings)`

    Description

      This function returns an object that allows streaming and typed results.

    See also

      big_query() , Sql.Sql , Sql.sql_result

  CLASS Sql.postgres_result

    Description

      Sql.postgres_result contains the result of a Postgres-query. See Sql.postgres for a description of this program's functions.

    Inherit postgres_result

      inherit Postgres.postgres_result : postgres_result

  CLASS Sql.Sql

    Description

      This class encapsulates a connection to an SQL server. It is a generic interface on top of the DB server specific implementations. That doesn't mean that there aren't plenty of server specific characteristics that still shine through, though.

      This class also serves as an interface guideline for the DB server specific connection classes.

      ## Untyped and typed mode

      The query results are returned in different ways depending on the query functions used: The `..typed_query` functions select typed mode, while the other query functions uses the older untyped mode.

      In untyped mode, all values except SQL NULL are returned as strings in their display representation, and SQL NULL is returned as zero.

      In typed mode, values are returned in pike native form where it works well. That means at least that SQL integer fields are returned as pike integers, floats as floats, SQL NULL as Val.null , and of course strings still as strings. The representation of other SQL types depend on the capabilities of the server specific backends. It's also possible that floats in some cases are represented in other ways if too much precision is lost in the conversion to pike floats.

    Note

      For historical reasons, there may be server specific backends that operate differently from what is described here, e.g. some that return a bit of typed data in untyped mode.

    Note

      Typed operation was not supported at all prior to Pike 7.8.363, and may not be supported for all databases.

    Variable master_sql

      `object Sql.Sql.master_sql`

    Description

      Server specific connection object used for the actual SQL queries.

    Variable case_convert

      `int(0..1) Sql.Sql.case_convert`

    Description

      Convert all field names in mappings to lower_case. Only relevant to databases which only implement big_query(), and use upper/mixed-case fieldnames (eg Oracle).

      `0` | No (default)
      `1` | Yes

    Method quote

      `string quote(string s)`

    Description

      Quote a string s so that it can safely be put in a query.

      All input that is used in SQL-querys should be quoted to prevent SQL injections.

      Consider this harmfull code:

      string my_input = "rob' OR name!='rob"; string my_query = "DELETE FROM tblUsers WHERE name='"+my_input+"'"; my_db->query(my_query);

    This type of problems can be avoided by quoting `my_input`. `my_input` would then probably read something like rob\' OR name!=\'rob

    Usually this is done - not by calling quote explicitly - but through using a sprintf like syntax

        string my_input = "rob' OR name!='rob";
           my_db->query("DELETE FROM tblUsers WHERE name=%s",my_input);

  Method encode_time

    `string encode_time(int t, int|void is_utc)`

  Description

    Converts a system time value to an appropriately formatted time spec for the database.

  Parameter `t`

    Time to encode.

  Parameter `is_utc`

    If nonzero then time is taken as a "full" unix time spec (where the date part is ignored), otherwise it's converted as a seconds-since-midnight value.

  Method decode_time

    `int decode_time(string t, int|void want_utc)`

  Description

    Converts a database time spec to a system time value.

  Parameter `t`

    Time spec to decode.

  Parameter `want_utc`

    Take the date part from this system time value. If zero, a seconds-since-midnight value is returned.

  Method encode_date

    `string encode_date(int t)`

  Description

    Converts a system time value to an appropriately formatted date-only spec for the database.

  Parameter `t`

    Time to encode.

  Method decode_date

    `int decode_date(string d)`

  Description

    Converts a database date-only spec to a system time value.

  Parameter `d`

    Date spec to decode.

  Method encode_datetime

    `string encode_datetime(int t)`

  Description

    Converts a system time value to an appropriately formatted date and time spec for the database.

  Parameter `t`

    Time to encode.

  Method decode_datetime

    `int decode_datetime(string datetime)`

  Description

    Converts a database date and time spec to a system time value.

  Parameter `datetime`

    Date and time spec to decode.

  Method create

    `void Sql.Sql(string host)`
    `void Sql.Sql(string host, string db)`
    `void Sql.Sql(string host, mapping(string:int|string) options)`
    `void Sql.Sql(string host, string db, string user)`
    `void Sql.Sql(string host, string db, string user, string password)`
    `void Sql.Sql(string host, string db, string user, string password, mapping(string:int|string) options)`
    `void Sql.Sql(object host)`
    `void Sql.Sql(object host, string db)`

  Description

    Create a new generic SQL object.

  Parameter `host`

    `object` | Use this object to access the SQL-database.

    `string`

    Connect to the server specified. The string should be on the format: `dbtype://[user[:password]@]hostname[:port][/database]` Use the dbtype protocol to connect to the database server on the specified host. If the hostname is `""` then the port can be a file name to access through a UNIX-domain socket or similar, e g `"mysql://root@:/tmp/mysql.sock/"`.

    There is a special dbtype `"mysqls"` which works like `"mysql"` but sets the `CLIENT_SSL` option and loads the `/etc/my.cnf` config file to find the SSL parameters. The same function can be achieved using the `"mysql"` dbtype.

    `int(0..0)` | Access through a UNIX-domain socket or similar.

  Parameter `db`

    Select this database.

  Parameter `user`

    User name to access the database as.

  Parameter `password`

    Password to access the database.

  Parameter `options`

    Optional mapping of options. See the SQL-database documentation for the supported options. (eg Mysql.mysql()->create() ).

  Note

    In versions of Pike prior to 7.2 it was possible to leave out the dbtype, but that has been deprecated, since it never worked well.

  Note

    Support for options was added in Pike 7.3.

  Method is_open

    `int is_open()`

  Description

    Returns true if the connection seems to be open.

  Note

    This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.

    For a more reliable check of whether the connection is alive, please use ping() .

  See also

    ping()

  Method ping

    `int ping()`

  Description

    Check whether the connection is alive.

  Returns

    Returns one of the following:

    `0` | Everything ok.
    `1` | The connection reconnected automatically.
    `-1` | The server has gone away, and the connection is dead.

  See also

    is_open()

  Method set_charset

    `void set_charset(string charset)`

  Description

    Changes the charset that the connection uses for queries and returned text strings.

  Parameter `charset`

    The charset to use. The valid values and their meanings depends on the database brand. However, the special value `"unicode"` (if supported) selects a mode where the query and result strings are unencoded (and possibly wide) unicode strings.

  Throws

    An error is thrown if the connection doesn't support the specified charset, or doesn't support charsets being set this way at all.

  Note

    See the `set_charset` functions for each database connection type for further details about the effects on the connection.

  See also

    get_charset , Sql.mysql.set_charset

  Method get_charset

    `string get_charset()`

  Description

    Returns the (database dependent) name of the charset used for (at least) query strings. Returns zero if the connection doesn't support charsets this way (typically means that a call to set_charset will throw an error).

  See also

    set_charset , Sql.mysql.get_charset

  Method error

    `int|string error()`

  Description

    Return last error message.

  Method select_db

    `void select_db(string db)`

  Description

    Select database to access.

  Method compile_query

    `string|object compile_query(string q)`

  Description

    Compiles the query (if possible). Otherwise returns it as is. The resulting object can be used multiple times to the query functions.

  Parameter `q`

    SQL-query to compile.

  See also

    query , typed_query , big_query , big_typed_query , streaming_query , streaming_typed_query

  Variable zero

    `protected ZeroWrapper Sql.Sql.zero`

  Description

    Instance of Zerowrapper used by handle_extraargs() .

  Method handle_extraargs

    `array(string|mapping(string|int:mixed)) handle_extraargs(string query, array(mixed) extraargs)`

  Description

    Handle sprintf -based quoted arguments

  Parameter `query`

    The query as sent to one of the query functions.

  Parameter `extraargs`

    The arguments following the query.

  Returns

    Returns an array with two elements:

    Array
    `string 0` | The query altered to use bindings-syntax.
    `mapping(string|int:mixed) 1` | A bindings mapping.

  Method query

    `array(mapping(string:string)) query(object|string q, mixed ... extraargs)`

  Description

    Sends an SQL query synchronously to the underlying SQL-server and returns the results in untyped mode.

  Parameter `q`

    Query to send to the SQL-server. This can either be a string with the query, or a previously compiled query (see compile_query() ).

  Parameter `extraargs`

    This parameter, if specified, can be in two forms:

    1. A mapping containing bindings of variables used in the query. A variable is identified by a colon (:) followed by a name or number. Each index in the mapping corresponds to one such variable, and the value for that index is substituted (quoted) into the query wherever the variable is used.

    res = query("SELECT foo FROM bar WHERE gazonk=:baz", ([":baz":"value"]));

  Binary values (BLOBs) may need to be placed in multisets.

  - Arguments as you would use in sprintf. They are automatically quoted.

        res = query("select foo from bar where gazonk=%s","value");

Returns

  Returns one of the following on success:

  `array(mapping(string:string))` | The result as an array of mappings indexed on the name of the columns. The values are either strings with the display representations or zero for the SQL NULL value.
  `zero` | The value `0` (zero) if the query didn't return any result (eg `INSERT` or similar).

Throws

  Throws an exception if the query fails.

See also

  typed_query , big_query , streaming_query

Method typed_query

  `array(mapping(string:mixed)) typed_query(object|string q, mixed ... extraargs)`

Description

  Sends an SQL query synchronously to the underlying SQL-server and returns the results in typed mode.

  For the arguments, please see the query() function.

Returns

  Returns one of the following on success:

  `array(mapping(string:mixed))` | The result as an array of mappings indexed on the name of the columns. The values have the appropriate native pike types where they fit the SQL data types - see the class doc for details on typed mode.
  `zero` | The value `0` (zero) if the query didn't return any result (eg `INSERT` or similar).

Note

  Typed mode is not supported by all sql databases. If not supported, an error is thrown.

See also

  query , big_typed_query

Method big_query

  `int|object big_query(object|string q, mixed ... extraargs)`

Description

  Sends an SQL query synchronously to the underlying SQL-server and returns the results in untyped mode.

  For the arguments, please see the query() function.

  The result is returned as an Sql.sql_result object in untyped mode. This allows for having some more info about the result as well as processing the result in a streaming fashion, although the result itself wasn't obtained streamingly from the server. Returns `0` if the query didn't return any result (e.g. `INSERT` or similar).

Note

  Despite the name, this function is not only useful for "big" queries. It typically has less overhead than query also for ones that return only a few rows.

See also

  query , streaming_query

Method big_typed_query

  `int|object big_typed_query(object|string q, mixed ... extraargs)`

Description

  Sends an SQL query synchronously to the underlying SQL-server and returns the results in typed mode.

  For the arguments, please see the query() function.

  The result is returned as an Sql.sql_result object in typed mode. This allows for having some more info about the result as well as processing the result in a streaming fashion, although the result itself wasn't obtained streamingly from the server. Returns `0` if the query didn't return any result (e.g. `INSERT` or similar).

Note

  Typed mode is not supported by all sql databases. If not supported, an error is thrown.

Note

  Despite the name, this function is not only useful for "big" queries. It typically has less overhead than typed_query also for ones that return only a few rows.

See also

  query , typed_query , big_query , streaming_query

Method streaming_query

  `int|object streaming_query(object|string q, mixed ... extraargs)`

Description

  Sends an SQL query synchronously to the underlying SQL-server and returns the results streaming in untyped mode.

  For the arguments, please see the query() function.

  The result is returned as a streaming Sql.sql_result object in untyped mode. This allows for having results larger than the available memory, and returning some more info about the result. Returns `0` if the query didn't return any result (e.g. INSERT or similar). For the other arguments, they are the same as for the query() function.

Note

  Streaming operation is not supported by all sql databases. If not supported, this function will fall back to calling big_query() .

See also

  big_query , streaming_typed_query

Method streaming_typed_query

  `int|object streaming_typed_query(object|string q, mixed ... extraargs)`

Description

  Sends an SQL query synchronously to the underlying SQL-server and returns the results streaming in typed mode.

  For the arguments, please see the query() function.

  The result is returned as a streaming Sql.sql_result object in typed mode. This allows for having results larger than the available memory, and returning some more info about the result. Returns `0` if the query didn't return any result (e.g. INSERT or similar).

Note

  Neither streaming operation nor typed results are supported by all sql databases. If not supported, this function will fall back to calling big_typed_query() .

See also

  streaming_query , big_typed_query

Method create_db

  `void create_db(string db)`

Description

  Create a new database.

Parameter `db`

  Name of database to create.

Method drop_db

  `void drop_db(string db)`

Description

  Drop database

Parameter `db`

  Name of database to drop.

Method shutdown

  `void shutdown()`

Description

  Shutdown a database server.

Method reload

  `void reload()`

Description

  Reload the tables.

Method server_info

  `string server_info()`

Description

  Return info about the current SQL-server.

Method host_info

  `string host_info()`

Description

  Return info about the connection to the SQL-server.

Method list_dbs

  `array(string) list_dbs(string|void wild)`

Description

  List available databases on this SQL-server.

Parameter `wild`

  Optional wildcard to match against.

Method list_tables

  `array(string) list_tables(string|void wild)`

Description

  List tables available in the current database.

Parameter `wild`

  Optional wildcard to match against.

Method list_fields

  `array(mapping(string:mixed)) list_fields(string table, string|void wild)`

Description

  List fields available in the specified table

Parameter `table`

  Table to list the fields of.

Parameter `wild`

  Optional wildcard to match against.

CLASS Sql.Sql.ZeroWrapper

  Description

    Wrapper to handle zero.

  See also

    zero

  Method _sprintf

    `string _sprintf(int fmt, mapping(string:mixed) params)`

  Returns

    Returns the following:

    `"NULL"` | If fmt is `'s'`.
    `"ZeroWrapper()"` | If fmt is `'O'`.

    Otherwise it formats a `0` (zero).

CLASS Sql.Null

  Description

    This class is used to implement the SQL NULL value.

  Note

    There's typically only one object of this class: Sql.NULL , but some databases (eg Oracle) have multiple types of NULL, in which case they all inherit this class.

  Deprecated
  See also

    Val.Null , Val.null , Sql.NULL

  Method encode_json

    `string encode_json()`

  Description

    Defined for use with Standards.JSON.encode , so that it formats NULL as `null`.

CLASS Sql.sqlite

  Description

    Low-level interface to SQLite3 databases.

    This class should typically not be accessed directly, but instead via Sql.Sql() with the scheme `"sqlite://"`.

  Method insert_id

    `int insert_id()`

  Description

    Returns the value of the `ROWID` (aka `OID`, aka `_ROWID_`, or declared `INTEGER PRIMARY KEY`) column for the most recent successful `INSERT` operation, or `0` (zero) if no `INSERT` operations have been performed on the connection yet.

  CLASS Sql.sqlite.ResObj

    Description

      Result object from big_query() .

Module Sql.pgsql_util

  Description

    Some pgsql utility functions

  CLASS Sql.pgsql_util.pgsql_result

    Description

      The result object returned by Sql.pgsql()->big_query() , except for the noted differences it behaves the same as Sql.sql_result .

    See also

      Sql.sql_result , Sql.pgsql , Sql.Sql , Sql.pgsql()->big_query()

    Method status_command_complete

      `string status_command_complete()`

    Description

      Returns the command-complete status for this query.

    See also

      affected_rows()

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method affected_rows

      `int affected_rows()`

    Description

      Returns the number of affected rows by this query.

    See also

      status_command_complete()

    Note

      This function is PostgreSQL-specific, and thus it is not available through the generic SQL-interface.

    Method num_fields

      `int num_fields()`

    See also

      Sql.sql_result()->num_fields()

    Method num_rows

      `int num_rows()`

    See also

      Sql.sql_result()->num_rows()

    Method eof

      `int eof()`

    See also

      Sql.sql_result()->eof()

    Method fetch_fields

      `array(mapping(string:mixed)) fetch_fields()`

    See also

      Sql.sql_result()->fetch_fields()

    Method fetch_row

      `array(mixed) fetch_row()`
      `void fetch_row(string|array(string) copydatasend)`

    Returns

      One result row at a time.

      When using COPY FROM STDOUT, this method returns one row at a time as a single string containing the entire row.

    Parameter `copydatasend`

      When using COPY FROM STDIN, this method accepts a string or an array of strings to be processed by the COPY command; when sending the amount of data sent per call does not have to hit row or column boundaries.

      The COPY FROM STDIN sequence needs to be completed by either explicitly or implicitly destroying the result object, or by passing a zero argument to this method.

    See also

      eof()

Module Sql.sql_util

  Description

    Some SQL utility functions

  Method quote

    `string quote(string s)`

  Description

    Quote a string so that it can safely be put in a query.

  Parameter `s`

    String to quote.

  Method fallback

    `void fallback()`

  Description

    Throw an error in case an unimplemented function is called.

  Method emulate_bindings

    `string emulate_bindings(string query, mapping(string|int:mixed)|void bindings, void|object driver)`

  Description

    Build a raw SQL query, given the cooked query and the variable bindings It's meant to be used as an emulation engine for those drivers not providing such a behaviour directly (i.e. Oracle). The raw query can contain some variables (identified by prefixing a colon to a name or a number (i.e. ":var" or ":2"). They will be replaced by the corresponding value in the mapping.

  Parameter `query`

    The query.

  Parameter `bindings`

    Optional mapping containing the variable bindings. Make sure that no confusion is possible in the query. If necessary, change the variables' names.

  CLASS Sql.sql_util.UnicodeWrapper

    Description

      Result object wrapper performing utf8 decoding of all fields.

    syntax

      `protected object Sql.sql_util.UnicodeWrapper.master_result` `void Sql.sql_util.UnicodeWrapper(object master_result)`

    Method num_rows

      `int num_rows()`

    Description

      Returns the number of rows in the result.

    Method num_fields

      `int num_fields()`

    Description

      Returns the number of fields in the result.

    Method eof

      `int(0..1) eof()`

    Description

      Returns `1` if there are no more rows in the result.

    Variable field_info

      `protected array(int|mapping(string:mixed)) Sql.sql_util.UnicodeWrapper.field_info`

    Description

      Cached fetch_fields() result.

    Method fetch_fields

      `array(int|mapping(string:mixed)) fetch_fields()`

    Description

      Returns Information about the fields in the result.

      The following fields are converted from UTF8 if present:

      `"name" : string` | The name of the field. Always present.
      `"table" : string` | The table the field is from. Not present from all databases.
      `"default" : string` | The default value for the column. Not available from all databases.

    Method seek

      `void seek(int rows)`

    Description

      Skip ahead the specified number of rows.

    Method fetch_row

      `int|array(string) fetch_row()`

    Description

      Fetch the next row from the result.

      All strings in the result are decoded from UTF8.

    Method fetch_json_result

      `int|string fetch_json_result()`

    Description

      JSON is always utf8 default, do nothing.

  CLASS Sql.sql_util.MySQLUnicodeWrapper

    Description

      Result wrapper for MySQL that performs UTF-8 decoding of all nonbinary fields. Useful if the result charset of the connection has been set to UTF-8.

    Note

      There's normally no need to use this class directly. It's used automatically when Mysql.set_unicode_decode_mode is activated.

    Inherit UnicodeWrapper

      inherit UnicodeWrapper : UnicodeWrapper

Module Mysql

  Description

    This module enables access to the Mysql database from within Pike. Use Sql.Sql for general database access.

    Mysql is available from http://www.mysql.com.

  See also

    Mysql.mysql , Mysql.mysql_result , Sql.Sql

  Method client_info

    `string client_info()`

  Description

    Get some information about the Mysql-server client library.

  See also

    mysql()->statistics() , mysql()->server_info() , mysql()->protocol_info() , mysql()->info()

  CLASS Mysql.mysql_result

    Description

      Objects of this class contain the result from Mysql queries.

    See also

      Mysql.mysql , Mysql.mysql->big_query()

    Method create

      `void Mysql.mysql_result(Mysql.mysql connection)`

    Description

      Make a new Mysql.mysql_result object.

    See also

      Mysql.mysql->big_query() , Mysql.mysql->list_dbs() , Mysql.mysql->list_tables() , Mysql.mysql->list_processes() , Mysql.mysql

    Method num_rows

      `int num_rows()`

    Description

      Number of rows in the result.

    See also

      num_fields()

    Method num_fields

      `int num_fields()`

    Description

      Number of fields in the result.

    See also

      num_rows()

    Method field_seek

      `void field_seek(int field_no)`

    Description

      Skip to specified field.

      Places the field cursor at the specified position. This affects which field mysql_result->fetch_field() will return next.

      Fields are numbered starting with 0.

    Note

      This function is usually not enabled. To enable it `SUPPORT_FIELD_SEEK` must be defined when compiling the mysql-module.

    See also

      fetch_field() , fetch_fields()

    Method eof

      `int(0..1) eof()`

    Description

      Sense end of result table.

      Returns `1` when all rows have been read, and `0` (zero) otherwise.

    See also

      fetch_row()

    Method fetch_field

      `int|mapping(string:mixed) fetch_field()`

    Description

      Return specification of the current field.

      Returns a mapping with information about the current field, and advances the field cursor one step. Returns `0` (zero) if there are no more fields.

      The mapping contains the same entries as those returned by Mysql.mysql->list_fields() , except that the entry `"default"` is missing.

    Note

      This function is usually not enabled. To enable it `SUPPORT_FIELD_SEEK` must be defined when compiling the mysql-module.

    See also

      fetch_fields() , field_seek() , Mysql.mysql->list_fields()

    Method fetch_fields

      `array(int|mapping(string:mixed)) fetch_fields()`

    Description

      Get specification of all remaining fields.

      Returns an array with one mapping for every remaining field in the result table.

      The returned data is similar to the data returned by Mysql.mysql->list_fields() , except for that the entry `"default"` is missing.

    Note

      Resets the field cursor to `0` (zero).

      This function always exists even when fetch_field() and field_seek() don't.

    See also

      fetch_field() , field_seek() , Mysql.mysql->list_fields()

    Method seek

      `void seek(int rows)`

    Description

      Skip ahead rows rows.

    Note

      Can only seek forward.

    See also

      fetch_row()

    Method fetch_row

      `int|array(string) fetch_row()`

    Description

      Fetch the next row from the result.

      Returns an array with the contents of the next row in the result. Advances the row cursor to the next now.

      Returns `0` (zero) at the end of the table.

    See also

      seek()

    Method fetch_json_result

      `string fetch_json_result()`

    Description

      Fetch all remaining rows and return them as `JSON`-encoded data.

    See also

      fetch_row()

    FIXME

      Ensure that all fields (including binary fields) are UTF-8 encoded.

  CLASS Mysql.mysql

    Description

      Interface to the Mysql database.

      This class enables access to the Mysql database from within Pike.

      Mysql is available from http://www.mysql.com.

    See also

      Mysql.mysql_result , Sql.Sql

    Method create

      `void Mysql.mysql()`
      `void Mysql.mysql(string host)`
      `void Mysql.mysql(string host, string database)`
      `void Mysql.mysql(string host, string database, string user)`
      `void Mysql.mysql(string host, string database, string user, string password)`
      `void Mysql.mysql(string host, string database, string user, string password, mapping(string:string|int) options)`

    Description

      Connect to a Mysql database.

      To access the Mysql database, you must first connect to it. This is done with this function.

    Parameter `host`

      If you give no argument, or give `""` as host it will connect with a UNIX-domain socket, which can be a big performance gain.

    Parameter `options`

      This optional mapping can contain zero or more of the following parameters:

      `"init_command" : string` | Command to execute on connect.
      `"timeout" : int` | Timeout in seconds.
      `"compress" : int(0..1)` | Enable compressed protocol.
      `"mysql_config_file" : string` | Change config file from `"my.cnf"`.
      `"mysql_group" : string` | Specify additional group to read from config file.
      `"mysql_named_pipe" : int(0..1)` | Use named pipe to connect to server.
      `"mysql_local_infile" : int(0..1)` | Enable use of LOCAL INFILE (security).
      `"mysql_charset_dir" : string` | Change charset directory.
      `"mysql_charset_name" : string` | Set connection charset - see set_charset for details. The default is `"latin1"`. As opposed to set_charset , this way of specifying the connection charset doesn't require MySQL 4.1.0.
      `"unicode_decode_mode" : int` | Enable unicode decode mode for the connection if nonzero. In this mode non-binary string results are automatically converted to (possibly wide) unicode strings. An error is thrown if the server doesn't support this. See set_unicode_decode_mode .
      `"ssl_key" : string` | Path to SSL-key for use in SSL-communication.
      `"ssl_cert" : string` | Path to SSL-cert for use in SSL-communication.
      `"ssl_ca" : string` | Path to SSL-CA for use in SSL-communication.
      `"ssl_capath" : string` | Path to SSL-CAPATH for use in SSL-communication.
      `"ssl_cipher" : string` | FIXME
      `"connect_options" : int` | Options used when connecting to the server. See mysql documentation for more information.

    Note

      Some options may not be implemented. Unimplemented options are silently ignored.

    Note

      To use SSL-connections, set the SSL-parameters correctly. They correspond to the parameters given to the mysql-client with the same name so make sure that the mysql-client works with SSL and set these parameters to the same values and everything should work. If SSL-options are loaded from a config-file, one may set the connect_options to include CLIENT_SSL.

    Note

      If Pike has been built with an old MySQL client lib then it might not be possible to specify some charsets that the server supports with the `"mysql_charset_name"` option. In such cases it's possible that set_charset works better (provided the server is 4.1 or newer).

    Method _sprintf

      `string _sprintf(int type, void|mapping flags)`

    Method is_open

      `int is_open()`

    Description

      Returns true if the connection seems to be open.

    Note

      This function only checks that there's an open connection, and that the other end hasn't closed it yet. No data is sent over the connection.

      For a more reliable check of whether the connection is alive, please use ping() .

    See also

      ping()

    Method ping

      `int ping()`

    Description

      Check whether the connection is alive.

    Returns

      Returns one of the following:

      `0` | Everything ok.
      `1` | The connection reconnected automatically.
      `-1` | The server has gone away, and the connection is dead.

    See also

      is_open()

    Method affected_rows

      `int affected_rows()`

    Description

      Returns the number of rows affected by the last query.

    Method insert_id

      `int insert_id()`

    Description

      Returns the id of the last INSERT query into a table with an AUTO INCREMENT field.

    Method error

      `string error()`

    Description

      Returns a string describing the last error from the Mysql-server.

      Returns `0` (zero) if there was no error.

    Method select_db

      `void select_db(string database)`

    Description

      Select database.

      The Mysql-server can hold several databases. You select which one you want to access with this function.

    See also

      create() , create_db() , drop_db()

    Method create_db

      `void create_db(string database)`

    Description

      Create a new database

      This function creates a new database named database in the Mysql-server.

    See also

      select_db() , drop_db()

    Method drop_db

      `void drop_db(string database)`

    Description

      Drop a database

      This function drops the database named database from the Mysql-server.

    See also

      create_db() , select_db()

    Method shutdown

      `void shutdown()`

    Description

      Shutdown the Mysql-server

      This function shuts down a running Mysql-server.

    See also

      reload()

    Method reload

      `void reload()`

    Description

      Reload security tables

      This function causes the Mysql-server to reload its access tables.

    See also

      shutdown()

    Method statistics

      `string statistics()`

    Description

      Some Mysql-server statistics

      This function returns some server statistics.

    See also

      server_info() , host_info() , protocol_info()

    Method server_info

      `string server_info()`

    Description

      Get the version number of the Mysql-server.

    See also

      statistics() , host_info() , protocol_info()

    Method host_info

      `string host_info()`

    Description

      Get information about the Mysql-server connection

    See also

      statistics() , server_info() , protocol_info()

    Method protocol_info

      `int protocol_info()`

    Description

      Give the Mysql protocol version

      This function returns the version number of the protocol the Mysql-server uses.

    See also

      statistics() , server_info() , host_info()

    Method list_dbs

      `Mysql.mysql_result list_dbs()`
      `Mysql.mysql_result list_dbs(string wild)`

    Description

      List databases

      Returns a table containing the names of all databases in the Mysql-server. If the argument wild is specified, only those matching it will be returned.

    See also

      list_tables() , list_fields() , list_processes() , Mysql.mysql_result

    Method list_tables

      `Mysql.mysql_result list_tables()`
      `Mysql.mysql_result list_tables(string wild)`

    Description

      List tables in the current database

      Returns a table containing the names of all tables in the current database. If the argument wild is given, only those matching it will be returned.

    See also

      list_dbs() , list_fields() , list_processes() , Mysql.mysql_result

    Method list_fields

      `array(int|mapping(string:mixed)) list_fields(string table)`
      `array(int|mapping(string:mixed)) list_fields(string table, string wild)`

    Description

      List all fields.

      Returns an array of mappings with information about the fields in the table named table . If the argument wild is given, only those fields matching it will be returned

      The mappings contain the following entries:

      `"name" : string` | The name of the field.
      `"table" : string` | The name of the table.
      `"default" : string` | The default value for the field.
      `"type" : string` | The SQL type of the field.
      `"length" : int` | The length of the longest possible value that can be stored in the field. Note that this measures the display length in string form.
      `"flags" : multiset(string)` | Some flags.
      `decimals : int` | The number of decimalplaces.

      The type of the field can be any of: `"decimal"`, `"char"`, `"short"`, `"long"`, `"float"`, `"double"`, `"null"`, `"time"`, `"longlong"`, `"int24"`, `"tiny blob"`, `"medium blob"`, `"long blob"`, `"var string"`, `"string"` or `"unknown"`.

      The flags multiset can contain any of:

      `"primary_key"` | This field is part of the primary key for this table.
      `"not_null"` | This field may not be NULL.
      `"blob"` | This field is a blob field.

    Note

      Michael Widenius recomends use of the following query instead: `show fields in 'table' like "wild"`.

    See also

      list_dbs() , list_tables() , list_processes() , Mysql.mysql_result.fetch_field()

    Method list_processes

      `Mysql.mysql_result list_processes()`

    Description

      List all processes in the Mysql-server

      Returns a table containing the names of all processes in the Mysql-server.

    See also

      list_dbs() , list_tables() , list_fields() , Mysql.mysql_result

    Method binary_data

      `int binary_data()`

    Description

      Inform if this version of Mysql.mysql supports binary data

      This function returns non-zero if binary data can be reliably stored and retreived with this version of the mysql-module.

      Usually, there is no problem storing binary data in mysql-tables, but data containing `'\0'` (NUL) couldn't be fetched with old versions (prior to 3.20.5) of the mysql-library.

Module Postgres

  CLASS Postgres.postgres

    Description

      This is an interface to the Postgres database server using libpq. This module may or may not be available on your Pike, depending whether the appropriate include and library files could be found at compile-time. Note that you do not need to have a Postgres server running on your host to use this module: you can connect to the database over a TCP/IP socket.

      Please notice that unless you wish to specifically connect to a Postgres server, you'd better use the Sql.Sql , which is a server-independent sql-server-class. The interfaces to all existing sql-classes are consistent. Using Sql.Sql ensures that your Pike applications will run with any supported SQL server without changing a single line of code, at least for most common (and simple) operations.

      The program Postgres.postgres provides the raw interface to the database. Many functions are not available for this program. Therefore, its use is DEPRECATED. It is included in this documentation only for completeness' sake. Use Sql.postgres instead, or even better Sql.Sql

    Note

      This driver is based on libpq and is DEPRECATED. There is a newer driver called Sql.pgsql which is faster and more robust than this driver and does not depend on any libraries.

    Note

      There is no testsuite for this module, since to test anything would require a working Postgres server. You can try to use the included scripts in the "pike/src/modules/Postgres/extras" directory but you'll probably have to patch them to reflect your site's settings.

      The behavior of the Postgres C API also depends on certain environment variables defined in the environment of the pike interpreter.

      `"PGHOST"` | Sets the name of the default host to connect to. It defaults to "localhost"
      `"PGOPTIONS"` | Sets some extra flags for the frontend-backend connection. DO NOT SET unless you're sure of what you're doing.
      `"PGPORT"` | Sets the default port to connect to, otherwise it will use compile-time defaults (that is: the time you compiled the postgres library, not the Pike driver).
      `"PGTTY"` | Sets the file to be used for Postgres frontend debugging. Do not use, unless you're sure of what you're doing.
      `"PGDATABASE"` | Sets the default database to connect to.
      `"PGREALM"` | Sets the default realm for Kerberos authentication. I never used this, so I can't help you.

      Refer to the libpq documentation for further details.

    See also

      Sql.Sql , Sql.postgres , Sql.postgres_result

    Variable version

      `string Postgres.postgres.version`

    Description

      Should you need to report a bug to the author, please submit along with the report the driver version number, as returned by this call.

    Method create

      `void Postgres.postgres()`
      `void Postgres.postgres(string host, void|string database, void|int port)`

    Description

      With no arguments, this function initializes (reinitializes if a connection had been previously set up) a connection to the Postgres backend. Since Postgres requires a database to be selected, it will try to connect to the default database. The connection may fail however for a variety of reasons, in this case the most likely of all is because you don't have enough authority to connect to that database. So use of this particular syntax is discouraged.

      The host argument allows you to connect to databases residing on different hosts. If it is 0 or "", it will try to connect to localhost.

      The database argument specifies the database to connect to. If 0 or "", it will try to connect to the default database.

    Note

      You need to have a database selected before using the sql-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible by you.

      You don't need bothering about syncronizing the connection to the database: it is automatically closed (and the database is sync-ed) when the object is destroyed.

    See also

      Sql.postgres , Sql.Sql , select_db

    Method select_db

      `void select_db(string dbname)`

    Description

      This function allows you to connect to a database. Due to restrictions of the Postgres frontend-backend protocol, you always have to be connected to a database, so in fact this function just allows you to connect to a different database on the same server.

    Note

      This function can raise exceptions if something goes wrong (backend process not running, not enough permissions..)

    See also

      create

    Method big_query

      `Sql.postgres_result big_query(string sqlquery)`

    Description

      This is the only provided interface which allows you to query the database. If you wish to use the simpler "query" function, you need to use the Sql.Sql generic sql-object.

      It returns a postgres_result object (which conforms to the Sql.sql_result standard interface for accessing data). I recommend using query() for simpler queries (because it is easier to handle, but stores all the result in memory), and big_query for queries you expect to return huge amounts of data (it's harder to handle, but fectches results on demand).

    Note

      This function can raise exceptions.

      The program Sql.postgres_result is a superset of the raw Postgres.postgres_result which does not do chunking to avoid excessive memory usage in case of large resultsets.

    See also

      Sql.Sql , Sql.sql_result

    Method error

      `string error()`

    Description

      This function returns the textual description of the last server-related error. Returns 0 if no error has occurred yet. It is not cleared upon reading (can be invoked multiple times, will return the same result until a new error occurs).

    See also

      big_query

    Method reset

      `void reset()`

    Description

      This function resets the connection to the backend. Can be used for a variety of reasons, for example to detect the status of a connection.

    Note

      This function is Postgres-specific, and thus it is not available through the generic SQL-interface.

    Method _set_notify_callback

      `void _set_notify_callback()`
      `void _set_notify_callback(function f)`

    Description

      With Postgres you can associate events and notifications to tables. This function allows you to detect and handle such events.

      With no arguments, resets and removes any callback you might have put previously, and any polling cycle.

      With one argument, sets the notification callback (there can be only one for each sqlobject).

      The callback function must return no value, and takes a string argument, which will be the name of the table on which the notification event has occurred. In future versions, support for user-specified arguments will be added.

    Note

      The Sql.postgres program adds support for automatic delivery of messages (see it for explanation on the inner workings of this feature).

      This function is Postgres-specific, and thus it is not available through the generic SQL-interface

    See also

      Sql.postgres

    Method host_info

      `string host_info()`

    Description

      This function returns a string describing what host are we talking to, and how (TCP/IP or UNIX sockets).

    Method affected_rows

      `int affected_rows()`

    Description

      This function returns the number of rows affected by the last query.

    Method _quote

      `string _quote(string s)`

    Description

      Escape a string to prevent SQL injection, using the current connection's character encoding settings.

  CLASS Postgres.postgres_result

    Description

      Contains the result of a Postgres-query.

    See also

      Sql.postgres, Postgres.postgres, Sql.Sql, Sql.sql_result

    Method create

      `void Postgres.postgres_result(object o)`

    Description

      You can't create istances of this object yourself. The only way to create it is via a big_query to a Postgres database.

    Method num_rows

      `int num_rows()`

    Description

      Returns the number of rows in the result.

    Method num_fields

      `int num_fields()`

    Description

      Returns the number of fields in the result.

    Method fetch_fields

      `array(mapping(string:mixed)) fetch_fields()`

    Description

      Returns an array with an entry for each field, each entry is a mapping with the following fields:

      `"name" : string` | Name of the column
      `"type" : int` | The type ID of the field. This is the database's internal representation type ID.
      `"length" : int|string` | Can be an integer (the size of the contents in bytes) or the word "variable".

    Note

      For char() fields, length is to be intended as the MAXIMUM length of the field. This is not part of the interface specifications in fact, but a driver-choice. In fact char() fields are for Postgres _FIXED_ length fields, and are space-padded. If CUT_TRAILING_SPACES is defined when the driver is compiled (default behavior) it will cut such spaces.

    Method seek

      `void seek()`

    Description

      Moves the result cursor (ahead or backwards) the specified number of rows. Notice that when you fetch a row, the cursor is automatically moved forward one slot.

    Method fetch_row

      `array(string) fetch_row()`

    Description

      Returns an array with the contents of the next row in the result. Advances the row cursor to the next row. Returns 0 at end of table.

    Bugs

      Since there's no generic way to know whether a type is numeric or not in Postgres, all results are returned as strings. You can typecast them in Pike to get the numeric value.

    See also

      seek()

Module Gdbm

  CLASS Gdbm.gdbm

    Method create

      `void Gdbm.gdbm(void|string file, void|string mode)`

    Description

      Without arguments, this function does nothing. With one argument it opens the given file as a gdbm database, if this fails for some reason, an error will be generated. If a second argument is present, it specifies how to open the database using one or more of the follow flags in a string:

      `r` | Open database for reading
      `w` | Open database for writing
      `c` | Create database if it does not exist
      `t` | Overwrite existing database
      `f` | Fast mode
      `s` | Synchronous mode
      `l` | Locking mode

      The fast mode prevents the database from syncronizing each change in the database immediately. This is dangerous because the database can be left in an unusable state if Pike is terminated abnormally.

      The default mode is `"rwc"`.

    Note

      The gdbm manual states that it is important that the database is closed properly. Unfortunately this will not be the case if Pike calls exit() or returns from main(). You should therefore make sure you call close or destruct your gdbm objects when exiting your program. This will probably be done automatically in the future.

    Method fetch
    Method `[]

      `string fetch(string key)`
      `string `[](string key)`

    Description

      Return the data associated with the key 'key' in the database. If there was no such key in the database, zero is returned.

    Method delete

      `int(0..1) delete(string key)`

    Description

      Remove a key from the database. Returns 1 if successful, otherwise 0, e.g. when the item is not present or the database is read only.

    Method firstkey

      `string firstkey()`

    Description

      Return the first key in the database, this can be any key in the database.

    Method nextkey

      `string nextkey(string key)`

    Description

      This returns the key in database that follows the key 'key' key. This is of course used to iterate over all keys in the database.

    Example

          // Write the contents of the database
            for(key=gdbm->firstkey(); k; k=gdbm->nextkey(k))
              write(k+":"+gdbm->fetch(k)+"\n");

    Method `[]=

      `string `[]=(string key, string data)`

    Description

      Associate the contents of 'data' with the key 'key'. If the key 'key' already exists in the database the data for that key will be replaced. If it does not exist it will be added. An error will be generated if the database was not open for writing.

    Example

          gdbm[key] = data;

    Returns

      Returns data on success.

    See also

      store()

    Method store

      `int store(string key, string data)`

    Description

      Associate the contents of 'data' with the key 'key'. If the key 'key' already exists in the database the data for that key will be replaced. If it does not exist it will be added. An error will be generated if the database was not open for writing.

    Example

          gdbm->store(key, data);

    Returns

      Returns `1` on success.

    Note

      Note that the returned value differs from that of `[]=() .

    See also

      `[]=()

    Method reorganize

      `int reorganize()`

    Description

      Deletions and insertions into the database can cause fragmentation which will make the database bigger. This routine reorganizes the contents to get rid of fragmentation. Note however that this function can take a LOT of time to run.

    Method sync

      `void sync()`

    Description

      When opening the database with the 'f' flag writings to the database can be cached in memory for a long time. Calling sync will write all such caches to disk and not return until everything is stored on the disk.

    Method close

      `void close()`

    Description

      Closes the database.