DEPRECATION WARNING

This documentation is not using the current rendering mechanism and will be deleted by December 31st, 2020. The extension maintainer should switch to the new system. Details on how to use the rendering mechanism can be found here.

EXT: Table for Connector service SQL

Created:2010-02-18T17:33:18
Changed by:Tomasz Krawczyk
Changed:2013-08-20T12:08:40.390000000
Classification:tk_svconsql_table
Keywords:external database, table, connector, service, sql, adodb, forAdmins, forDevelopers, forAdvanced
Author:Tomasz Krawczyk
Email:tomasz@typo3.pl
Info 4:
Language:en

img-1 img-2 EXT: Table for Connector service SQL - tk_svconsql_table

EXT: Table for Connector service SQL

Extension Key: tk_svconsql_table

Language: en

Keywords: external database, table, connector, service, sql, adodb, forAdmins, forDevelopers, forAdvanced

Copyright 2013, Tomasz Krawczyk, <tomasz@typo3.pl>

This document is published under the Open Content License

available from http://www.opencontent.org/opl.shtml

The content of this document is related to TYPO3

- a GNU/GPL CMS/Framework available from www.typo3.org

Table of Contents

EXT: Table for Connector service SQL 1

`Introduction 3 <#__RefHeading__598_1075316393>`_

What does it do? 3

Features list 3

Requirements 3

`Users manual 4 <#__RefHeading__604_1075316393>`_

Installation 4

Usage 4

`Configuration 6 <#__RefHeading__612_1075316393>`_

Reference 6

`Bug reports 8 <#__RefHeading__783_998949593>`_

Introduction

What does it do?

This extension builds simple HTML table from data taken from an external database. For the connection with other databases extensions Connector SQL service and ADOdb are used.

Features list

  • You can define table class, caption, row and column classes, border, cellpadding and cellspacing.
  • You can hide some of cols.
  • Customizable data formatting. Additionally, you can use hook for extended formating.
  • Supports output caching with Caching Framework.
  • Caption can be taken from the first field of the first row of the SQL query result.
  • Database settings, table settings and caching settings configurable through TypoScript or FlexForm.

Requirements

This extension requires extensions: ADOdb (adodb), Connector Services (svconnector), Connector service - SQL (svconnector_sql) to be installed.

Users manual

Installation

  • Download and install with Extension Manager.
  • Include extension static template - “Table for Connector service SQL (tk_svconsql_table)”.
  • Configure plugin TypoScript settings or configure settings on a page by the FlexForm. (The FlexForm settings are overwriting TypoScript settings).

If you are using TYPO3 v4.5 then you have to turn on Caching Framework in your localconf.php by adding:

$TYPO3_CONF_VARS['SYS']['useCachingFramework'] = true;

Usage

  • In the Page module add General Plugin.
  • Go to the Plugin sheet and select plugin: Table for Connector SQL.
  • Go again to the Plugin sheet and fill plugin options.
Database sheet

All fields on this sheet must be filled except field “init”.

img-3

Fetch mode field may have “Both” value. This extension doesn't work good if both values are send. In case of “mysql” database driver you may see repeated table balues. In general you have to test how it works with your db driver.

Table sheet

On this sheet you can set some table parameters. Most fields are self explanatory.

  • Column names list - coma delimited list. Column names used in table header.
  • Column formats list – coma delimited list. List of column data formats. Available formats are: string, int, dec, date and time. String is the default so you can skip the “string” name.
  • List of hidden column numbers - coma delimited list, starting from zero.
  • Row classes and Column classes – coma delimited list of classes.
  • Caption in first column – if you select this then table caption will be taken from the first field of the first row. Value of the Caption field will not be used.
  • Number of column containing change. Values fom this column are compared to 0. You can use this for additional styling. I used this in the table presenting quotations data. Values bigger than 0 was green color, lower than zero was red and values equal 0 was blue ;-) See formatOutputHook hook.
Cache sheet

Here you can only set amount of seconds the output table is kept in cache (caching lifetime).

Configuration

Reference

plugin.tx_tksvconsqltable_pi1.formatting

dateFormat

Property

dateFormat

Data type

string

Description

Date format for PHP function date().

Default

Y-m-d

timeFormat

Property

timeFormat

Data type

string

Description

Time format for PHP function date().

Default

G:i

decimals

Property

decimals

Data type

string

Description

Number of decimals for PHP function number_format().

Default

0

dec_point

Property

dec_point

Data type

string

Description

Decimal separator for PHP function number_format().

Default

.

thousands_sep

Property

thousands_sep

Data type

string

Description

Thousands separator for PHP function number_format(). Special value “space” can be used if you want to use this char as thousands separator.

Default

,

plugin.tx_tksvconsqltable_pi1.db

driver

Property

driver

Data type

string

Description

Supported by ADOdb database driver name

Default

server

Property

server

Data type

string

Description

Server name

Default

user

Property

user

Data type

string

Description

Database user name

Default

password

Property

password

Data type

string

Description

Database user password. NOTE! Password is stored not encrypted.

Default

database

Property

database

Data type

string

Description

Name of the database

Default

query

Property

query

Data type

string

Description

SQL query

Default

init

Property

init

Data type

string

Description

DB initialization parameters. F.e. “SET NAMES utf8;”

Default

fetchMode

Property

fetchMode

Data type

Integer

Description

Defines result table type. Available are default, numerical, associative and both.

Default

0 (default)

plugin.tx_tksvconsqltable_pi1.table

templateFile

Property

templateFile

Data type

String

Description

Path to HTML table template

Default

res/template.html

Border

Property

Border

Data type

Integer

Description

Table border

Default

1

cellPadding

Property

cellPadding

Data type

integer

Description

Table celppadding parameter

Default

2

cellspasing

Property

cellspasing

Data type

integer

Description

Table celpspacing parameter

Default

1

class

Property

class

Data type

string

Description

Table CSS class name

Default

caption

Property

caption

Data type

string

Description

Table caption

Default

caption_as_span

Property

caption_as_span

Data type

Integer

Description

Caption will be drawn before table in span

Default

0

colNames

Property

colNames

Data type

string

Description

Coma delimited list of column names – header. NOTE! Amount of column names must be equal to amount of column formats.

Default

colFormats

Property

colFormats

Data type

string

Description

Coma delimited list of column formats which are used for date formatting. Available formats are: string, int, dec, date, time. Empty fields will be treated as strings.

NOTE! Amount of column formats must be equal to amount of column names.

Default

hiddenCols

Property

hiddenCols

Data type

string

Description

Coma delimited list of column numbers. Beginning from zero.

Default

rowClasses

Property

rowClasses

Data type

string

Description

Coma delimited list of row classes

Default

even,odd

colClasses

Property

colClasses

Data type

string

Description

Coma delimited list of column CSS classes

Default

captionInFirstCol

Property

captionInFirstCol

Data type

boolean

Description

Set it to 1 if you want have table caption in the first field of the first row. NOTE! Caption parameter will not be used.

Default

0

change_col

Property

change_col

Data type

Integer

Description

Number of column containing value compared to zero.

Default

lugin.tx_tksvconsqltable_pi1.cache

lifetime

Property

lifetime

Data type

integer

Description

Amount of seconds for output caching

Default

3600

((generated))
Example
page = PAGE
page.typeNum = 0
page.10 < plugin.tx_tksvconsqltable_pi1
page.10 {
  cache.lifetime = 3600

  formating {
    dateFormat = Y-m-d
    timeFormat = G:i
    decimals = 4
    dec_point = .
    thousands_sep = ,
  }
  db {
    driver = mssql
    server = 10.20.30.40
    user = SomeUser
    password = your-secret-password
    database = SomeDB
    query = select field1, field2, field3 from sometable
    init >
    fetchMode = 1
  }
  table {
    cellPadding = 2
    cellSpacing = 1
    class = someTableClass
    caption = This is my table
    colNames = Field1 Title, Field2 Title, Field3 Title, Field4 Title
    colFormats = ,date,time,dec
    hiddenCols = 1
    rowClasses = ,alt
    colClasses = ,f1,f2,f3
    captionInFirstCol = 0
    change_col >
  }
}