Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Help! Java method argument into prepared statement

    Hello.

    I have only being doing Java for a week and have an course assignment to create a prototype for a Fault Report Desk. I have chosen to use a MS Access DB instead of multidimensional arrays to show alternative methods.

    I can create and connect to my database using a Java constructor
    I can also populate the database with sample data by using defined figures by using a constructor.

    I am trying to develop it further by creating a Java method that will also add a new record to my database however it will use 'user inputted variables' or method arguments.

    This will hopefully give me a bit of success into how to change a DB field called 'AssignedEngineer' when 'FaultNo' = x

    My db name = ServiceDeskDB.mdb
    table to be updated = ClassNetStore

    My current constructor that creates a sample input to my database and works is:

    Code:
    public static void populateDBwithSampleData ()
        {
            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection con = DriverManager.getConnection("jdbc:odbc:ServiceDeskDB.mdb");
                Statement stmt = con.createStatement();
    
                       
                stmt.executeUpdate("INSERT INTO ClassNetStore " + "(DateOfIncident, TimeOfIncident, Priority, ReportedBy, ReportedInPerson, ReportedByEmail, ReportedByTelephone, ReportedByWWW, Appointment, ContactEmail, ContactTelephone, AssignedEngineer, Status, DTGCleared, Manufacturer, Model, Location, AssetNumber, Fault) VALUES ('03/01/12', '14:32', 'High', 'User1', No, Yes, No, No, 'Student1', 'student1@classnet.com', '1234', 'Service Desk Operator', 'Raised','', 'Sony', 'Vaio', 'P10', 'A123456','Blue Screen of Death')"); 
      
    con.close();
    }
    catch( Exception e) 
           {
                System.out.println(e.getMessage());
                e.printStackTrace();
            }   
    
        }
    I am hoping that someone may be able to give me some direction in how to create a 'Method' that requests all of the above values and uses them as variables.

    I am hoping to create something that may for example say
    Code:
      public int updateAssignedengineer(int x, String y)
    //where x = faultnumber to be updated and y = engineer name
    UPDATE ClassNetSore
    SET AssignedEngineer = 'y'
    WHERE FaultNo = 'x';
    Any help or assistance would be greatly appreciated.

    Thank you very much in advance

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    What have you tried with this so far?
    Also, are you looking to create this as something more generic, or is it still called under specific method names with defined signatures? Generic will be more work to do.

  • #3
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have tried to use to java api prepared statement example which relates to coffee sales.

    Ideally I would like to make a method that passes enough arguments into the SQL to populate a full row. Basically like my code that I have put in the top code box which populates the entire database row with fixed data but by using passed arguments instead.

    The ability to create a method which updates the 'AssignedEngineer' field within the database table for a particular 'faultno' with the new assigned engineer submitted by the engineer is my main aim.

    Once I have one example I will be able to copy and modify the method signatures to update the other fields.

    Thankyou very much for the help so far.

    I appreciate it

  • #4
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    any time you call SQL you need to know how many arguments it is expecting (if you are using a stored proc) and if not using a stored proc then you just pass the argument...
    maybe this will make more clear...
    Code:
    SQL PROC
    @col
    @table
    select @col from @table
    Code:
    call proc
    string myCol = "users";
    string myTable = "tbl_accounts"
    // idk too much java db connection stuff so ill demo ado.net
    // and just the param part...
    cmd.Parameters.Add(new SqlParameter("@col",mycol));
    cmd.Parameters.Add(new SqlParameter("@table",myTable));
    Code:
    and if just doing straight command (ie no stored proc)
    string myCol = "users";
    string myTable = "tbl_accounts"
    string myQuery = "select " + myCol + " from " + myTable
    or am I missing your question....?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #5
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much for your help. I am much closer now.

    There are 2 arguments in question
    The 'fault number' and the 'new engineers name to be assigned' to the AssignedEngineer column.

    So update the AssignedEngineers name to whatever was submitted by the user (as an arguments within the method signature) WHERE the FaultNumber = the other argument submitted by the user.

    Thank you very much

  • #6
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am not sure if it makes it any clearer but so far my code is

    Code:
    public int updateAssignedengineer(int x, String y)
        {
            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection con = DriverManager.getConnection("jdbc:odbc:ServiceDeskDB.mdb");
                Statement stmt = con.createStatement();
                stmt.execute("SET AssignedEngineer  to 'y' FROM ClassNetStore WHERE FaultNo ='x' ");
                con.close();
            }
            catch (Exception err) {
                System.out.println("ERROR: " + err);
            }
    
    
        }
    }
    //I know that i don't have a return statement in there but i am unsure what to put.
    From my initial post the top code box has a line which populates the full database using fixed strings in a constructor.

    Code:
    stmt.executeUpdate("INSERT INTO ClassNetStore " + "(DateOfIncident, TimeOfIncident, Priority, ReportedBy, ReportedInPerson, ReportedByEmail, ReportedByTelephone, ReportedByWWW, Appointment, ContactEmail, ContactTelephone, AssignedEngineer, Status, DTGCleared, Manufacturer, Model, Location, AssetNumber, Fault) VALUES ('03/01/12', '14:32', 'High', 'User1', No, Yes, No, No, 'Student1', 'student1@classnet.com', '1234', 'Service Desk Operator', 'Raised','', 'Sony', 'Vaio', 'P10', 'A123456','Blue Screen of Death')");
    The fault number is not shown in the above line of text as it is an autonumber field created by the data base. This code is also shown in the initial post (within the first code field).

    Am i miles away from achieving a method that would allow me to change values using method arguments?

    Thank you.

  • #7
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    stmt.execute("SET AssignedEngineer to 'y' FROM ClassNetStore WHERE FaultNo ='x' ");
    change to

    Code:
    stmt.execute("SET AssignedEngineer  to '" + y + "' FROM ClassNetStore WHERE FaultNo ='" + x + "' ");
    or better yet
    Code:
    string commandText = "SET AssignedEngineer  to '" + y + "' FROM ClassNetStore WHERE FaultNo ='" + x + "' ";
    stmt.execute(commandText);
    BUT!!!!!!!!
    I think your query needs a little work (although I could be wrong bc I use TSQL)
    I would think the query should look something like...
    Code:
    update ClassNetStore
    set AssignedEngineer = 'y'
    where FaultNo = x
    either way I assume that passing an int to SQL when it expects a string will fail (the way you have it) and passing an int to SQL by encapsulating it in '' (ie 'x') means it is a varchar or char or something not an int (which is what I assume you SQL is expecting)

    bottom line- if you are having issues, it is always best to eliminate "what" end it is happening on- you need to shoot a query against SQL and verify your query is correct before implementing your code, other wise you need to discern whether the error is coming from your code or SQL or both

    Edit: a little more elaboration on my SQL query...
    Code:
    declare @y as varchar = 'y'
    declare @x as int = x
    
    update ClassNetStore
    set AssignedEngineer = @y
    where FaultNo = @x
    oh and...
    I don't know Java equivilant but should be something like....
    object obj = stmt.execute("SET Assigne.........");
    and then try to convert obj to an int to return it
    Last edited by alykins; 02-10-2012 at 09:11 PM.

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • Users who have thanked alykins for this post:

    NewbiDaz (02-10-2012)

  • #8
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    For anyone who is following this post: SUCCESS! Almost perfect.

    As a recap, i am using JAVA (i am very new to it so am still using BlueJ) which is connecting to an access database in order to store faults that would be reported to a typical ITIL based service desk.

    One of my tasks was to create a method in java that can update the 'Assigned Engineer' record to a particular fault.

    I have the engineer part working and the fault number if i change my faultnumber field type in the database to text. The issue i now have is that my FaultNumber field in the database is an autonumber created using
    Code:
    stmt.executeUpdate("CREATE TABLE ClassNetStore " + "(FaultNo AUTOINCREMENT NOT NULL PRIMARY KEY")
    I am not sure how to change the primitive type of my argument to be compatible with the access AutoIncrement field. I have tried int, long, String and float with out success.

    For those who want to see how i adapted Alykins help :--------->
    Code:
    public void updateAssignedengineer(String NewEngineer, String FaultNumber)
        {
            try
            {
            
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection con = DriverManager.getConnection("jdbc:odbc:ServiceDeskDB.mdb");
                Statement stmt = con.createStatement();
                stmt.execute("UPDATE ClassNetStore SET AssignedEngineer='" + NewEngineer + "' WHERE FaultNo ='" + FaultNumber + "'");
        }
        catch (Exception err) {
                System.out.println("ERROR: " + err);
            }
        
        }
    If anyone can suggest how to match my FaultNo argument with a Autonumber value it would be appreciated.

    As it stands, if i use anything other than String when my database is set to Text i receive
    Code:
    ERROR: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
    Thank you to everybody who has helped.

  • #9
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    unless I am mistaken (I hate Access) the "auto increment" field is the same as doing an is identity field in SQL... DO NOT!!!!!!!!!! change it!!!!! it is auto incrementing for a reason!!!!! let the database handle itself!!!! if you want to make a field that you can manipulate the int then make a new column....
    You can make "stored procs" in access and call them (how well it will mesh I do not know) here is a good link for learning that sort of thing... your proc will be very close to what I posted (I posted TSQL code... see below for how to make a TSQL proc to do what you want)... you will be making it in VBA (visual basic w/ access back end) the main thing you will need to grasp is that you are going to be calling a vb script from Java (they may not play well)
    anyways here is how you would do it in TSQL
    Code:
    create proc usp_UpdateEngineer @AE varchar(20), @ID int
    as
    
    update ClassNetStore
    set AssignedEngineer = @AE
    where FaultNo = @ID
    now please read this!!!! in that link I posted it says do not use the @ symbol and calls out differences- what I posted is TSQL you will need to make sure you code for Access (it will be close and almost the same, but not)
    worth noting is: I do not know what return codes are in Access but a 0 in TSQL would mean it went through correctly (most languages (if not all) return 0 means it went through ok)

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #10
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thankyou very much and I know that I have set up the auto numbering for a reason however I have to define the variable type I am trying to match/query in my method signature as far as I know. I know I could create another access column of type 'int' which does not autonumber by Microsoft access however could use the java ++ increment statement.

    Thankyou.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •