DBE Text Driver

DOCUMENTATION

• NAME
• SYNOPSIS
• EXAMPLE
• DESCRIPTION
• CONNECT
• SQL SYNTAX
• SELECT
• CREATE TABLE
• DROP TABLE
• INSERT
• UPDATE
• DELETE
• SUPPORTED FUNCTIONS
• Aggregate functions
• Numeric functions
• String functions
• Date and time functions
• Other functions
• LIMITATIONS
• AUTHORS
• COPYRIGHT

NAME

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

  [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  

  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 

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

DESCRIPTION

CONNECT

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

• PROVIDER [Text]
  Provider must be "Text".

• DBQ | DEFAULTDIR [path]
  Path to text files. Defaults to the current directory.

• USESCHEMA [1|0|Yes|No|True|False]
  A boolean value to enable oder disable "schema.ini". Defaults to Yes.

• COLNAMEHEADER [1|0|Yes|No|True|False]
  A boolean value to enable or disable column names in first row of the text files. Defaults to Yes.

• QUOTECHAR [char]
  The character for quoting values. Defaults to '"'.

• DEC | DECIMALSYMBOL [char]
  The character for the decimal point. Defaults to '.'.

• FORMAT [CSVDelimited|TABDelimited|Delimited(;)]
  Format definition of Text files. Format CSVDelimited delimits columns by comma, TABDelimited delimits columns by tab and Delimited(;) defines a custom delimiter, where ';' can be replaced with any character except of '"'.

• DELIMITER [char]
  Column delimiter character. Defaults to ';'. Overwrites FORMAT.

• MSR | MAXSCANROWS [num]
  Maximum number of rows to scan for to detect data types of a table. Defaults to 25.

• CHARSET | CHARACTERSET [string]
  Client character set. Supported charsets are ANSI and UTF-8. Default is ANSI.

• EXTENSIONS [string]
  Lists of file name extensions for the Text files on the data source separated by comma. Default is "csv,txt"

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]

DELETE

  DELETE FROM tbl_name
    [WHERE where_condition]

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

AUTHORS

COPYRIGHT