What is the Java equivalent to SQL user defined table type?

Hi all,

I am calling a stored procedure on an SQL Server.
One of it's input parameters is a user defined table.
It is called udt_UsersById and its sole column is defined as
UserID(PK, int, not null)

Open in new window


declare @p2 dbo.udt_UsersById
insert into @p2 values(4668321)

Now, if I want to initialize this parameter which java SQL type should I use?

Many thanks, Tal
Tal TeneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Seems like poor database design to have a user-defined table with a single value, and nothing else, but oh, well.,.

How are you interacting with the database?  I am assuming JDBC, but there are a few different approaches that you can take.
0
Tal TeneAuthor Commented:
Hi Bob,

I can agree with you about the poor design, but it is not up to me...

Yes, I use JDBC CallableStatement to call something like:

{ call spProcName(?,?,?,?) }

And then I use the SetString, SetInt, setTimestamp (etc.) methods.
0
Bob LearnedCommented:
I believe what you are looking for with JDBC is the Object type and the setObject.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tal TeneAuthor Commented:
1. I am really surprised that there is no any standard solution for that. After all, I guess I am not the first one who needs to implement a UDT SP parameter using Java...
2. You said that there might be better ways to acheive that. Which are they?
0
Bob LearnedCommented:
Let me first say that I am not a Java "expert"--I only have one year of experience, but I always learn something easier when trying to answer questions, and by trying different things.  This was an abandoned question, which means that it didn't attract the attention of the real experts.  I am always open to helping others get their own answers.

JDBC seems to be the preferred method with Java.  I wasn't trying to suggest that there is a better way, I was trying to say that there are different ways, like using Spring.
0
Tal TeneAuthor Commented:
Thank you Bob for your effort.
I will check the SetObject method.
I just wonder why I had so few views for the question, and why only you out of all the experts that were promised to my before signing the premium account had the cortusey to try and help me.
But I guess this is not your problem... :-)
0
Bob LearnedCommented:
There are different explanations about why questions are abandoned.  I have been participating since 1999, and I have heard it all.

Biggest reasons are interest and timing.  The business model for E-E is to motivate people to answer questions.  If you are a contributing expert, it is free.  I haven't ever paid for services.  I have answered questions that I had no idea going in, so if you have a logical approach, nothing should be out of reach.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Tal,
I'm not a JAVA developer but a SQL Server guy and just trying to help you finding an alternative.
Can you post the Stored Procedure header?
Also, how in JAVA do you store a table to a variable?
0
Bob LearnedCommented:
I am not really a big fan of JDBC and the callableStatement.

Here is a reference for mapping:

Mapping SQL and Java Types
http://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html
0
Tal TeneAuthor Commented:
Vitor -

1. The SP header is as follows:

ALTER PROCEDURE [dbo].[spUpdateConference]
.
.
.
	@nComputerId int = null,
	
	/*Conference users*/
	@usersList udtUsers READONLY,
	
	/*Conference rooms*/
	@roomList udtRooms READONLY,
	
	/*Output*/
	@nStatusCode int output
AS
.
.
.

Open in new window


(I omitted many others irrelevant parameters...)

2. "how in JAVA do you store a table to a variable?" - Well, this is the "Mother of all questions" !  This is where I need you help... :-)

Bob -  You say that you "only have one year of experience" and still you already don't like
JDBC and the callableStatement... :-)   I am familar with the link you provided, but there is no single word there about user defined tables... :-(
0
Bob LearnedCommented:
I have 13 years experience with .NET, C#, and VB.NET, and only 1 year experience with java and JDBC, which is enough for me to know that I don't like JDBC very much.

How about Java DB?

Java User Defined Types (UDT) in Java DB
http://www.javacodegeeks.com/2013/10/java-user-defined-types-udt-in-java-db.html

I have zero knowledge 'bout Java DB, but it might be a better choice.
0
Tal TeneAuthor Commented:
Thank you Bob,
I will check Java DB :-)
+++++++++++++++++

Checked it - I can't be sure that it supports SQL Server.
0
Bob LearnedCommented:
Fair enough...I misread it...

Java DB is a relational database management system
not a database connectivity component.

Reading this now:

Using Customized Type Mappings
https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.