Accessing database Variables using JDBC
Hi,
I have a question for some of you Java specialists out there.
I want to execute a select statment in my database (SQL Server 2000) and store the output in a variable. - The reason I am not directly executing the SQL statement from my JDBC connection is that I need to execute a number of SQL statements and then finally would like to store my result set in a variable in the database - NOW, my question is , how do I (If I can) access a database variable using either servlets or JSP. Any help is welcome.
A quick response would be much appreciated.
thanks
You can't.
A variable has a fixed scope. When you are in java you are outside of that scope.
Now if you want to return a value in a parameter to a stored procedure, or as a result set that is a different matter.
A result set might look like something like this at the end of the SQL block.
select myvariable;
Thanks for the help.
By, "Now if you want to return a value in a parameter to a stored procedure, or as a result set that is a different matter." what exactly do you mean -
could you please elaborate on that. Are we talking about SQL stored procedures here. Also, are you saying that we can return the value of a parameter (SQL parameter) using the resultset
But you did say that we CANNOT return the value of a SQL variable to Java in any way - why I had posted the question was because you can do this by using ASP apparently - see SQL variables and read their values.
thanks in advance
We are probably talking about different things.
ASP, at least when I used consisted of the following:
-html
-server side script (vbscript, javascript, perlscript)
-client side script (usually javascript but it could be vbscript and perlscript)
In the above you have three possible kinds of variables java, perl, vb. In each of those languages you can use SQL text to manipalate data in the database. However the text itself never ran in the ASP server nor was there such a thing a SQLscript, so there was nothing that was a SQL variable.
However, if I write a stored procedure in Oracle, it can definitely have variables. And likewise a oracle package can have variables. But there is no way to directly use those variables in ASP. The value of the variable would have to be returned to the ASP script language and a variable in tha language could contain that value.
Perhaps I just don't understand ASP or it has evolved in the last 3 years. If so then ignore everthing I have said.
Hi again,
You mentioned and I quote
"" The value of the variable would have to be returned to the ASP script language and a variable in tha language could contain that value.""
So are you saying that we can maybe declare a global SQL variable and return the value of that variable to a JSP/servlet or ASP script variable.
>So are you saying that we can maybe declare a
>global SQL variable and return the value of
>that variable to a JSP/servlet or ASP script variable.
Yes.
For example you can have a package variable in an Oracle package. To return the current value of that you write a stored proc. The stored proc could return that variable either as a result set, a 'out' variable or a return value.
what jschell is explaining is that you can make a call to a stored procedure with in parameters, process many sql statements or do whatever you need to accomplish in the stored procedure, then return out parameters back to the java program. The out parameters can include a cursor, string, int, etc. from the stored procedure. You can then process the out parameters when they are returned to the java program. It is similar to a method call where you can have in parameters and return values.
hope this makes things clear as mud!!
Jamie
