Hello
when i try to execute a query based on a callable statement for an Acces DB i get the error "not supported yet". Is there a plan for when it will be? I have some stored queries in an Acces DB that i cannot put into code.
Thanks
Matt
|
|
HXTT Access doesn't supports stored procedures for MS Access now, hasn't such a complement plan in the near future. But HXTT Access supports most of VIEW(QUERY of MS ACCESS) and Linked Table to other Access databases. If your stored queries are only some query, you can try "select * from queryName" to visit those data.
|
These Queries expect parameters. Can you tell me how to form a valid "SELECT * FROM query"-Statement?
One query for example is like:
INSERT INTO XY ( A, B, C )
SELECT ? AS Expr1, ? AS Expr2, ? AS Expr3;
How would the SQL-Statment for something like this look like?
Thanks :)
|
HXTT Access supports View without parameter since we have a good idea to pass parameter for View with parameter. Wha't the call method of jdbc-odbc bridge driver for MS Access?
I guess that you sql should be insert INSERT INTO XY ( A, B, C ) values(?,?,?);
|
Well normally it would be a callable statment like {call abcInsert} i think that instead you would like me to do something like
"INSERT INTO abcInsert(A,B,C) values(?,?,?);" where abcInsert is an MS-Access Query, right. Does this work with all SQL-Commands, like Delete or Update to?
With other words, can i treat an Access-Query as a normal table?
|
Well normally it would be a callable statment like {call abcInsert} i think that instead you would like me to do something like
"INSERT INTO abcInsert(A,B,C) values(?,?,?);" where abcInsert is an MS-Access Query, right. Does this work with all SQL-Commands, like Delete or Update to?
With other words, can i treat an Access-Query as a normal table?
|
Hi
i think i misunderstood you or we misunderstod each other. Therefor again:
In a DB (wich wasnt designed by me, and cannot be redesigned) are 4 stored queries, which are currently opend with the CallableStatement like "{call ADelete}", where "Delete" is just appended to the table name "A". There are also some for select, insert and update.
These Queries are looking like this:
1) "{call ASelect}" -> SELECT A.* FROM A;
2) "{call ADelete}" -> DELETE A.Modes A.* FROM A WHERE (((A.Modes)=[ID]));
3) "{call AUpdate}" -> INSERT INTO A ( Name, Size, Mode, Preset )
SELECT ? AS Expr1, ? AS Expr2, ? AS Expr3, ? AS Expr4;
4) "{call AInsert}" -> UPDATE A SET A.Name = [1], A.Size = [2], A.Mode = [3], A.Preset = [4] WHERE (((A.ID)=[ID]));
Therefor normal SQL-Commands stored in the DB. How can i execute them?
|
Now HXTT Access has supported most simple stored procedures. Please download the latest package, and see the below sample code:
aselect: SELECT A.* FROM A;
adelete: DELETE * FROM A WHERE ((([A].[Modes])=ID));
AUpdate: UPDATE A SET A.Name = [1], a.[Size] = [2], A.Mode = [3], A.Preset = [4]
WHERE ((([A].[Modes])=[ID]));
AInsert: INSERT INTO A ( Name, [Size], Mode, Preset ) SELECT Expr1, Expr2, Expr3, Expr4;
import java.sql.*;
import java.util.Properties;
public class testAccessCall1 {
public static void main(String argv[]) {
try {
Class.forName("com.hxtt.sql.access.AccessDriver");
String url = "jdbc:Access:/f:/mdbfiles";
Connection con = DriverManager.getConnection(url, new Properties());
con.setCatalog("hxtt.mdb");
CallableStatement cstmt = con.prepareCall("{?=call aselect}");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.execute();
ResultSet rs=(ResultSet)cstmt.getObject(1);
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++)
{
System.out.println(resultSetMetaData.getColumnLabel(i));
System.out.println("Name " + resultSetMetaData.getColumnName(i) + ": type " + resultSetMetaData.getColumnTypeName(i) + " isNullable " + resultSetMetaData.isNullable(i));
}
rs.beforeFirst();
while (rs.next()) {
System.out.print(rs.getRow() + ":");
for (int i = 1; i <= iNumCols; i++) {
Object colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
cstmt.close();
cstmt=con.prepareCall("{call aselect()}");
rs=cstmt.executeQuery();
resultSetMetaData = rs.getMetaData();
iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++)
{
System.out.println(resultSetMetaData.getColumnLabel(i));
System.out.println("Name " + resultSetMetaData.getColumnName(i) + ": type " + resultSetMetaData.getColumnTypeName(i) + " isNullable " + resultSetMetaData.isNullable(i));
}
rs.beforeFirst();
while (rs.next()) {
System.out.print(rs.getRow() + ":");
for (int i = 1; i <= iNumCols; i++) {
Object colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
cstmt.close();
cstmt=con.prepareCall("{?=call bselect(?)}");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.setString(2, "456");
cstmt.execute();
rs=(ResultSet)cstmt.getObject(1);
resultSetMetaData = rs.getMetaData();
iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++)
{
System.out.println(resultSetMetaData.getColumnLabel(i));
System.out.println("Name " + resultSetMetaData.getColumnName(i) + ": type " + resultSetMetaData.getColumnTypeName(i) + " isNullable " + resultSetMetaData.isNullable(i));
}
rs.beforeFirst();
while (rs.next()) {
System.out.print(rs.getRow() + ":");
for (int i = 1; i <= iNumCols; i++) {
Object colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
cstmt.close();
cstmt=con.prepareCall("{?=call adelete(?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "4567");
cstmt.execute();
int count=cstmt.getInt(1);
System.out.println(count);
cstmt=con.prepareCall("{call adelete(?)}");
cstmt.setString(1, "4567");
count=cstmt.executeUpdate();
System.out.println(count);
cstmt=con.prepareCall("{?=call aupdate(?,?,?,?,?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString("1", "name");
cstmt.setInt("2", 333);
cstmt.setString("3", "mode22");
cstmt.setString("4", "preset22");
cstmt.setString("ID", "9999");
cstmt.execute();
count=cstmt.getInt(1);
System.out.println(count);
cstmt=con.prepareCall("{call aupdate(?,?,?,?,?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString("1", "name");
cstmt.setInt("2", 333);
cstmt.setString("3", "mode22");
cstmt.setString("4", "preset22");
cstmt.setString("ID", "9999");
count=cstmt.executeUpdate();
System.out.println(count);
cstmt=con.prepareCall("{?=call ainsert(?,?,?,?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString("Expr1", "name222");
cstmt.setInt("Expr2", 2333);
cstmt.setString("Expr3", "mode33");
cstmt.setString("Expr4", "insert22");
cstmt.execute();
count=cstmt.getInt(1);
System.out.println(count);
cstmt=con.prepareCall("{call ainsert(?,?,?,?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString("Expr1", "name222");
cstmt.setInt("Expr2", 2333);
cstmt.setString("Expr3", "mode33");
cstmt.setString("Expr4", "insert22");
count=cstmt.executeUpdate();
System.out.println(count);
con.close();
}
catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}
while ( (sqle = sqle.getNextException()) != null);
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
Hello
First of all: Thank you!!! Just yesterday the question about CallableStatement came up and you timed it perfectly :)
unfortunally it wont work :'(
The Query i am looking up is in the DB but i get a SQLException:
Stored procedure ... doesn't exist.
Error Code:212992
SQL State:34000
do you need the DB?
Thanks
Matthias
|
Hello
First of all: Thank you!!! Just yesterday the question about CallableStatement came up and you timed it perfectly :)
unfortunally it wont work :'(
The Query i am looking up is in the DB but i get a SQLException:
Stored procedure ... doesn't exist.
Error Code:212992
SQL State:34000
do you need the DB?
Thanks
Matthias
|
|
You can zip and email it to webmaster@hxtt.com. If your table has sensitive data, you can backup it, remove all rows and compact/repair that database sample first.
|
Passed test. Please try the below sample.
import java.sql.*;
import java.util.Properties;
public class testAccessCall2 {
public static void main(String argv[]) {
try {
Class.forName("com.hxtt.sql.access.AccessDriver");
String url = "jdbc:Access:/f:/mdbfiles";
Connection con = DriverManager.getConnection(url, new Properties());
con.setCatalog("Specific.mdb");
CallableStatement cstmt = con.prepareCall("{call QSpecificBLTable}");
;
ResultSet rs=cstmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++)
{
System.out.println(resultSetMetaData.getColumnLabel(i));
System.out.println("Name " + resultSetMetaData.getColumnName(i) + ": type " + resultSetMetaData.getColumnTypeName(i) + " isNullable " + resultSetMetaData.isNullable(i));
}
rs.beforeFirst();
while (rs.next()) {
System.out.print(rs.getRow() + ":");
for (int i = 1; i <= iNumCols; i++) {
Object colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
cstmt.close();
PreparedStatement pstmt = con.prepareStatement("select * from QSpecificBLTable;");
;
rs=pstmt.executeQuery();
resultSetMetaData = rs.getMetaData();
iNumCols = resultSetMetaData.getColumnCount();
for (int i = 1; i <= iNumCols; i++)
{
System.out.println(resultSetMetaData.getColumnLabel(i));
System.out.println("Name " + resultSetMetaData.getColumnName(i) + ": type " + resultSetMetaData.getColumnTypeName(i) + " isNullable " + resultSetMetaData.isNullable(i));
}
rs.beforeFirst();
while (rs.next()) {
System.out.print(rs.getRow() + ":");
for (int i = 1; i <= iNumCols; i++) {
Object colval = rs.getObject(i);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
pstmt.close();
con.close();
}
catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}
while ( (sqle = sqle.getNextException()) != null);
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
Wont pass with this test...
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.access.n.char(Unknown Source)
at com.hxtt.sql.dv.a(Unknown Source)
at com.hxtt.sql.dv.(Unknown Source)
at com.hxtt.sql.bf.prepareCall(Unknown Source)
at com.hxtt.sql.bf.prepareCall(Unknown Source)
at com.hxtt.sql.bf.prepareCall(Unknown Source)
at testAccessCall2.main(testAccessCall2.java:25)
|
|
Please check whether you have download the latest package, becase I can pass that test.
|
Downloaded "Access JDBC 3.0 Package 899,927 2006-05-04 05:20"
But it still wont work...
|
|
You're right. Please download the latest package again. Thanks for your patience:)
|
Perfect... everything seems to works fine!
Next week the modifications will go into an extensive test, but i dont think i will encounter any more problems.
Thanks for your help and for implementing this feature!!!
CHINA RULES!!! :o)
|
Main
webmaster@hxtt.com