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 case_convert
bool 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
- Variable master_sql
object Sql.Sql.master_sql
- Description
Server specific connection object used for the actual SQL queries.
- Method create
Sql.Sql Sql.Sql(string host)
Sql.Sql Sql.Sql(string host, string db)
Sql.Sql Sql.Sql(string host, mapping(string:int|string) options)
Sql.Sql Sql.Sql(string host, string db, string user)
Sql.Sql Sql.Sql(string host, string db, string user, string password)
Sql.Sql Sql.Sql(string host, string db, string user, string password, mapping(string:int|string) options)
Sql.Sql Sql.Sql(object host)
Sql.Sql 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
Exactly which databases are supported by pike depends on the installed set of client libraries when pike was compiled.
The possible ones are
- mysql
libmysql based mysql connection
- mysqls
libmysql based mysql connection, using SSL
- dsn
ODBC based connection
- msql
- odbc
ODBC based connection
- oracle
Oracle using oracle libraries
- pgsql
PostgreSQL direct network access. This module is independent of any external libraries.
- postgres
PostgreSQL libray access. Uses the Postgres module.
- rsql
Remote SQL api, requires a rsql server running on another host. This is an API that uses sockets to communicate with a remote pike running pike -x rsqld on another host.
- sqlite
In-process SQLite database, uses the SQLite module
- sybase
Uses the sybase module to access sybase
- tds
Sybase and Microsoft SQL direct network access using the TDS protocol. This module is independent of any external libraries.
- Note
Support for options was added in Pike 7.3.