DBD::cubrid - CUBRID driver for the Perl5 Database Interface (DBI)
use DBI; $dsn = "DBI:cubrid:database=$database;host=$hostname;port=$port"; $dsn = "DBI:cubrid:database=$database;host=$hostname;port=$port;autocommit=$autocommit"; $dbh = DBI->connect ($dsn, $user, $password); $sth = $dbh->prepare("SELECT * FROM TABLE"); $sth->execute; $numFields = $sth->{'NUM_OF_FIELDS'}; $sth->finish; $dbh->disconnect;
#!/usr/bin/perl use strict; use DBI; # Connect to the database. my $dbh = DBI->connect ( "DBI:cubrid:database=testdb;host=localhost;port=33000", "public", "", {RaiseError => 1, AutoCommit => 0}); # Drop table 'foo'. This may fail, if 'foo' doesn't exist. # Thus we put an eval around it. eval { $dbh->do("DROP TABLE foo") }; print "Dropping foo failed: $@\n" if $@; # Create a new table 'foo'. This must not fail, thus we don't # catch errors. $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))"); # INSERT some data into 'foo'. $dbh->do("INSERT INTO foo VALUES (1, "Tim"); # Same thing, but using placeholders $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen"); # Now retrieve data from the table. my $sth = $dbh->prepare("SELECT * FROM foo"); $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) { print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n"; } $sth->finish(); # Disconnect from the database. $dbh->disconnect();
DBD::cubrid is a Perl module that works with the DBI module to provide access to CUBRID databases.
This documentation describes driver specific behavior and restrictions. It is not supposed to be used as the only reference for the user. In any case consult the DBI documentation first!
use DBI; $dsn = "DBI:cubrid:database=$database"; $dsn = "DBI:cubrid:database=$database;host=$hostname"; $dsn = "DBI:cubrid:database=$database;host=$hostname;port=$port"; $dsn = "DBI:cubrid:database=$database;host=$hostname;port=$port;autocommit=$autocommit" $dbh = DBI->connect ($dsn, $user, $password);
This method creates a database handle by connecting to a database, and is the DBI equivalent of the "new" method. You can also use "dbi:cubrid..." in $dsn. There are some properties you can configure when create the conntion.
If the HA feature is enabled, you munst specify the connection information of the standby server, which is used for failover when failure occurs, in the url string argument of this function.
althosts=standby_broker1_host,standby_broker2_host, . . . : String. Specifies the broker information of the standby server, which is used for failover when it is impossible to connect to the active server. You can specify multiple brokers for failover, and the connection to the brokers is attempted in the order listed in alhosts.
rctime : INT. An interval between the attempts to connect to the active broker in which failure occurred. After a failure occurs, the system connects to the broker specified by althosts (failover), terminates the transaction, and then attempts to connect to he active broker of the master database at every rctime. The default value is 600 seconds.
autocommit : String. Configures the auto-commit mode. The value maybe true, on, yes, false, off and no. You can set autocommit property here or in the \%attr parameter. If you set this property at both places, the latter is effective.
\%attr
login_timeout : INT. Configures the login timeout of CUBRID.
query_timeout: INT. Configures the query timeout of CUBRID.
disconnect_on_query_timeout : String. Make the query_timeout effective. The value maybe true, on, yes, false, off and no.
The following are some examples about different $dsn:
$db = "testdb"; $host= "192.168.10.29"; $port = 33088; $dsn = "dbi:cubrid:database=$db;host=$host;port=$port"; # connection $dsn string when property(alhosts) specified for HA $alhosts = "192.168.10.28:31000,192.168.10.27:33055"; $dsn = "dbi:cubrid:database=$db;host=$host;port=$port;alhosts=$alhosts"; # connection $dsn string when property(alhosts, rctime) specified for HA $rctime = 1000; $dsn = "dbi:cubrid:database=$db;host=$host;port=$port;alhosts=$alhosts;rctime=$rctime"; $query = 600; $dsn = "dbi:cubrid:database=$db;host=$host;port=$port;query_timeout=$query;disconnect_on_query_timeout=yes";
$rows = $dbh->do ($statement) or die $dbh->errstr; $rows = $dbh->do ($statement, \%attr) or die $dbh->errstr; $rows = $dbh->do ($statement, \%attr, @bind_values);
Prepare and execute a single statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. For example:
$dbh->do ("CREATE TABLE test_cubrid (id INT, name varchar(60))"); $dbh->do ("INSERT INTO test_cubrid VALUES (1, 'Jobs')") $dbh->do ("INSERT INTO test_cubrid VALUES (?, ?)", undef, (2, "Gate"));
$sth = $dbh->prepare ($statement, \%attr);
Prepares a statement for later execution by the database engine and returns a reference to a statement handle object. You can get $sth->{NUM_OF_PARAMS} after the statement prepared.
$sth->{NUM_OF_PARAMS}
$dbh->commit or die $dbh->errstr;
Issues a COMMIT to the server, indicating that the current transaction is finished and that all changes made will be visible to other processes. If AutoCommit is enabled, then a warning is given and no COMMIT is issued. Returns true on success, false on error.
$dbh->rollback or dir $dbh->errstr;
Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and false on error.
$dbh->disconnect or warn $dbh->errstr;
Disconnects from the CUBRID database. If the AutoCommit is on, any uncommitted changes will be committed before disconnect is called. Otherwise, it will roll back.
AutoCommit
$ary_ref = $dbh->selectall_arrayref ($statement); $ary_ref = $dbh->selectall_arrayref ($statement, \%attr); $ary_ref = $dbh->selectall_arrayref ($statement, \%attr, @bind_values);
Returns a reference to an array containing the rows returned by preparing and executing the SQL string. See the DBI documentation for full details.
$hash_ref = $dbh->selectall_hashref ($statement, $key_field);
Returns a reference to a hash containing the rows returned by preparing and executing the SQL string. See the DBI documentation for full details.
$ary_ref = $dbh->selectcol_arrayref ($statement, \%attr, @bind_values);
Returns a reference to an array containing the first column from each rows returned by preparing and executing the SQL string. It is possible to specify exactly which columns to return. See the DBI documentation for full details.
@row_ary = $dbh->selectrow_array ($statement); @row_ary = $dbh->selectrow_array ($statement, \%attr); @row_ary = $dbh->selectrow_array ($statement, \%attr, @bind_values);
Returns an array of row information after preparing and executing the provided SQL string. The rows are returned by calling "fetchrow_array". The string can also be a statement handle generated by a previous prepare. Note that only the first row of data is returned. If called in a scalar context, only the first column of the first row is returned. Because this is not portable, it is not recommended that you use this method in that way.
@row_ary = $dbh->selectrow_arrayref ($statement); @row_ary = $dbh->selectrow_arrayref ($statement, \%attr); @row_ary = $dbh->selectrow_arrayref ($statement, \%attr, @bind_values);
Exactly the same as "selectrow_array", except that it returns a reference to an array, by internal use of the "fetchrow_arrayref" method.
$hash_ref = $dbh->selectrow_hashref ($statement); $hash_ref = $dbh->selectrow_hashref ($statement, \%attr); $hash_ref = $dbh->selectrow_hashref ($statement, \%attr, @bind_values);
Exactly the same as "selectrow_array", except that it returns a reference to an hash, by internal use of the "fetchrow_hashref" method.
$insert_id = $dbh->last_insert_id ($catalog, $schema, $table, $field); $insert_id = $dbh->last_insert_id ($catalog, $schema, $table, $field, \%attr);
Attempts to retrieve the ID generated for the AUTO_INCREMENT column which is updated by the previous INSERT query. This method only be available immediately after the insert statement has executed for CUBRID. And CUBRID will also ignore the $catalog, $schema, $table and $field parameters. CUBRID will return NULL if no last insert id. For example:
$insert_id = $dbh->last_insert_id (undef, undef, undef, undef);
$rv = $dbh->ping;
This method is used to check the validity of a database handle. The value returned is either 0, indicating that the connection is no longer valid, or 1, indicating the connection is valid.
$value = $dbh->get_info ($info_type);
DBD::cubrid supports get_info(), bug (currently) only a few info types.
get_info()
$sth = $dbh->table_info ($catalog, $schema, $table, $type); #then $sth->fetchall_arrayref or $sth->fetchall_hashref etc
DBD::cubrid supports attributes for table_info().
table_info()
In CUBRID, this method will return all tables and views visible to the current user. CUBRID doesn't support catalog and schema now. The table argument will do a LIKE search if a percent sign (%) or an underscore (_) is detected in the argment. The type argument accepts a value of either "TABLE" or "VIEW" (using both is the defualt action). Note that a statement handle is returned, not a direct list of table. See the examples below for ways to handle this.
The following fields are returned:
TABLE_CAT: Always NULL, as CUBRID doesn't have the concept of catalogs.
TABLE_SCHEM: Always NULL, as CUBRID doesn't have the concept of schemas.
TABLE_NAME: Name of the table (or view, synonym, etc).
TABLE_TYPE: The type of object returned. It will be "TABLE" or "VIEW".
REMARKS: A description of the table. Always NULL (undef).
Examples of use:
# Display all tables and views to the current user $sth = $dbh->table_info (undef, undef, undef, undef); for my $info (@{$sth->fetchall_arrayref({})}) { print "\$info->{TABLE_NAME} = $info->{TABLE_NAME} \t \$info->{TABLE_TYPE} = $info->{TABLE_TYPE}\n"; } # Display the specified table $sth = $dbh->table (undef, undef, 'test_%', undef); for my $info (@{$sth->fetchall_arrayref({})}) { print "\$info->{TABLE_NAME} = $info->{TABLE_NAME} \t \$info->{TABLE_TYPE} = $info->{TABLE_TYPE}\n"; }
@names = $dbh->tables ($catalog, $schema, $table, $type);
Simple interface to "table_info". Returns a list of matching table names. See more information in DBI document;
$sth = $dbh->primary_key_info ($catalog, $schema, $table); # then $sth->fetchall_arrayref or $sth->fetchall_hashref etc
CUBRID does not support catalogues and schemas so TABLE_CAT and TABLE_SCHEM are ignored as selection criterion. The TABLE_CAT and TABLE_SCHEM fields of a fetched row is always NULL (undef).
The result set is ordered by TABLE_NAME, COLUMN_NAME, KEY_SEQ, and PK_NAME.
@key_column_names = $dbh->primary_key ($catalog, $schema, $table);
Simple interface to the "primary_key_info" method. Returns a list of the column names that comprise the primary key of the specified table. The list is in primary key column sequence order. If there is no primary key then an empty list is returned.
$sth = $dbh->foreign_key_info ($pk_catalog, $pk_schema, $pk_table, $fk_catalog, $fk_schema, $fk_table); # then $sth->fetchall_arrayref or $sth->fetchall_hashref etc
CUBRID does not support catalogues and schemas so $pk_catalog, $pk_schema, $fk_catalog and $fk_schema are ignored as selection criteria. The PKTABLE_CAT PKTABLE_SCHEM FKTABLE_CAT and FKTABLE_SCHEM fields of a fetched row are are always NULL (undef).
$pk_catalog
$pk_schema
$fk_catalog
$fk_schema
The DEFERABILITY field is always NULL, CUBRID does not support it now.
The result set is ordered by PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME.
$sth = $dbh->column_info ($catalog, $schema, $table, $column); # then $sth->fetchall_arrayref or $sth->fetchall_hashref etc
CUBRID does not support catalogues and schemas so TABLE_CAT and TABLE_SCHEM are ignored as selection criterion. The TABLE_CAT and TABLE_SCHEM fields of a fetched row is always NULL(undef).
CUBRID only support the field of TABLE_NAME, COLUMN_NAME, NULLABLE, IS_NULLABLE, TYPE_NAME, COLUMN_DEF, ORDINAL_POSITION, DATA_TYPE, COLUMN_SIZE, NUM_PREC_RADIX, DECIMAL_DIGITS and SQL_DATA_TYPE now.
$type_info_all = $dbh->type_info_all;
Returns a reference to an array which holds information about each data type variant supported by the database and driver. The array and its contents are treated as read-only.
@type_info = $dbh->type_info ($data_type);
Returns a list of hash references holding information about one or more variants of $data_type. See the DBI documentation for more details.
In order to use the private database handle methods, you must make sure the name prefix 'cubrid_' is associated with a registered driver. If not, you must manually add it. To do this, you should install DBI by yourself. First, download the source code of DBI and unpack it. Second, open DBI.pm with your favourite text editor, find the definition of $dbd_prefix_registry. Third, add the line
cubrid_ => { class => 'DBD::cubrid', },
save and exit. Then you can execute
perl Makefile.PL make make install
After doing these, you can use the private database handle methods.
$sth->cubrid_lob_get ($column);
This method can get a column of the lob object from CUBRID database. You need to point out which column you want to fetch as lob object and the column start with 1.
$sth->cubrid_lob_export ($row, $filename);
This method will export a lob object as a file. You must specify the row you want to fetch and the name of the file you want to export, and the row start with 1. Attention, before use this function, you need to call cubrid_lob_get first. For example
$sth = $dbh->prepare ("SELECT * from test_lob"); $sth->execute; $sth->cubrid_lob_get (2); # fetch the second column $sth->cubrid_lob_export (3, "1.jpg"); # export the third row as "1.jpg" $sth->cubrid_lob_close();
$sth->cubrid_lob_import ($index, $filename, $type);
This method will import a file in CUBRID database. The parameter $index is indicated to which placeholder you want to bind the file in $filename, and $type can SQL_BLOB or SQL_CLOB. For example
$sth = $dbh->prepare ("INSERT INTO test_lob VALUES (?, ?)"); $sth->bind_param (1, 1); $sth->cubrid_lob_import (2, "1.jpg", DBI::SQL_BLOB); $sth->execute;
$sth->cubrid_lob_close ();
This method will close the lob object that cubrid_lob_get gets. Once you use cubrid_lob_get, you'd better use this method when you don't use the lob object any more.
Supported by DBD::cubrid as proposed by DBI. In CUBRID 8.4.0, the default of AutoCommit is OFF. And in CUBRID 8.4.1, the default of Autocommit is ON. It is highly recommended that you explicitly set it when calling "connect".
Returns the name of the current database.
$sth->bind_param ($index, $bind_value); $sth->bind_param ($index, $bind_value, $bind_type);
Allows the user to bind a value and/or a data type to a placeholder. The value of $index is a number of using the '?' style placeholder. Generally, you can bind params without specifying the data type. CUBRID will match it automatically. That means, you don't use $bind_type for most data types in CUBRID. But it won't work well with some special data types, such as BLOB and CLOB. The following are data types supported by CUBRID.
$index
$bind_type
----------------------------------------- | CUBRID | sql_types | ----------------------------------------- | CHAR | SQL_CHAR | | VARCHAR | SQL_VARCHAR | | NUMERIC | SQL_NUMERIC | | DECIMAL | SQL_DECIMAL | | INTEGER | SQL_INTEGER | | SMALLINT | SQL_SMALLINT | | REAL | SQL_REAL | | FLOAT | SQL_FLOAT | | DOUBLE | SQL_DOUBLE | | DATE | SQL_TYPE_DATE | | TIME | SQL_TYPE_TIME | | TIMESTAMP | SQL_TYPE_TIMESTAMP | | BIGINT | SQL_BIGINT | | DATETIME | SQL_TYPE_TIMESTAMP | | ENUM | SQL_VARCHAR | ----------------------------------------- | BLOB | SQL_BLOB | | CLOB | SQL_CLOB | -----------------------------------------
Note that, DBD:cubrid does not support BIT, SET, MULTISET and SEQUENCE now. And if you want to bind BLOB/CLOB data, you must specify $bind_type.
# CREATE TABLE test_cubrid (id INT, name varchar(50), birthday DATE, salary FLOAT) $sth = $dbh->prepare ("INSERT INTO test_cubrid VALUES (?, ?, ?, ?)"); $sth->bind_param (1, 1); $sth->bind_param (2, 'Jobs'); $sth->bind_param (3, '1979-10-1'); $sth->bind_param (4, 1000.5); $sth->execute; # CREATE TABLE test_cubrid (id INT, paper CLOB); $sth = $dbh->prepare ("INSERT INTO test_cubrid VALUES (?, ?)"); $sth->bind_param (1, 10); $sth->bind_param (2, "HELLO WORLD", DBI::SQL_CLOB); $sth->execute;
$rv = $sth->execute; $rv = $sth->execute (@bind_values);
Executes a previously prepared statement. In addition to UPDATE, DELETE, INSERT statements, for which it returns always the number of affected rows and to SELECT statements, it returns the number of rows thart will be returned by the query.
$ary_ref = $sth->fetchrow_arrayref; $ary_ref = $sth->fetch;
Fetches the next row of data from the statement handle, and returns a reference to an array holding the column values. Any columns that are NULL are returned as undef within the array.
If there are no more rows or if an error occurs, the this method return undef. You should check $sth-err> afterwords (or use the RaiseError attribute) to discover if the undef was due to an error.
$sth-
RaiseError
Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Also, the elements of the array are also reused for each row, so take care if you want to take a reference to an element.
@ary = $sth->fetchrow_array;
Similar to the "fetchrow_array" method, but returns a list of column information rather than a reference to a list. Do not use this in a scalar context.
$hash_ref = $sth->fetchrow_hashref; $hash_ref = $sth->fetchrow_hashref ($name);
Fetches the next row of data and returns a hashref containing the name of the columns as the keys and the data itself as the values. Any NULL value is returned as as undef value.
If there are no more rows or if an error occurs, the this method return undef. You should check $sth-err> afterwords (or use the RaiseError attribute) to discover if the undef returned was due to an error.
The optional $name argument should be either NAME, NAME_lc or NAME_uc, and indicates what sort of transformation to make to the keys in the hash.
$name
NAME
NAME_lc
NAME_uc
$tbl_ary_ref = $sth->fetchall_arrayref (); $tbl_ary_ref = $sth->fetchall_arrayref ($slice); $tbl_ary_ref = $sth->fetchall_arrayref ($slice, $max_rows);
Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs, the data read in so far will be returned. Because of this, you should always check $sth-err> after calling this method, unless RaiseError has been enabled.
If $slice is an array reference, fetchall_arrayref uses the "fetchrow_arrayref" method to fetch each row as an array ref. If the $slice array is not empty then it is used as a slice to select individual columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).
$slice
With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
If $slice is a hash reference, fetchall_arrayref uses "fetchrow_hashref" to fetch each row as a hash reference.
See the DBI documentation for a complete discussion.
$hash_ref = $sth->fetchall_hashref ($key_field);
Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for a full discussion.
$rv = $sth->finish;
Indicates to DBI that you are finished with the statement handle and are not going to use it again.
$rv = $sth->rows;
Returns the number of rows returned by the last query. In contrast to many other DBD modules, the number of rows is available immediately after calling $sth-execute>. Note that the "execute" method itself returns the number of rows itself, which means that this method is rarely needed.
Returns the number of columns returned by the current statement. A number will only be returned for SELECT statements, for SHOW statements (which always return 1), and for INSERT, UPDATE, and DELETE statements which contain a RETURNING clause. This method returns undef if called before execute().
execute()
Returns the number of placeholders in the current statement.
Returns an arrayref of column names for the current statement. This method will only work for SELECT statements, for SHOW statements, and for INSERT, UPDATE, and DELETE statements which contain a RETURNING clause. This method returns undef if called before execute().
The same as the NAME attribute, except that all column names are forced to lower case.
The same as the NAME attribute, except that all column names are forced to upper case.
Similar to the NAME attribute, but returns a hashref of column names instead of an arrayref. The names of the columns are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0. This method returns undef if called before execute().
The same as the NAME_hash attribute, except that all column names are forced to lower case.
Returns an arrayref indicating the data type for each column in the statement. This method returns undef if called before execute().
Returns an arrayref of integer values for each column returned by the statement. This method returns undef if called before execute().
Returns an arrayref of integer values for each column returned by the statement. The number indicates if the column is nullable or not. 0 = not nullable, 1 = nullable. This method returns undef if called before execute().
we will have to install the following software componets in order to usd DBD::cubrid.
CUBRID DBMS
Install the latest version of CUBRID Database System, and make sure the Environment Variable %CUBRID% is defined in your system
Perl Interpreter
If you're new to Perl, you should start by running perldoc perlintro , which is a general intro for beginners and provides some background to help you navigate the rest of Perl's extensive documentation. Run perldoc perldoc to learn more things you can do with perldoc.
DBI module
The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.
To fire up the CPAN module, just get to your command line and run this:
perl -MCPAN -e shell
If this is the first time you've run CPAN, it's going to ask you a series of questions - in most cases the default answer is fine. If you finally receive the CPAN prompt, enter
install DBD::cubrid
To build and install from source, you should move into the top-level directory of the dirstribution and issue the following commands.
tar zxvf DBD-cubrid-(version)-tar.gz cd DBD-cubrid-(version) perl Makefile.PL make make test make install
To report a bug, or view the current list of bugs, please visit http://jira.cubrid.org/browse/APIS
To install DBD::cubrid, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::cubrid
CPAN shell
perl -MCPAN -e shell install DBD::cubrid
For more information on module installation, please visit the detailed CPAN module installation guide.