Christian Müller > DBE-0.99_1 > DBE::Driver::TEXT

Download:
DBE-0.99_1.tar.gz

Dependencies

Annotate this POD

Module Version: 0.99.01   Source  

NAME ^

DBE::Driver::TEXT - Text Driver for the Perl Database Express Engine

SYNOPSIS ^

  use DBE;
  
  $con = DBE->connect( 'Provider=Text;DBQ=/path/to/text-files' );
  
  $res = $con->query( 'SELECT * FROM "table.csv"' );
      or
  $stmt = $con->prepare( 'SELECT * FROM "table.csv"' );
  $res = $stmt->execute();
      or
  $con->prepare( 'SELECT * FROM "table.csv"' );
  $res = $con->execute();

EXAMPLE ^

NOTE: This requires a schema.ini file in the same path as the delimited text file. You must change the columns in the schema.ini file to correspond to your delimited file. Also, you must change the path in the connect string to the correct path for your file.

To create a schema.ini file, use a text editor. Below are the schema.ini entries for the sample1.txt file listed below in this example:

schema.ini

  [sample1.txt]
  ColNameHeader = False
  Format = CSVDelimited
  CharacterSet = ANSI
  Col1 = ProductID int
  Col2 = ProductName char width 30
  Col3 = QuantityPerUnit char width 30
  Col4 = UnitPrice double
  Col5 = Discontinued int  

You could copy and paste the following delimited file example into a text editor and save it as sample1.txt. You could then modify the DBQ path, below, to point to the comma delimited file sample1.txt.

sample1.txt

  1,Chai,10 boxes x 20 bags,18.00,0
  2,Chang,24 - 12 oz bottles,19.00,0
  3,Aniseed Syrup,12 - 550 ml bottles,10.00,0
  4,Chef Anton's Cajun Seasoning,48 - 6 oz jars,22.00,0
  5,Chef Anton's Gumbo Mix,36 boxes,21.35,1
  6,Grandma's Boysenberry Spread,12 - 8 oz jars,25.00,0
  7,Uncle Bob's Organic Dried Pears,12 - 1 lb pkgs.,30.00,0 

NOTE: Make sure when you paste it in the text editor that you do not leave any blank lines at the top of the file.

sample1.pl

  use DBE;
  
  $con = DBE->connect(
      'Provider' => 'Text',
      'DBQ' => 'E:\Samples\Text\CommaDelimited',
      'Charset' => 'UTF-8',
  );
  
  $res = $con->query( 'SELECT * FROM "sample1.txt"' );

DESCRIPTION ^

DBE::Driver::Text is a DBE driver for text files. It is almost compatible to the Microsoft Text Driver.

CONNECT

  use DBE;
  
  DBE->connect( 'Provider=Text;DBQ=.' );

The parameters are specified as follows:

SQL SYNTAX ^

SELECT

  SELECT
      select_expr, ...
    FROM table_references
    WHERE where_condition
    [GROUP BY {col_name | expr}, ...]
    [ORDER BY {col_name | expr} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  
  table_references:
    table_reference [, table_reference] ...
  
  table_reference:
    tbl_name [[AS] alias]

CREATE TABLE

  CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
  
  create_definition:
    col_name column_definition
  
  column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
  
  data_type:
      SMALLINT[(length)]
    | INT[(length)]
    | INTEGER[(length)]
    | REAL[(length)]
    | DOUBLE[(length)]
    | FLOAT[(length)]
    | CHAR(length)
    | VARCHAR(length)
    | BINARY(length)
    | VARBINARY(length)
    | BLOB

DROP TABLE

  DROP TABLE [IF EXISTS] tbl_name

INSERT

  INSERT INTO tbl_name [(col_name, ...)]
    VALUES (expr, ...)

UPDATE

  UPDATE tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]

Note: UPDATE creates a temporary table and fills it with the updated data. After then the temporary table will be replaced with the old table.

DELETE

  DELETE FROM tbl_name
    [WHERE where_condition]

Note: DELETE creates a temporary table and fills it with the undeleted data. After then the temporary table will be replaced with the old table.

SUPPORTED FUNCTIONS ^

Aggregate functions

COUNT (expr)
SUM (expr)
AVG (expr)
MIN (expr)
MAX (expr)

Numeric functions

ABS (expr)
ROUND (expr [, prec])

String functions

CONCAT (expr1, expr2, ...)
TRIM ([{LEADING|TRAILING|BOTH}] [expr FROM] expr)
LTRIM (expr)
RTRIM (expr)
LOWER (expr) | LCASE (expr)
UPPER (expr) | UCASE (expr)
LENGTH (expr) | OCTET_LENGTH (expr)
CHAR_LENGTH (expr) | CHARACTER_LENGTH (expr)
LOCATE (substr, str [, start])
POSITION (substr IN str)
SUBSTRING | SUBSTR (expr {,|FROM} index [{,|FOR} count])

Date and time functions

CURRENT_TIMESTAMP | CURRENT_TIMESTAMP () | NOW ()
CURRENT_DATE | CURRENT_DATE ()
CURRENT_TIME | CURRENT_TIME ()

Other functions

CONVERT (expr, type)
CONVERT (expr USING charset)
IF (expr_eval, expr_true, expr_false)

LIMITATIONS ^

Table format FIXEDLENGTH is not supported.

AUTHORS ^

Written by Christian Mueller

COPYRIGHT ^

The DBE::Driver::TEXT module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

syntax highlighting: