Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT ACCESS
Callable Statement
Matthias Becker
2006-04-21 04:42:20.0
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
Re:Callable Statement
HXTT Support
2006-04-21 04:49:43.0
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.
Re[2]:Callable Statement
Matthias Becker
2006-04-21 06:55:40.0
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 :)
Re:Re:Re:Callable Statement
HXTT Support
2006-04-21 08:09:09.0
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(?,?,?);
Re[4]:Callable Statement
Matthias Becker
2006-04-21 14:05:20.0
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?
Re[4]:Callable Statement
Matthias Becker
2006-04-21 14:15:31.0
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?
Re[5]:Callable Statement
Matthias Becker
2006-04-24 04:36:57.0
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?
Re:Re:Re:Re:Re:Re:Re:Callable Statement
HXTT Support
2006-05-04 05:18:18.0
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();
}
}
}




Re[6]:Callable Statement
Matthias Becker
2006-05-05 02:41:13.0
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
Re[6]:Callable Statement
Matthias Becker
2006-05-05 02:45:58.0
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
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
HXTT Supports
2006-05-05 02:48:42.0
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.
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
Hxtt Support
2006-05-05 03:23:30.0
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();
}
}
}

R[9]:Callable Statement
Matthias Becker
2006-05-05 04:05:47.0
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)
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
HXTT Support
2006-05-05 04:33:53.0
Please check whether you have download the latest package, becase I can pass that test.
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
Matthias Becker
2006-05-05 04:49:45.0
Downloaded "Access JDBC 3.0 Package 899,927 2006-05-04 05:20"
But it still wont work...
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
HXTT Support
2006-05-05 05:15:02.0
You're right. Please download the latest package again. Thanks for your patience:)
Re:Re:Re:Re:Re:Re:Re:Re:Callable Statement
Matthias Becker
2006-05-05 05:53:27.0
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)

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap