NORSYS SOFTWARE © 2012

NETICA API

JAVA VERSION 5.04

norsys.netica
Class DatabaseManager

java.lang.Object
  |
  +--norsys.netica.DatabaseManager

public class DatabaseManager

extends java.lang.Object

A resource for connecting to a database and importing case data or exporting findings to that database. Currently, only ODBC compliant databases are supported.

ODBC stands for "Open Database Connectivity". It allows for a standard approach to access any database for which an ODBC "driver" has been written. Most databases available today support it. ODBC connectivity allows Netica to access case data within a database, to populate databases from the data in a network, or to generally manipulate databases by means of standard SQL statements.

For Netica to support ODBC connectivity at the C-API level, ODBC libraries need to be linked into the Netica software. Such libraries are platform specific. Currently only the ODBC libraries for the Microsoft Windows platforms (Win98/ME/NT/2000/XP) have been linked into Netica. Accordingly, only Netica running on a Microsoft Windows platform can take advantage of this functionality. However, the database itself can be available on any platform, such as an ORACLE database available via the Internet.

In future, other client platforms may be supported. Please contact Norsys, if you require ODBC access from a non-Windows platform.

Since:

All versions

Version:

5.04 - January 21, 2012

Constructor Summary

DatabaseManager(java.lang.String odbcConnectionString, java.lang.String options, Environ env)

Creates and returns a new DatabaseManager object, which manages all interaction with a database.

Method Summary

void	addNodes(Net net, java.lang.String columns, java.lang.String tables, java.lang.String condition, java.lang.String options)
void	executeSql(java.lang.String sqlCmd, java.lang.String options) Executes sqlCmd, an arbitrary SQL1 command.
void	finalize() Removes this DatabaseManager from the system, and releases all the resources it uses (memory, connections, etc.).
Environ	getEnviron() Returns this DatabaseManager's Netica environment.
void	insertFindings(NodeList nodeList, java.lang.String columnNames, java.lang.String tables, java.lang.String options) Creates a new record in the database dbMgr consisting of the current findings.

Methods inherited from class java.lang.Object

clone, equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

public DatabaseManager (

String	odbcConnectionString,
String	options,
Environ	env

) throws NeticaException

Creates and returns a new DatabaseManager object, which manages all interaction with a database. The database must be ODBC compliant.

Note. This method is currently only available for Netica running on Microsoft Windows. For support on other platforms, please contact Norsys.

Connection String

The connectionString defines an ODBC data source that this database manager will communicate with. The connectionString is the standard ODBC resource definition string passed directly to the standard SQLDriverConnect ODBC command. The general syntactic form of a connection string is "param=value;param=value;...", where 'param' is not case sensitive. Here are some sample connection strings for some common types of database configuration:

Data Source		Connection String
A Microsoft Access database located on your hard disk at C:\MyProjectDir\myAccessDB.mdb		"driver={Microsoft Access Driver (*.mdb)}; dbq=C:\\MyProjectDir\\myAccessDB.mdb; UID=myUserAccount;PWD=myPassword"
A Microsoft Excel spreadsheet located on your hard disk at C:\MyProjectDir\myData.xls		"driver={Microsoft Excel Driver (*.xls)}; dbq=C:\\MyProjectDir\\myData.xls"
An arbitrary data source named "myDataSource" that is registered with the Windows ODBC Data Source Administrator1		"DSN=myDataSource;UID=myUserAccount;PWD=myPassword"
An ORACLE 8i data server running locally with database name "OraInstanceName"		"DRIVER={Microsoft ODBC for Oracle}; SERVER=OraInstanceName; UID=OraUser;PWD=OraPswd;"
A MySQL database called "myDB" running on a machine whose domain name address is "db1.abc.com" communicating via port 5432		"Driver={MySQL};Server=db1.abc.com; Port=5432;Option=131072;Stmt=;Database=myDB; Uid=myUsername;Pwd=myPassword"
A Microsoft SQL Server database called "myDB" running on a machine called "DB_server" on your Microsoft Network LAN		"Driver={SQL Server};Server=DB_server;DataBase=myDB"
A text file2 located on your hard disk at C:\MyProjectDir\myFile.csv		"Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=C:\MyProjectDir\;Extensions=asc,csv,tab,txt"

¹ To access the Windows ODBC Data Source Administrator, from the Start menu, select "Settings" then "Control Panel". Then for Windows 2000/XP, double-click on "Administrative Tools" and then "Data Sources (ODBC)", and for Windows 95/98/NT, double-click on "ODBC Sources".

² For text files, your SQL statements must use the file name as the TABLE name. For example: "SELECT * FROM myFile.csv". Furthermore, the first line of the text file is assumed to give the COLUMN names. If you prefer other options than these, then use the Windows ODBC Data Source Administrator¹ which has an excellent wizard for text-file databases.

If the database does not have user accounts, then you do not need to specify a UID and PWD.

There are a great many parameters that can be specified within connectionString. Some are generic and apply to all database vendors, like "DSN" and "UID", while others are vendor specific. See your database vendor's documentation and the documentation for SQLDriverConnect to see what other parameters may be available. A good on-line source of documentation for ODBC is available at http://www.microsoft.com/data/odbc/.

Hint: If you are having difficulty getting your connection string to work, then use the Windows ODBC Data Source Administrator to connect to the database, give it a data source name (DSN), "myName", and then use "DSN=myName" as the simplified connection string. The Administrator has powerful wizards to facilitate both finding the database and defining precisely how you wish to access it.

WARNING: Because the connection string may contain a UserID and Password to your database, it represents a security risk. You may want to take extra precautions by securing your source code, dynamically fetching the password from the user, or asking your database administrator to place extra restrictions on the database user account.

Connection Pooling

The ODBC 3.0 standard allows for the caching of database connections, also known as "connection pooling". Every ODBC call involves requesting a connection and releasing it when done. Making a connection is expensive and can often take longer than an actual query to the database. For this reason, you will typically want to enable connection pooling so that your connections are opened only once, and thereafter are taken from and released back to the pool, rather than being really fully initialized and released with each ODBC call. You may wish to disable connection pooling if there are only a limited number of connections available and your process must be a good citizen and share its database connectivity with other processes.

Pass "pooling" for options to enable connection pooling. Pass "no_pooling" to disable it. Other control parameters may be added in future.

Connections in the pool may expire after a time. Adjustments to this time limit and to other properties of the connection pool can be made sometimes via the connection string (see above), and sometimes via the ODBC Data Source Administrator control panel, depending on the ODBC driver that is available.

When the DatabaseManager object is deleted (see finalize), any connections it may have are released.

Parameters:

String		connectionString		A standard ODBC server definition string.
String		options		For now, supply "pooling" to enable connection pooling, or "no_pooling" to disable it. Other control parameters may be added in future.
Environ		env		The Environ in which this new DatabaseManager will be placed.

Version:

Versions 2.26 and later have this method.
In the C Version of the API, this function is named NewDBManager_cs.

See Also:

finalize		Discard the database manager
executeSql		Execute an arbitrary SQL command
insertFindings		Insert net findings using SQL INSERT
Caseset.addCases		Retrieve a set of cases using SQL SELECT
addNodes		Add nodes to a net using cases from SQL SELECT

Example:

 // Create a new table called "Table2" in our MS-Access database, myDb.mdb,
 // and define four columns in the table.
 DatabaseManager dbMgr = new DatabaseManager(
      "driver={Microsoft Access Driver (*.mdb)}; dbq=.\\myDB.mdb;UID=dba1;", 
      "pooling", 
      env );
 dbMgr.executeSql( "CREATE TABLE Table2 (TxtFld1 CHAR(10),
                                         IntFld1 INTEGER,
                                         FloatFld1 FLOAT,
                                         DateFld1 date)",
                                         null);

Method Detail

public void addNodes (

Net	net,
String	columns,
String	tables,
String	condition,
String	options

) throws NeticaException

Adds new nodes to net corresponding to variables in the database, if they aren't there already.

For more information on columnNames, tables and condition, see Caseset.addCases.

options may be "favor_discrete" or "favor_continuous" to control whether to add discrete nodes or continuous nodes for questionable database columns.

This method behaves similarly to the Netica Application menu choice Cases -> Add Case File Nodes. You may want to experiment with that before using this function in your code.

Parameters:

Net		net		The net you wish to add nodes to.
String		columnNames		The comma-delimited list of database column names, in the same order as the nodes in nodeList. If null, then Netica will use the Node titles or names as the column names.
String		tables		the comma-delimited list of database table names into which you are selecting. May be null, if the database has only one conventional (non-system) table.
String		condition		the conditions that follow the "WHERE" clause in a SQL INSERT statement.
String		options		For future expandability. Pass null for now.

Version:

Versions 3.22 and later have this method.
In the C Version of the API, this function is named AddNodesFromDB_bn.

See Also:

DatabaseManager		Creates the DatabaseManager
executeSql		Execute an arbitrary SQL command
insertFindings		Insert net findings using SQL INSERT
Caseset.addCases		Retrieve a set of cases using SQL SELECT

public void executeSql (

String	sqlCmd,
String	options

) throws NeticaException

Executes sqlCmd, an arbitrary SQL¹ command.

This method is useful for doing database administration tasks. Netica makes no attempt to interpret the command; it just passes it directly to the database driver.

If there is a problem with the SQL command, a Netica exception will be thrown explaining the nature of the problem.

WARNING: This method can severely modify the database.

¹ SQL is a standard query language for accessing databases. To properly use this method, you should have familiarity with SQL.

Pass null for options; it is only for future expansion.

Parameters:

String		sqlCmd		The SQL command to execute.
String		options		For future expandability. Pass null for now.

Version:

Versions 2.26 and later have this method.
In the C Version of the API, this function is named ExecuteDBSql_cs.

See Also:

DatabaseManager		Creates the DatabaseManager
insertFindings		Insert net findings using SQL INSERT
Caseset.addCases		Retrieve a set of cases using SQL SELECT
addNodes		Add nodes to a net using cases from SQL SELECT

Example:

See DatabaseManager

public void finalize ( ) throws NeticaException

Removes this DatabaseManager from the system, and releases all the resources it uses (memory, connections, etc.).

Version:

Versions 2.26 and later have this method.
In the C Version of the API, this function is named DeleteDBManager_cs.

See Also:

DatabaseManager

Create a new database manager

Overrides:

finalize in class java.lang.Object

public Environ getEnviron ( ) throws NeticaException

Returns this DatabaseManager's Netica environment.

Version:

Versions 2.26 and later have this method.

See Also:

DatabaseManager

Create a new DatabaseManager in the Environ

public void insertFindings (

NodeList	nodeList,
String	columnNames,
String	tables,
String	options

) throws NeticaException

Creates a new record in the database dbMgr consisting of the current findings.

This method corresponds to the basic SQL¹ INSERT statement:

INSERT INTO table1 (col1,col2,...,colN) VALUES (value1,value2,...,valueN) .

nodeList represents the nodes (node1,...,nodeN) whose current finding values (value1,...,valueN) will be inserted.

columnNames is a comma-delimited list of database column names. The names in this list must be in the exact same order as their corresponding nodes in nodeList. If columnNames is null, then for each Node, Netica will use that Node's title (or, if the title is not defined, then the name) as the corresponding column name.

tables is a comma-delimited list of database table names. If the database has only one conventional (non-system) table, then you can submit null for this parameter and Netica will find the implied table for you.

Thus, for the SQL command INSERT INTO table1 (col1,col2,...,colN) VALUES (value1,value2,...,valueN), tables should be "table1"; columnNames should be "col1,col2,...,colN"; and nodeList should be a list of nodes in the order node1, node2, ..., nodeN.

Pass null for options; it is only for future expansion.

What value is inserted? If a node does not have a value then "NULL" is used for the value inserted. For most databases, this has the result of inserting a "Missing Data" value, although check with your database vendor regarding this (e.g., MS-ACCESS-2000 generally follows this rule but will insert "false" in a boolean field, instead of "Missing Data"). Otherwise, if a node does have a value, then the behavior varies, depending on whether the node is discrete or continuous. For discrete nodes, if that node has a state title, then that state title is inserted. If it does not have a title, but it has a name, then the name is inserted. And if it has neither title nor name, then the numeric state index (0... nStates-1) is inserted. For continuous nodes, the number inserted is the same as that returned by Node.finding.

If there is a problem with the SQL INSERT command, an exception will be thrown explaining the nature of the problem.

¹ SQL is a standard query language for accessing databases. To properly use this method, you should have basic familiarity with the SQL INSERT statement.

Parameters:

NodeList		nodeList		The nodes whose values will be inserted.
String		columnNames		The comma-delimited list of database column names, in the same order as the nodes in nodeList. If null, then Netica will use the Node titles or names as the column names.
String		tables		the comma-delimited list of database table names into which you are inserting. May be null, if the database has only one conventional (non-system) table.
String		options		For future expandability. Pass null for now.

Version:

Versions 2.26 and later have this method.
In the C Version of the API, this function is named InsertFindingsIntoDB_bn.

See Also:

DatabaseManager		Creates the DatabaseManager
executeSql		Execute an arbitrary SQL command
Caseset.addCases		Retrieve a set of cases using SQL SELECT
addNodes		Add nodes to a net using cases from SQL SELECT

Example:

  DatabaseManager dbMgr = new DatabaseManager ("driver={Microsoft Access Driver (*.mdb)};
                                                dbq=.\\myDB.mdb;UID=dba1;", 
                                                "pooling", env );
  
  Net net = new Net ();
  net.setName ("databaseTestNet");
  
  Node sexNode       = new Node ("sex",       2, dbNet);
  Node heightNode    = new Node ("height",    0, dbNet);
  Node ownsHouseNode = new Node ("ownsHouse", 2, dbNet);
  Node numDogsNode   = new Node ("numDogs",   0, dbNet);
  sexNode.state(0).setName ("M");
  sexNode.state(1).setName ("F");
  
  sexNode.finding().enterState ("M");
  heightNode.finding().enterReal (2.222);
  ownsHouseNode.finding().enterState (1);
  
  //Insert the net's current findings into Table1
  dbMgr.insertFindings (dbNet.getNodes(), 
                        "Sex, Height, \"Owns a house\", \"Number of dogs\"", 
                        "Table1",
                        null);