Passing DB Name/Owner Name in a prepareCall statement

When calling a stored procedure in sql server passing just the Stored
Procedure name, my stored procedure name works correctly. Below is
the String I use when performing Connection.prepareCall(String)
{CALL sp_name (?,?,?)}
However when I want to specify the db name and the user name (name of
the user who owns the sp), I get an error that the driver could not
find the stored procedure. Any clues as to how this could be fixed?
In other words, how can I set the default database name and owner name
to query against?
{CALL dbName.username.sp_name (?,?,?)}
Ryan wrote:

> When calling a stored procedure in sql server passing just the Stored
> Procedure name, my stored procedure name works correctly. Below is
> the String I use when performing Connection.prepareCall(String)
> {CALL sp_name (?,?,?)}
> However when I want to specify the db name and the user name (name of
> the user who owns the sp), I get an error that the driver could not
> find the stored procedure.
It's the DBMS, not the driver, that can or cannot find a stored procedure.
Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
help.
Any clues as to how this could be fixed?
> In other words, how can I set the default database name and owner name
> to query against?
> {CALL dbName.username.sp_name (?,?,?)}
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Ryan wrote:

> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...
>
> I get the following exception:
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]The procedure name
> 'dbName.username1.dbName.userName2.' contains more than the maximum
> number of prefixes. The maximum is 3.
> The call I pass to Connection.prepareCall is:
> {CALL dbName.username2.sp_name (?,?,?)}
> where dbname = the name of the database that I logged into. Username1
> is the username that I logged in with. Username2 is the username of
> the owner of the stored procedure and sp_name is the name of the
> stored procedure.
very odd. Does this code work for you?
It does for me... (logging in as a non-sa user...)
Statement s = c.createStatement();
s.execute("use tempdb");
s.execute("exec master.dbo.sp_who");
PreparedStatement p = c.prepareStatement("{ call master.dbo.sp_who() }");
p.execute();
CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who() }");
cl.execute();
|||Thanks Joe,
I found the culprit in an extended class. It was prepending a default
databaseName.ownerName. causing this "weird" behavior. A wasted
thread,
{CALL dbname.ownername.sp_name (?,?,?)}
will work.
Joe Weinstein wrote:[vbcol=seagreen]
> Ryan wrote:
news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
Stored[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
not[vbcol=seagreen]
procedure.[vbcol=seagreen]
that will[vbcol=seagreen]
name[vbcol=seagreen]
Username1[vbcol=seagreen]
of
> very odd. Does this code work for you?
> It does for me... (logging in as a non-sa user...)
> Statement s = c.createStatement();
> s.execute("use tempdb");
> s.execute("exec master.dbo.sp_who");
> PreparedStatement p = c.prepareStatement("{ call
master.dbo.sp_who() }");
> p.execute();
> CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who()
}");
> cl.execute();