Hey Claire,
I feel your pain! I have had to deal with just this sort of situation
migrating a database from DB2 to Oracle. I have encountered it across
multiple platforms and databases (Windows, Linux, AIX; SQL Server, Oracle,
DB2). It is definately NOT specific to SQL Server.
The behaviour we are experiencing is due to the way RDBMS's are laid out.
Each table is contained within a larger container (sometimes called a
schema, sometimes called a catalog). You can directly access tables within
your own {schema|catalog} (normally the same as your username). For
others, you need to prepend the {schema|catalog} name, as you have found
out.
I have not found a particularly good way around this that is portable even
to just the databases we use internally. The JDBC DataSource for DB2 8.x
supports a 'currentSchema' property, but it doesn't work with DB2 7.x.
There is a setCatalog() method in the java.sql.Connection interface, but I
haven't been able to get it to work with any of the databases we have on
hand.
One possible approach might involve using some sort of persistence
mechanism such as Container-Managed Persistence (CMP) Entity EJB's, JDO or
Hibernate. You will still have to hardcode the {schema|catalog}, but by
seperating the SQL from the Java source, it should minimize the impact of
moving to a different {catalog|schema}.
HTH,
Ian
"Claire Wall"
<[EMAIL PROTECTED] To: "Struts Users Mailing List"
<[EMAIL PROTECTED]>
tosys.com> cc:
Subject: Re: microsoft sqlserver driver
& struts
02/27/2004 10:46
AM
Please respond
to "Struts Users
Mailing List"
ok i've manager to connect to the datasource! What I did was to move my
jars
into common/lib.
Now I have another problem though. For some reason this driver does not
like
my queries - more specifically it does not like table names used on their
own but only likes them when the database name and database user is
inserted
before it like 'dbname.dbo.user_table'. Why is this? I cannot change all my
queries (and there are alot of them) so that they have this prefix as it
will cause many problems if the application has to be ported to another
database. Is this an SQL Server database setting, or something to do with
the driver itself?
I know I'm getting off the struts issue now but any help would be great! :)
----- Original Message -----
From: "Claire Wall" <[EMAIL PROTECTED]>
To: "Struts Users Mailing List" <[EMAIL PROTECTED]>
Sent: Friday, February 27, 2004 3:19 PM
Subject: Re: microsoft sqlserver driver & struts
> Ian,
>
> This is the way I tried first as it seemed most logical to use the
> SQLServerDataSource type from the mssqlserver.jar but this doesnt work
for
> me. The only one that made some progress was the
> com.microsoft.jdbcx.base.BaseDataSource but then this threw some strange
> error which said:
>
> java.lang.NoClassDefFoundError: com/merant/jdbcspy/SpyLogger
> at com.microsoft.jdbcx.base.BaseDataSource.setLogWriter(Unknown Source)
>
> I've had a look into this but I don't see why this error should be
occuring.
> Maybe there's something wrong I have done with where I've put the jars..
I
> have three jars: mssqlserver.jar, msbase.jar and msutil.jar. the last two
> look to be pretty similiar with regards to what they contain. I was
> wondering whether this could be causing conflicts or something.
>
> As I said, i can connect fine to the datasource from a test class but
going
> via struts is causing this Invalid DataSource problem.
>
> Claire
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "Struts Users Mailing List" <[EMAIL PROTECTED]>
> Sent: Friday, February 27, 2004 3:02 PM
> Subject: Re: microsoft sqlserver driver & struts
>
>
> > Hi Claire,
> >
> > We are successfully connecting to a Microsoft SQL Server 2000 using the
> > type 4 JDBC drivers. However, we do not use DBCP, but rather the
> > DataSource implementation provided by Microsoft. Here is the
> configuration
> > we use (this is taken from an internal configuration file that we use
to
> > create the DataSource's from an AXIS handler for our web services, not
> > struts-config.xml, but the same properties should work)
> >
> > <datasource name="peregrine" type
> > ="com.microsoft.jdbcx.sqlserver.SQLServerDataSource">
> > <serverName>XXX</serverName>
> > <databaseName>Peregrine</databaseName>
> > <portNumber>1433</portNumber>
> > <loginTimeout>60</loginTimeout>
> > <user>XXX</user>
> > <password>XXX</password>
> > </datasource>
> >
> > Also, if you need to connect to the same database across multiple web
> apps,
> > you want to look into using JNDI instead of the struts-config.xml file.
> >
> >
> > HTH,
> > Ian
> >
> >
> >
> >
> > "Claire Wall"
> > <[EMAIL PROTECTED] To: "Struts Users
Mailing
> List" <[EMAIL PROTECTED]>
> > tosys.com> cc:
> > Subject: microsoft
> sqlserver driver & struts
> > 02/27/2004 05:34
> > AM
> > Please respond
> > to "Struts Users
> > Mailing List"
> >
> >
> >
> >
> >
> >
> > Hi,
> >
> > I am trying to use microsoft's jdbc driver with my struts application,
but
> > to no avail. Here is my datasource definition:
> >
> > <data-source key="DB" type="org.apache.commons.dbcp.BasicDataSource">
> > <set-property property="description" value="My SqlServer pool"/>
> > <set-property property="driverClassName" value
> > ="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
> > <set-property property="url" value
> > ="jdbc:microsoft:sqlserver://SERVERNAME:1433;DatabaseName=DBNAME"/>
> > <set-property property="username" value="xxx"/>
> > <set-property property="password" value="xxx"/>
> > <set-property property="maxActive" value="20"/>
> > <set-property property="maxCount" value="20"/>
> > <set-property property="minCount" value="2"/>
> > <set-property property="maxWait" value="5000"/>
> > <set-property property="defaultAutoCommit" value="false"/>
> > <set-property property="defaultReadOnly" value="false"/>
> > </data-source>
> >
> > I have the required jars located in the WEB-INF/lib of my application
> which
> > is running on Tomcat 4.1.29. When i try to connect to the database
using
> > this driver from a test class, it connects no problem so i know that
the
> > url and driver class name are correct. So the problem must be the type
of
> > DataSource which I am to use. Does anybody know which datasource to use
> > with Microsoft's sqlserver driver? I tried using the DataSource that is
in
> > the mssqlserver jar but this didnt work either.
> >
> > The error that I get is an Invalid DataSource.
> >
> > Any help would be really appreciated!
> >
> > Thanks
> > Claire
> >
> >
> >
> >
> >
> >
> >
> > This transmission may contain information that is privileged,
confidential
> and/or exempt from disclosure under applicable law. If you are not the
> intended recipient, you are hereby notified that any disclosure, copying,
> distribution, or use of the information contained herein (including any
> reliance thereon) is STRICTLY PROHIBITED. If you received this
transmission
> in error, please immediately contact the sender and destroy the material
in
> its entirety, whether in electronic or hard copy format. Thank you.
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
This transmission may contain information that is privileged, confidential and/or
exempt from disclosure under applicable law. If you are not the intended recipient,
you are hereby notified that any disclosure, copying, distribution, or use of the
information contained herein (including any reliance thereon) is STRICTLY PROHIBITED.
If you received this transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard copy format. Thank
you.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
