Up to Discussions in Ask-the-Expert

This Question is Assumed Answered

1 "correct" answer available (5 pts) 15 "helpful" answers available (3 pts)
2 Replies Last post: Jul 10, 2008 8:27 AM by mgordon

MS SQL Server + Tomcat database configuration

Jun 30, 2008 11:20 AM

Click to view CalVic's profile Level 1 CalVic 7 posts since
Jun 27, 2008

Hi, I'm extremely new to Curl and I'm trying to write a database driven application but I'm having trouble setting it up.

I've been following "The Basic Database Server" from here: http://developers.curl.com/userdocs/docs/en/dguide/data-connection.html

So far I have SQLServer set up as my database management system and I've downloaded Tomcat as my java servlet engine. I downloaded the Microsoft SQLServer JDBC driver into the Tomcat dir as instructed. The CDBC .war file is also in the specified dir. Both the server.xml file and cdbc-server.xml have been modified word for word except for the user/pass.

For server.xml, three environment elements are added. With MS Sql Server, would something like this be correct?

"The first configuration parameter is cdbc/database. The value should be "mysql" or "oracle" depending on what database you are using with the CDBC servlet. It is used to select the Resource that will be used to configure the JDBC driver, either "cdbc-mysql" or "cdbc-oracle". You can use other values if you wish, provided you also supply a corresponding Resource element in the server.xml file and a ResourceLink element in the cdbc-server.xml file (see below). If unspecified, the default value is "mysql" and the resource "cdbc-mysql" is used to configure the JDBC driver."

<!-- CDBC configuration -->
<Environment name="cdbc/database"
type="java.lang.String"
value="sqlServer"
/>

<Environment name="cdbc/transaction-control"
type="java.lang.String"
value="mysql"
/>

<Environment name="cdbc/table-type"
type="java.lang.String"
value="TABLE,VIEW,SYNONYM"
/>

<Resource name="cdbc-sqlServer"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost/cdbc_test?useUnicode=true&characterEncoding=utf-8"
username="myUserName"
password="myPassWord"
maxActive="20"
maxIdle="10"
/>

There are obviously more things to change. But which ones? And to what? What would I modify in the cdbc-server.xml file?

<Context path="/cdbc-server" reloadable="false">

<Logger className="org.apache.catalina.logger.SystemOutLogger"
verbosity="4" timestamp="true"/>

<ResourceLink name="cdbc/transaction-control"
global="cdbc/transaction-control"
type="java.lang.String" />

<ResourceLink name="cdbc/table-type" global="cdbc/table-type"
type="java.lang.String" />

<ResourceLink name="jdbc/cdbc" global="cdbc-mysql"
type="javax.sql.DataSource" />

<ResourceLink name="jdbc/cdbc-mysql" global="cdbc-mysql"
type="javax.sql.DataSource" />

<ResourceLink name="jdbc/cdbc-oracle" global="cdbc-oracle"
type="javax.sql.DataSource" />

<ResourceLink name="cdbc/default-schema/oracle/en"
global="cdbc/default-schema/oracle/en"
type="java.lang.String" />

<ResourceLink name="cdbc/default-schema/oracle/ja"
global="cdbc/default-schema/oracle/ja"
type="java.lang.String" />

</Context>


Sorry for all the questions but it seems like a lot of the examples are very mySQL specific and not enough explanation is given for other database management systems. I'm a total newb. Also, any advice on what else I should do is helpful. Thanks.

Click to view hokada's profile Level 1 hokada 12 posts since
Jun 29, 2008
1. Re: MS SQL Server + Tomcat database configuration Jun 30, 2008 7:45 PM

As I have not tried on SQL Server, I don't know whether it does work or not. But you can try following step.

There are obviously more things to change. But which ones? And to what? What would I modify in the cdbc-server.xml file?
Can you add the following elements?

cdbc-server.xml
<ResourceLink name="cdbc/database" global="cdbc/database" type="java.lang.String" />
<ResourceLink name="jdbc/cdbc" global="cdbc-sqlServer" type="javax.sql.DataSource" />
<ResourceLink name="jdbc/cdbc-mysql" global="cdbc-sqlServer" type="javax.sql.DataSource" />

SQL server might need the following elements

server.xml
<Environment name="cdbc/default-schema/sqlServer" type="java.lang.String" value="your schema name" />

cdbc-server.xml
<ResourceLink name="cdbc/default-schema/sqlServer" global="cdbc/default-schema/sqlServer" type="java.lang.String" />

Click to view mgordon's profile Curl mgordon 48 posts since
Oct 17, 2007
2. Re: MS SQL Server + Tomcat database configuration Jul 10, 2008 8:27 AM
Here are a few more things to do. Let us know if you are still having problems. BTW, I put up a simple applet that lets you view database tables via a CDBC connection. You can use it to test your connection, and you can read the code to see how it works.

It's a good idea to use serialize? = true when you create your connection. This makes the connection faster and the data sent over the wire is much more compact. It turns out that this is required with Oracle. I'm not sure about MS Sql Server but it could be important.

let nc:BasicConnection = {BasicConnection {url server}, serialize? = true}


Also, use "jdbc" for transaction control in your server.xml file. The "mysql" value only works with MySql.

<Environment name="cdbc/transaction-control"
        type="java.lang.String"
        value="jdbc"
    />


We also found that you need to use upper case table names with Oracle because we aren't quoting table names in the SQL we generate to update the data on the server. This could be important with Sql Server too.

Actions

More Like This