Search

dbListColumns

Version 23.4.8839

dbListColumns

Lists the columns in a table or view on the database server. Depending on your configuration and the target database type, some of these parameters and output attributes might not be relevant.

Required Parameters

  • driver: The JDBC driver class name in the cross-platform edition or the ADO.NET provider name in the .NET edition.
  • conn: The connection string or database URL.
  • table: The table or view from which to list the columns.

Optional Parameters

  • schema: The name of the database schema in which the table resides. The schema is commonly referred to as the namespace of a group of tables.
  • catalog: The catalog in the database server. This is commonly referred to as the database. If you do not provide a value, columns from all catalogs in the database server are returned.

Output Attributes

  • db:columnname: The name of the column in the specified table or view.
  • db:datatype: The SQL data type of the column in the specified table or view.
  • db:columnsize: The size of the column in the specified table or view.
  • db:iskey: Boolean (true or false) indicating if the column is the primary key column.
  • db:nullable: Boolean (true or false) indicating if the column is nullable.
  • db:readonly: Boolean (true or false) indicating if the column is read-only.
  • db:autoincrement: Boolean (true or false) indicating if the column is an autoincrement column.

Example

In this example, the targeted database server is MySQL and the targeted table is actor. Notice that there is no database parameter in the connection string: if the database is specified in the connection string, the catalog parameter is not required. This script writes all the attributes of the target table to an output file.

<!-- Initializing the output item -->
<arc:set attr="output.data" />
<!-- Creating the input db item and the necessary attributes -->
<arc:set attr="db.driver" value="cdata.jdbc.mysql.MySQLDriver" />
<arc:set attr="db.catalog" value="sakila" />
<arc:set attr="db.table" value="actor" />
<arc:set attr="db.conn" value="jdbc:cdata:mysql:server=localhost;port=3306;user=root;password=Password123;"/>

<arc:call op="dbListColumns" in="db" out="results" >
  <!-- adding result data from the operation to an output item that will be pushed out as a file -->
  <arc:set attr="output.data">[output.data]\nColumn Name=[results.db:columnname]\nData Type=[results.db:datatype]\nSize=[results.db:columnsize]\nKey=[results.db:iskey]\nNullable=[results.db:nullable]\nReadOnly=[results.db:readonly]\nAutoInc=[results.db:autoincrement]\n-----</arc:set>
</arc:call>

<!-- setting the filename and pushing the file out --> 
<arc:set attr="output.filename" value="results.txt" />
<arc:push item="output" />

Output:

Column Name=actor_id
Data Type=SMALLINT UNSIGNED
Size=2
Key=True
Nullable=False
ReadOnly=False
AutoInc=True
-----
Column Name=first_name
Data Type=VARCHAR
Size=45
Key=False
Nullable=False
ReadOnly=False
AutoInc=False
-----
Column Name=last_name
Data Type=VARCHAR
Size=45
Key=False
Nullable=False
ReadOnly=False
AutoInc=False
-----
Column Name=last_update
Data Type=TIMESTAMP
Size=19
Key=False
Nullable=False
ReadOnly=False
AutoInc=False
-----

You can modify the example above to target other databases as long as the driver is installed on the machine where Arc is installed and running. Adjusting this example for .NET involves changing the driver and conn inputs as follows:

<arc:set attr="db.driver" value="System.Data.CData.MySql" /> 
<arc:set attr="db.conn" value="Server=localhost;Database=sakila;UID=root;Password=Password123;"/>