Current Position:Home > Syntax of creating a stored procedure in Ms Access

Syntax of creating a stored procedure in Ms Access

Update:10-11Source: network consolidation
Advertisement
I need to create a stored procedure in Ms Access and i need to create it via a java program .This is the query i have written
CREATE PROCEDURE procProductsList As
Select A.Id,B.L_Name from Transaction A,Member B where A.Id = B.Id and A.Id = [@emp] and A.Name = [@name].But when i try to run it i get an error as Syntax error in CREATE TABLE statement.Plz could sumone help.

The Best Answer

Advertisement
//Creates a stored procedure as a resultset
boolean createStoredProcedure(String strSql,Connection connect)
Statement stmt=null;     
     int columnCount=0;
     try
          if(connect.isClosed()==true)
               return false;
               else
               stmt=connect.createStatement();
               stmt.executeUpdate(strSql);                                             
          } //End else
     } //End try
     catch(SQLException sql)
          System.out.print("Sql Exception caught");
          sql.printStackTrace();
          return false;
     } //End catch
     return true;
     } //End function
//Function for passing the number of parameters in the parameters list
String getInvokeParamsList(Vector vector)
     String strParamsInvokeList = new String("");
     if(vector==null)
strParamsInvokeList=null;
     else
          strParamsInvokeList += "(";
          for(int i=0; i < vector.size(); i++)
               strParamsInvokeList += "?";
               if(i < (vector.size() - 1))
                    strParamsInvokeList += ", ";
          strParamsInvokeList += ")";
     return strParamsInvokeList;
//Calls a stored Procedure
ResultSet callStoredProcedure(Connection connect,String strProcedureName,Vector vecValues)
     ResultSet rs=null;
     try
               CallableStatement cs = connect.prepareCall("{call " + strProcedureName + getInvokeParamsList(vecValues) + "}");
               for(int i=0;i<vecValues.size();i++)
               cs.setString(i+1,(String)vecValues.get(i));
               rs = cs.executeQuery();
catch(SQLException sql)
          System.out.print("Sql Exception caught: " + sql.toString());
          sql.printStackTrace();
catch(NullPointerException npexp)
          System.out.print("NullPointerException Exception caught: " + npexp.toString());
          npexp.printStackTrace();
     return rs;//End catch
class database1
          public static void main(String[] args)
               try
                    String strFilepath="C:\\trainee";
                    String strFilename="config.xml";
                    String strvendor="TICPEN0001003";
                    String str1="CREATE PROCEDURE procProductsList As Select A.Id,B.L_Name from Transaction A,Member B where A.Id = B.Id and A.Id = [@emp] and A.Name = [@name]";
                    String proc="stproc";
                    int columnCount=0;
                    clsDatabase clsdb=new clsDatabase(strFilepath,strFilename,strvendor);
                    Connection boolConn=clsdb.getConnect();
                    Vector vec=new Vector();
                    if(boolConn!=null)
                         System.out.println("Connection established");     
                         vec.add("3");
                    vec.add("Glove");
                         boolean b1=clsdb.createStoredProcedure(str1,boolConn);
                         if(b1==true)
                              ResultSet rs1=clsdb.callStoredProcedure(boolConn, proc, vec);
                                   if(rs1==null)
                                   System.out.println("rs1 null");
                              else
                                   ResultSetMetaData rsmd = rs1.getMetaData();                         
                                   columnCount = rsmd.getColumnCount();
                                   while(rs1.next())
                                        for(int i=1 ;i<=columnCount; i++)
                                             String value=rs1.getString(i);
                                             System.out.println(value);
                         else
                         System.out.print("Stored Procedure not created");
               else
                    System.out.println("Connection not established");      
catch(Exception e)
                    //System.out.println("Exception caught4: " + e.toString());
                    e.printStackTrace();
               }                                                                                          //End catch
     }                                                                                          //End main
}
  • Syntax of creating a stored procedure in Ms Access Update:10-11

    I need to create a stored procedure in Ms Access and i need to create it via a java program .This is the query i have written CREATE PROCEDURE procProductsList As Select A.Id,B.L_Name from Transaction A,Member B where A.Id = B.Id and A.Id = [@emp] an

  • How to create a stored procedure and use it in Crystal reports Update:10-11

    Hi All, Can anyone explain me how to create a stored procedure and use that stored procedure in Crystal reports. As I have few doubts in this process, It would be great if you can explain me with a small stored proc example. Thanks in advance.If you

  • How to create a stored procedure that contains all 3 AFTER Triggers Update/Insert/Delete ? Update:11-30

    Hi guys, I'm trying to create a Stored procedure that will automatically add all 3 After triggers when executed on any given database, can someone please explain and give an example on how do I go about doing this ? I'd also like it to raise any erro

  • Pointbase : How can I create a stored procedure with Pointbase database? Update:10-11

    Hello, Excuse me for my english, I'm not anglophone. I try to create a stored procedure. This is my file SampleExternalMethods.java :   import java.sql.*;    //import com.pointbase.jdbc.jdbcInOutDoubleWrapper;          public class SampleExternalMeth

  • Can I create a Stored Procedure That access data from tables of another servers? Update:10-11

    I'm developing a procedure and within it I'm trying to access another server and make a select into a table that belongs to this another server. When I compile this procedure I have this error message: " PLS-00904: insufficient privilege to access ob

  • Problems in creating RLANG stored procedure in HANA studio Update:10-11

    Hi, While I am creating a stored procedure (using .hdbprocedure file) in HANA studio with language as RLANG, I am getting an error message like this: "It is not supported to use another language than sqlscript". I have the following setting conf

  • ERP2005 SR3 Create SAP Stored Procedures Error Update:11-30

    Hi Seniors, I have tried to install SAP ERP ECC 6.0 IDES ERP2005 SR3 , I got the error in installation step 19/26 (Create SAP Stored Procedures). OS : WIN 2008 SERVER DB : MS SQL 2008 I have fallow the Note 1152240. Error log : ERROR 2010-03-02 12:08

  • How to create a stored procedure in a dynamic schema Update:11-30

    Hi, I want to create a stored procedure in a dynamic shcema (schema name will be known at run time). Can any one guide me on this. I tried using define var1=schema1; and then in create or replace procedure &var1.porcName( ) but it is not working Than

  • Beginner - trying to create a stored procedure. Update:11-30

    I want to create a stored procedure, where the person calling the procedure only has to up date the date_key : The procedure is just creating a table and will be ran every couple days. Create or replace procedure WklyHVCReport (date_transaction numbe

  • How to use dynamic parameter when a report is created using Stored Procedures Update:11-30

    Hi all, any one have the idea of how to use dynamic parameter in crystal report XI R2 when report is created using Stored Procedure. Regards shashi kant chauhanHi You can create an SQL command in Database Expert > Expand your datasource > Add comman