xie zhi
To execute a dynamic sql statement, you just need to change some of the
Java code. You don't even need the class query if you always want
dynamic queries.
Here is the new Java code:
/*
* CachePersentG.java
*
*/
import java.io.*;
import java.util.*;
import com.intersys.objects.*;
import java.sql.*;
public class CacheQueryPersentG{
public static void main(String[] args){
try {
String url="jdbc:Cache://localhost:1972/USER";
String username="_SYSTEM"; // null for default
String password="sys"; // null for default
//CacheQuery cq;
Statement stmt;
java.sql.ResultSet rs;
Database dbconnection = CacheDatabase.getDatabase (url,
username, password);
/* Create a ResultSet */
System.out.println( "Creating a ResultSet" );
/* Create a CacheQuery */
//cq = new CacheQuery( dbconnection, User.TestStoredProc1","G");
stmt = dbconnection.createStatement();
InputStreamReader isr = new InputStreamReader(System.in);
BufferedReader br = new BufferedReader(isr);
System.out.print("Enter SQL statement: ");
String gn = br.readLine();
/* Execute the query and loop across the returned rows */
System.out.print("gn: " + gn);
//rs = cq.execute(gn);
rs = stmt.executeQuery(gn);
System.out.print("execute(gn): ok!");
ResultSetMetaData rsmd = rs.getMetaData();
System.out.print("getMetaData: ok!");
int colnum = rsmd.getColumnCount();
while (rs.next()) {
for (int i=1; i<=colnum; i++) {
System.out.print(rsmd.getColumnName(i) + " ");
}
System.out.println();
for (int i=1; i<=colnum; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.println();
}
dbconnection.close();
} catch (Exception ex) {
System.out.println("Caught exception: " +
ex.getClass().getName()
+ ": " + ex.getMessage());
}
}
}
xie zhi wrote:
> I was requested to make a procedure which can execute sql and return result.
> I got the sample( Class User.TestStoredProc1) ,but I did not Know COS,the
> sample
> only can take the name of global or array? and how to do a procedure which
> can
> execute sql and return result?
>
> Thanking you for your kind assistance and looking forward to hearing form
> you soon .
>
> xie zhi
>
>
> "Ben Taylor" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
>>Xie
>>I'm not sure what you were intending to do, but it looks like from your
>>code, you have it correct. But, you pass in an SQL statement as an
>>argument, and your stored procedure is meant to take the string name of
>>a global, or at least a local array? Are you simply wanting to pass in
>>sql strings and have them executed? If so, there is surely a simpler way
>>to do that.
>>
>>Ben Taylor
>>MPLNet, Inc.
>>
>>xie zhi wrote:
>>
>>>I'm sorry for the size of the download is out of proportion to the
>
> problem.
>
>>>I replace the file and ask again
>>>
>>>
>>> I try to build an stored procedure with a Dynamic SQL Query...but i
>
> have
>
>>>problems
>>>with the syntax (i don`t know the right statement).The procedure like
>
> below
>
>>>----------------------------------------------------------------------
>>>Class User.TestStoredProc1 Extends %RegisteredObject [ ProcedureBlock ]
>>> {
>>> // WARNING! Cache CDL doesn't support PROJECTION. Please review
>>>
>>> Property glvn As %String;
>>>
>>> ClassMethod GClose(QHandle As %Binary) As %Status
>>> {
>>> Set QHandle=""
>>> Quit $$$OK
>>> }
>>>
>>> ClassMethod GExecute(ByRef QHandle As %Binary, glvn As %String) As
>>>%Status
>>> {
>>> Set QHandle=$LB("",glvn)
>>> Quit $$$OK
>>> }
>>>
>>> ClassMethod GFetch(ByRef QHandle As %Binary, ByRef Row As %List,
>
> ByRef
>
>>>AtEnd
>>> As %Integer = 0) As %Status
>>> {
>>> Set Row=""
>>> ; pull data out of QHandle
>>> Set x=$List(QHandle,1)
>>> Set glvn=$List(QHandle,2)
>>> ; $Order to next node
>>> Set x=$O(@glvn@(x))
>>> ; update QHandle for next fetch
>>> Set QHandle=$LB(x,glvn)
>>> ; test for end
>>> If x="" Set AtEnd=1 Quit $$$OK
>>> ; fill in Row with data for this row
>>> Set Row=$LB($na(@glvn@(x)),@glvn@(x))
>>> Quit $$$OK
>>> }
>>>
>>> ClassMethod MExecute(command As %String) As %String
>>> {
>>> Set %value = ""
>>> If $Extract(command,1)="=" {
>>> Set command = "Set %value "_command
>>> }
>>> Xecute command
>>> Set value = %value
>>> Kill %value
>>> Quit value
>>> }
>>>
>>> Query G(glvn As %String) As %Query(CONTAINID = 0, ROWSPEC =
>>>"Node:%String,
>>> Value:%String") [ SqlProc ]
>>> {
>>> }
>>>
>>>}
>>>----------------------------------------------------------------------
>>>
>>> call it in java(CacheQueryPersentG.java),it occured a Fatal error
>>>
>>>error message:
>>>----------------------------------------------------------------------
>>>Caught exception: com.intersys.objects.CacheServerExecption: Caught
>>>SQLException:
>>> [SQLCODE: [SQlCODE: <-400>:<Fatal error occurred>]
>>>[Cache Error: <<SYNTAX>zGFetch+5^user.TestStoredProc1.1>]
>>>[Details: <ZU151Error>],ErrorCode=400,SQlState=S1000 caused by:
>>>java.sql.SQLException:
>>> [SQLCODE: <-400>:<Fatal error occurred>]
>>>[Cache Error: <<SYNTAX>zGFetch+5^user.TestStoredProc1.1>]
>>>[Details: <ZU151Error>]Underlying exception: java.sql.SQLException:
>>>[SQLCODE: <-400>:<Fatal error occurred>]
>>>[Cache Error: <<SYNTAX>zGFetch+5^user.TestStoredProc1.1>]
>>>[Details: <ZU151Error>]
>>>----------------------------------------------------------------------
>>>
>>>CacheQueryPersentG.java like below
>>>----------------------------------------------------------------------
>>>/*
>>> * CachePersentG.java
>>> *
>>> */
>>>import java.io.*;
>>>import java.util.*;
>>>import com.intersys.objects.*;
>>>
>>>import java.sql.*;
>>>
>>>public class CachePersentG{
>>>
>>> public static void main(String[] args){
>>> try {
>>>
>>> String
>>>url="jdbc:Cache://localhost:1972/ISJSAMPLES";
>>> String username="_SYSTEM"; // null for
>
> default
>
>>> String password="sys"; // null for default
>>> Class.forName ("com.intersys.jdbc.CacheDriver");
>>> Connection dbconnection =
>>>DriverManager.getConnection(url, username, password);
>>> CallableStatement cstmt =
>
> dbconnection.prepareCall("{call
>
>>>SQLUSER.TestStoredProc1_G(?)}");
>>> InputStreamReader isr = new
>>>InputStreamReader(System.in);
>>> BufferedReader br = new BufferedReader(isr);
>>> System.out.print("Enter sql: ");
>>> String gn = br.readLine();
>>> cstmt.setString(1, gn);
>>> java.sql.ResultSet rs = cstmt.executeQuery();
>>> ResultSetMetaData rsmd = rs.getMetaData();
>>>
>>> int colnum = rsmd.getColumnCount();
>>> while (rs.next()) {
>>> for (int i=1; i<=colnum; i++) {
>>>
>>>System.out.print(rsmd.getColumnName(i) + " ");
>>> }
>>> System.out.println();
>>>
>>> for (int i=1; i<=colnum; i++) {
>>> System.out.print(rs.getString(i)
>
> + "
>
>>>");
>>> }
>>> System.out.println();
>>> }
>>>
>>> dbconnection.close();
>>> } catch (Exception ex) {
>>> System.out.println("Caught exception: " +
>>> ex.getClass().getName()
>>> + ": " + ex.getMessage());
>>> }
>>> }
>>>
>>>}
>>>----------------------------------------------------------------------
>>>
>>> how i must write the query ! I have searched in the manuals - but i
>
> can't
>
>>>find any example.
>>> anyone can help me?
>>>
>>> Thanks for helps!
>>> Best Regards
>>> xie zhi
>>>
>>>
>>>
>>>
>>>
>
>
