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
