Tags:
create new tag
, view all tags

Question

I created the mysql database "ventas" which contains the table "shop" using the example data from the mysql manual:

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec) 

But I don't know how to do queries to the database/table created. I don't understand the function of the 'description' variable. Could somebody please help me?

I have this settings on bin/DatabasePluginConfig.pm:

$db_driver      = "mysql";
$db_database    = "ventas";
$db_username    = "pch";
$db_password    = "123456";
$db_table       = "shop";

$db_hostname    = "localhost";
$db_edit_prefix = "https";

Trying:

%DATABASE_TABLE{description="I don't know what put here" columns="vendor"}%

The error Apache log shows:

[Tue Jan 21 12:09:51 2003] null: DBD::mysql::st execute failed: Unknown column '
description' in 'field list' at ../lib/TWiki/Plugins/DatabasePlugin.pm line 149.
[Tue Jan 21 12:09:51 2003] null: DBD::mysql::st fetchrow_array failed: fetch() w
ithout execute() at ../lib/TWiki/Plugins/DatabasePlugin.pm line 151. 

Thanks

  • TWiki version: dec 2000
  • Perl version: 5.6.0
  • Web server & version: Apache 1.3.20
  • Server OS: Red Hat 7.2
  • Web browser & version: Phoenix 0.5
  • Client OS: Windows 98

-- PabloChamorro - 21 Jan 2003

Answer

You have to have a table in your database with the (ID, description, ...) fields described in DatabasePlugin. Then you set "table" and the other attributes in DatabasePluginConfig.pm to access that table, not to the table you want to look at. The description="..." attribute of the %DATABASE_TABLE% tag refers to a row in the new table with all the columns from the DatabasePlugin table. Then you put all the info to access that into there.

So say you would have a twiki_database_plugin table in your mysql database that would look like this:

id description driver db_name table_name ro_username ro_password hostname
1 VentasShop mysql ventas shop pch 123456 localhost

Then your DatabasePluginConfig.pm file would have this:

...
$db_table       = "twiki_database_plugin";
...

And you would reference your "shop" table like this: %DATABASE_TABLE{description="VentasShop"}%

You could add more tables to access by adding rows in the twiki_database_plugin table.

Note that it would be more secure if:

  • You used some user without access to the twiki_database_plugin table to access the other tables.
  • That user did not have write access to any tables.

-- WalterMundt - 22 Jan 2003

Question: IP instead of hostname?

I just realized that the entries for hostname in DatabasePluginConfig and the access-table have to be verbose: setting either or both to 127.0.0.1 did not work, with TWiki and DB (mysql) running on the same server. localhosts works fine. Ales, due to limitations of the local network, I'll probably have to get plain IPs to work. Did I just forget to try one type of quoting (tried single, double)? Or will I have to patch the local hosts?

-- PatrickHansmeier - 11 Feb 2003

What should the value of ConfigSource be in the configuration file for DatabasePlugin?

-- BrianBeaudet - 25 Apr 2007

Here's a clip from the Config.spec:

#---+ DatabasePlugin
# Database access information can either be configured here,
# or it can be recovered from a remote database.
# <p />
# If a remote database is used, it must contain a table with the
# following fields:
# <ul>
# <li>description - A symbolic name for the database.</li>
# <li>driver - Database driver</li>
# <li>db_name - The name of the database</li>
# <li>db_sid - The SID of the database (optional: required for Oracle)</li>
# <li>ro_username - The user name to use</li>
# <li>ro_password - The password for the specified ro_username</li>
# <li>hostname - The name of the host on which the specified db_name lives</li>
# </ul>
# The table specifies all the available databases.
# <p />
# **SELECT Local,Remote**
# Specifies where to get database configuration information from. 'Local'
# gets it here, 'Remote' looks up a database.
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigSource} = 'Local';
#---++ Setup for Remote database table
# **STRING 30**
# If {ConfigSource} is Remote, specifies the name of the host that
# serves the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigHost} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the driver for the DB that
# serves the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigDriver} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the name of the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigDB} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the sid of the database that
# contains the database info table (not required for MySQL)
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigSID} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the name of the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigTable} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the username for the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigUsername} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the password to the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigPassword} = '';
# ---++ Setup for DATABASE_EDIT (optional)
# **STRING 30**
$TWiki::cfg{Plugins}{DatabasePlugin}{EditURLPrefix} = 'https';
# **STRING 50**
# This can either be a relative path or absolute.  If absolute, then it
# would look something like machine.com/path_to_place_for/phpMyAdmin
$TWiki::cfg{Plugins}{DatabasePlugin}{EditURLPath} = 'phpMyAdmin-2.2.2-rc1';
#---++ Setup for Local databases table
# **PERL**
# Table of configuration info for all the databases you might access.
# Used if {ConfigSource} is Local.
# This structure is an array of database definitions. Each database
# is defined using a hash where the fields of the array are:
# <ol>
# <li> description - Symbolic name for this database</li>
# <li> driver - DB driver - values like: mysql, Oracle, etc.</li>
# <li> hostname - DB host</li>
# <li> database - DB name</li>
# <li> sid - DB sid (Oracle, ignore for MySQL)</li>
# <li> username - DB username</li>
# <li> password - DB password</li>
# <li> table_name - optional table name, used with DATABASE_TABLE and DATABASE_REPEAT to predefine which table to look up
# </ol>

I recommend using 'Local'.

-- CrawfordCurrie - 14 May 2007

Setting this to answered. Please open a new support question if you have additional questions.

-- PeterThoeny - 06 Jul 2007

Edit | Attach | Watch | Print version | History: r8 < r7 < r6 < r5 < r4 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r8 - 2007-07-06 - PeterThoeny
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.