Help! Java method argument into prepared statement

NewbiDaz · 02-09-2012, 10:58 AM

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

Fou-Lu · 02-09-2012, 07:37 PM

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.

NewbiDaz · 02-09-2012, 09:03 PM

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

alykins · 02-09-2012, 09:10 PM

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....?

NewbiDaz · 02-09-2012, 09:59 PM

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

NewbiDaz · 02-10-2012, 08:49 PM

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.

alykins · 02-10-2012, 09:06 PM

Quote:

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

NewbiDaz · 02-10-2012, 11:56 PM

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.

alykins · 02-11-2012, 05:35 AM

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)

NewbiDaz · 02-11-2012, 06:55 AM

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.