Java SQL Inserts with variables problem

[icklejez]

Ive given my Strings, ints and floats variables, and tried to insert the variables into a table in access using SQL, But it gives some errors, firstly:

public static void addItem(String value1, String value2, int value3, int value4, float value5)
{
System.out.println("Enter value1: ");
Scanner input = new Scanner(System.in);

value1 = input.next();
System.out.println("Enter value2: ");
value2 = input.next();
System.out.println("Enter value3: ");
value3 = input.nextInt();
System.out.println("Enter value4: ");
value4 = input.nextInt();
System.out.println("Enter value5: ");
value5 = input.nextFloat();

try
{
String insert = "INSERT INTO myDatabase"
+ " VALUES(value1,value2,value3,value4,value5)";
statement.executeUpdate(insert);
}
catch(SQLException e)
{
System.out.println("* Cannot execute query! *");
e.printStackTrace();
System.exit(1);
}

------------------------------------------------------------

So why wont the variables work when inserted into my database? i cant think of another way of doing it, any reasons why this doesnt work?

Also, in main, under addItems() it says: The method addItem(String, String, int, int, float) in the type StockAccess is not applicable for the
arguments () I dont know what arguments to pass in main where string, string etc were.

 

[mundane]

IIRC, you should be inserting into a specific table within the database, and not using the database name itself. You can also specify the actual column name orders - insert into table ( col1, col2 ) values ( val1, val2 ). Although that approach does take a bit more typing, I think the statement is more explicit.

 

[icklejez]

yeh the myDatabase is the table name, if that helps at all. I've had it working where value1, value2, value3 etc are specific values, but ofcourse that would need to change everytime as the user may enter a different value into the scanner, so i assign it to a variable... but it doesnt work with variables i dont think? any suggestions

 

[mundane]

I'm assuming that where you're saying 'specific values', you mean some string literals being defined within your source code.

Oh - I see now.

"Insert into <TableName> values ( " + value1 + ", " + value2 + ", " + value3 + ", " + value4 + ", " + value5 + ");"

 

[icklejez]

Yes, i want variables inserted instead of the specific, strings, ints and floats, so value5 has been assigned to a float that the user entered into the scanner - is that how you do it? with + symbols and "" symbols?

 

[mundane]

Your SQL String needs to be just that - a String. So you take some string literals, such as "INSERT INTO TABLE VALUES(", and concatenate with the variables you're looking to insert into the columns. Java objects and primitives will implicitly convert to strings when you treat them as such, so the code:
"VALUES ( " + value1 + ", "
will concatenate the first string literal with the string value of 'value1', and then tack on ", ". So if value1 actually is "Ted", the resulting string is "VALUES ( Ted, ".

Another point - you have your method, addItem, in which the caller fills in these 'valueX' arguments. You'll likely want to move your scanner code out into the calling method, and then pass in the values your user has provided.

 

[icklejez]

So move the scanner into main? At the moment, main looks like this:

public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
link = DriverManager.getConnection(
"jdbcdbc:stockAccess","","");
}
catch(ClassNotFoundException e)
{
System.out.println("* Unable to load driver! *");
System.exit(1);
}
catch(SQLException e)
{
System.out.println(
"* Cannot connect to database! *");
System.exit(1);
}

addItem();
System.out.println(
"Table contents after changes:\n");
displayTableContents();
closeDown();
}

------------------------------------------------------------------------------------------

But I dont know what to pass as the arguments in addItem() in main, I tried using value1, value2, value3 etc, but its still telling me:

The method addItem(String, String, int, int, float) in the type myDatabase is not applicable for the arguments ()

 

[mundane]

You need to put your scanner code in there. Here's some pseudo-code

main() {
Obtain the jdbc driver
Setup up scanner
Read in value 1, 2, 3 ..
addItem( value1, value2, value3, ... )
}

addItem( val1, val2 ... ) {
Create the SQL string
Submit it to the database
}

Assuming that 'ink' you refer to is static, your addItem method should be able to use it to construct a statement.

 

[icklejez]

I dont understand how to create the SQL string and submit it without it being a variable, also, i dont understand how to construct a statement that will work? I have to pass what i have said that i will return which are, value1, value2 etc, but it doesnt accept them?

 

[mundane]

We've been over the sql string a few posts above. Once you have the variables value1, ..., filled in with your terms, you paste them together the create a full string in SQL syntax.

The statement is just another Java class instance, I think you grab it from your connection object. You provide the statement your SQL string, tell it to execute, and it should update your database.

There are plenty of tutorials/examples on the web. Specifically, look up how implement UPDATEs on tables.