Note: Direct database imports cannot be done with Corda® Embedder in PHP or PERL. For these environments, follow the instructions in Manually Processing A Query.
To import data directly from a JDBC or ODBC driven database, specify a database driver, name, and SQL query, and Corda Embedder automatically executes the query and imports the resulting data into the Image Template file.
While this method is powerful, it prevents you from creating context-specific special effects like drilldown and hover text. The reason for this is that you never "see" the data, and thus cannot determine where to place these context-specific special effects. If these effects are needed, use a manual database query, as described in Manually Processing A Query.
Topics discussed in this section include:
Important: This documentation assumes you are familiar with SQL queries and the database to which you are connecting. If not, become familiar with them before continuing, or get help from a database administrator.
When preparing an SQL query, it is important to have an idea of what the resulting data table should look like. Organize the data in tables consistent with the spreadsheet format used by Corda Builder's Data Editor. For more information about organizing data, see Graph Data and About Maps in the Corda Builder™ User Guide.
For non-standard graph types (X-Y and Time Plots), see Data Organization in the Corda 7 Graph Guide.
If a graph uses data categories, the data table can also be organized with each data item in a separate row, and columns for the category name, series name, and data value. For more information, see Alternative Data Organization Format in the Corda 7 Graph Guide.
To send data to a map shape, you must properly identify the map shape to which the data value belongs. Data sets can identify a map shape using one of three attributes: name, long name, or code. All three of these names can be discovered either in Corda Builder, or in the ITXML source for the Image Template file.
Ideally, create SQL queries that contain only the data needed for Corda Server™ to populate the graph or map. However, Corda Server can filter the data set, if necessary. For more information, see META Tags.
Before importing data in Java, make sure you have a JDBC driver for the database. Some installations of Java (including the JRE installed with Corda Server on Windows*) already contain certain drivers. However, if the Java installation does not contain the appropriate driver, include the appropriate JDBC driver in the classpath for the Java program, servlet, or Web application. Do this in the same way that you added the CordaEmbedder.jar file to the Java classpath in Java Application Servers in the Corda 7 Install and Administration manual.
Once you have done this, use the setDBQuery() (for graphs) or setMapDBQuery() (for maps) to import data.
The syntax for setDBQuery() is as follows:
myImage.setDBQuery(objectname, databasedriver, databaseURL, user, password, SQLquery);
objectname: The name of the graph object into which you want to import the data.
databasedriver: The full name of the JDBC driver. This is not the file or archive that contains the driver, but rather the Java class itself. For example: com.oracle.jdbc.OracleDriver.
databaseURL: The name of the database, which is typically in the form jbdc:driver:name (e.g., jdbc:odbc:mydata, jdbc:oracle:mydata . Make sure that this database is accessible from the computer on which you have installed Corda Embedder.
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The sample code below demonstrates the use of setDBQuery using Sun's JDBC-ODBC bridge driver, which is a database driver automatically installed with JRE for Windows.
CordaEmbedder myImage = new CordaEmbedder();
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "image_templates\examples\bar.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setDBQuery("graph", //name of graph or map
"sun.jdbc.odbc.JdbcOdbcDriver", //database driver
"jdbc:odbc:CordaSamples", //database URL
"user", //user name
"password", //password
"Select Description, OnHand from parts order by Description");//SQL Query
String PCHTML = myImage.getEmbeddingHTML();
The syntax for setMapDBQuery() is as follows:
myImage.setDBQuery(objectname, layer, databasedriver, databaseURL, user, password, SQLquery);
objectname: The name of the map shape into which you want to import the data.
layer: The map layer in which the map shape resides. For more information about map layers, see Map Layers in the Corda Builder User Guide.
databasedriver: The full name of the JDBC driver. This is not the file or archive that contains the driver, but rather the Java class itself. For example: com.oracle.jdbc.OracleDriver .
databaseURL: The name of the database, which is typically in the form jbdc:driver:name (e.g., jdbc:odbc:mydata, jdbc:oracle:mydata . Make sure that this database is accessible from the computer on which you have installed Corda Embedder.
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The sample code below demonstrates the use of setMapDBQuery using Sun's JDBC-ODBC bridge driver, which is a database driver automatically installed with JRE for Windows.
Example setMapDBQuery Java code
CordaEmbedder myImage = new CordaEmbedder();
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "maps\US\US.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setMapDBQuery("texas", //name of graph or map
"Areas", //name of map layer
"sun.jdbc.odbc.JdbcOdbcDriver", //database driver
"jdbc:odbc:CordaSamples", //database URL
"user", //user name
"password", //password
"Select Description, OnHand from parts order by Description"); //SQL Query
To query data using a custom method, take advantage of the Java Corda Embedder's setResultSet and setMapResultSet methods, which let you import data from a resultSet object.
For graphs, use setResultSet, which takes two parameters:
The name of the object into which you want to import the data
A resultSet object
The sample code below demonstrates the use of setResultSet. It assumes you have already imported the java.sql library (with the statement import java.sql.* . This code achieves the same result as the setDBQuery Java code sample above.
Using setResultSet
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con= DriverManager.getConnection("jdbc:odbc:CordaSamples", "user", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select Description, OnHand from parts order by Description");
myImage.setResultSet("graph", rs);
For maps, use setMapResultSet, which takes three parameters:
The sample code below demonstrates the use of setMapResultSet. It assumes you have already imported the java.sql library (with the statement import java.sql.*). This code achieves the same result as the setMapDBQuery Java code sample above.
Using setMapResultSet
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con= DriverManager.getConnection("jdbc:odbc:CordaSamples" , "user", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select Description, OnHand from parts order by Description");
myImage.setMapResultSet("texas", ”Areas”, rs);
Use the .NET Corda Embedder to import data from any ODBC data source configured on the computer that is generating Web pages.
On Windows NT/2000/XP systems, view ODBC data sources by selecting Administrative 7 > Data Sources from the Control Panel. This opens the ODBC Data Source Administrator. Corda Embedder can access any database listed under the User DSN, System DSN, and File DSN tabs, but you typically only want to use data sources listed under the System DSN tab, as other data sources aren't available when the current user logs off of the system.
Note: Add a database to this list by clicking on the Add button. This documentation assumes that you have already successfully added the database you want to access.
The image below shows what you might see in the ODBC Data Source Administrator. In this particular example, the database that we want to use is CordaSamples.
Once you have done this, use the setDBQuery (for graphs) or setMapDBQuery (for maps) to import data.
The syntax for setDBQuery is as follows:
myImage.setDBQuery(objectName, "", databaseName, user, password, SQLquery);
objectname: The name of the graph object into which you want to import the data.
unused: The second parameter is not used in the COM version of setDBQuery and should be set to an empty string.
databaseName: The name of the database, available from the ODBC Data Source Administrator (e.g., CordaSamples). Be sure that this database is accessible from the computer running Corda Embedder.
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The code sample below demonstrates the use of setDBQuery using a graph object named graph; a user name and password of user and password; a database named CordaSamples; and an SQL query of "Select Description, OnHand from parts order by Description".
Importing from a Database to a Graph Using ASP
set myImage = Server.CreateObject("Corda.Embedder");
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "image_templates\examples\bar.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setDBQuery "graph", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description"
Response.Write myImage.getEmbeddingHTML()
The syntax for setMapDBQuery is similar:
myImage.setMapDBQuery(objectName, layer, "", databaseName, user, password, SQLquery);
objectname: The name of the map shape into which you want to import the data.
layer: The map layer in which the map shape resides. For more information about map layers, see Map Layers in the Corda Builder User Guide.
unused: The second parameter is not used in the COM version of setDBQuery and should be set to an empty string.
databaseName: The full name of the JDBC driver. This is not the file or archive that contains the driver, but rather the Java class itself. For example: com.oracle.jdbc.OracleDriver .
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The code sample below demonstrates the use of setMapDBQuery using a map object named texas; a user name and password of user and password; a database named CordaSamples; and an SQL query of "Select Description, OnHand from parts order by Description".
Importing from a Database to a Map Using ASP
set myImage = Server.CreateObject("Corda.Embedder");
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "maps\US\US.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setMapDBQuery "texas", "Areas", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description"
Response.Write myImage.getEmbeddingHTML()
Use the.NET version of Corda Embedder to import data from any ODBC data source that has been set up on the computer that is generating Web pages.
On Windows NT/2000/XP systems, view ODBC data sources by selecting Administrative 7 > Data Sources from the Control Panel. This opens the ODBC Data Source Administrator. Corda Embedder can access any database listed under the User DSN, System DSN, and File DSN tabs, but you typically only want to use data sources listed under the System DSN tab, as other data sources aren't available when the current user logs off of the system.
Note: Add a database to this list by clicking on the Add button. This documentation assumes that you have already successfully added the database you want to access.
The image below shows what you might see in the ODBC Data Source Administrator. In this particular example, the database that we want to use is CordaSamples.
Once you have done this, use the setDBQuery (for graphs) or setMapDBQuery (for maps) to import data.
The syntax for setDBQuery is as follows:
myImage.setDBQuery(objectName, "", databaseName, user, password, SQLquery);
objectname: The name of the graph object into which you want to import the data.
unused: The second parameter is not used in the COM version of setDBQuery and should be set to an empty string.
databaseName: The name of the database, available from the ODBC Data Source Administrator (e.g., CordaSamples). Be sure that this database is accessible from the computer running Corda Embedder.
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The two code samples below demonstrate the use of setDBQuery using a graph object named graph; a user name and password of user and password; a database named CordaSamples; and an SQL query of "Select Description, OnHand from parts order by Description".
Importing from a Database to a Graph Using ASP.NET (C#)
set myImage = Server.CreateObject("Corda.Embedder");
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "image_templates\examples\bar.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setDBQuery("graph", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description");
Response.Write(myImage.getEmbeddingHTML());
Importing from a Database to a Graph Using ASP.NET (Visual Basic)
set myImage = Server.CreateObject("Corda.Embedder")
myImage.externalServerAddress="<server_address>:2001"
myImage.internalCommPortAddress = "localhost:2002"
myImage.imageTemplate = "image_templates\examples\bar.itxmlv"
myImage.width = 600
myImage.height = 400
myImage.pcScript = "title.setText(Hello World)"
myImage.setDBQuery "graph", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description"
Response.Write myImage.getEmbeddingHTML()
The syntax for setMapDBQuery is similar:
myImage.setMapDBQuery(objectName, layer, "", databaseName, user, password, SQLquery);
objectname: The name of the map shape into which you want to import the data.
layer: The map layer in which the map shape resides. For more information about map layers, see Map Layers in the Corda Builder User Guide.
unused: The second parameter is not used in the COM version of setDBQuery and should be set to an empty string.
databaseName: The full name of the JDBC driver. This is not the file or archive that contains the driver, but rather the Java class itself. For example: com.oracle.jdbc.OracleDriver .
user: The username to access the database. Pass an empty string if no username is needed.
password: The password to access the database. Pass an empty string if no password is needed.
SQLquery: The actual SQL query to run on the database. For more information about building an SQL query, see Preparing a Query above.
The two code samples below demonstrate the use of setMapDBQuery using a map object named texas; a user name and password of user and password; a database named CordaSamples; and an SQL query of "Select Description, OnHand from parts order by Description".
Importing from a Database to a Map Using ASP.NET (C#)
set myImage = Server.CreateObject("Corda.Embedder");
myImage.externalServerAddress="<server_address>:2001";
myImage.internalCommPortAddress = "localhost:2002";
myImage.imageTemplate = "maps\US\US.itxml";
myImage.width = 600;
myImage.height = 400;
myImage.pcScript = "title.setText(Hello World)";
myImage.setMapDBQuery("texas", "Areas", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description");
Response.Write(myImage.getEmbeddingHTML());
Importing from a Database to a Map Using ASP.NET (Visual Basic)
set myImage = Server.CreateObject("Corda.Embedder")
myImage.externalServerAddress="<server_address>:2001"
myImage.internalCommPortAddress = "<server_address>:2002"
myImage.imageTemplate = "maps\US\US.itxml"
myImage.width = 600
myImage.height = 400
myImage.pcScript = "title.setText(Hello World)"
myImage.setMapDBQuery "texas", "Areas", "", "CordaSamples", "user", "password", "Select Description, OnHand from parts order by Description"
Response.Write myImage.getEmbeddingHTML()
ASP and ASP.NET let you make a DSN-less connection to a database. Instead of a database name, enter a string containing all of the parameters necessary to identify the database. The actual syntax depends on the database, but looks something like the following:
Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=True;User ID=MyUserID;Initial Catalog=Northwind;Data Source=MyDataServer
For example, using a DSN-less connection string in a setDBQuery statement might look like the following:
myImage.setDBQuery "graph","","Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=True;User ID=MyUserID;Initial Catalog=Northwind;Data Source=MyDataServer","","","select teamname, wins from Y1998"
Important: Username and password are set in the connection string, and left blank in the setDBQuery or setMapDBQuery statement.
Many ASP and ASP.NET developers use the global.asa file to specify data connections. setDBQuery and setMapDBQuery statements can leverage this file. The example below provides a sample global.asa connection string.
Global.asa Connection String
<script language="vbscript" runat="server">
Sub Application_OnStart
Application("myconnection_ConnectionString") ="Data Source=" & Server.Mappath("database.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"
End Sub
</script>
Using this sample global.asa connection string in a setDBQuery statement looks something like the following:
myImage.setDBQuery "graph","",Application("myconnection_ConnectionString"),"","","select teamname, wins from Y1998"
Important: Do not specify a user name or password in the setDBQuery statement. These are already specified in the global.asa file.