Getting this out of my database

public String[] getPrices(String flightNo) {
    String[] temp = {"Svart 1000", "Röd 800"};


How do I write a correct sql statment for this?

My database:
varchar, varchar, int

so flightno is the first one
color is the second
and price the last
MickeysAsked:
Who is Participating?
 
for_yanCommented:

this is correct:

ResultSet rs = statement.executeQuery("select color, price from table_name where flight_no = '" + flight_no + "'");
0
 
for_yanCommented:
I don't understand the begiining of your questuion - with java code


for your table this is the normal isnert statenmt would be:

insert into table_name (flight_no, color, price) values ('AA-263','blue',213.23)

so if your values are

String flight_no = "AA-263";
String color = "blue";
double price = 213.23

then your ssql staetmet in Java program wiill be:
stmt.executeUpdate("insert into table_name (flight_no, color, price) values ('" + flight_no+ "','" + color+ "'," + price + ")"); 

Open in new window


0
 
CEHJCommented:
Possibly the following (use PreparedStatement)
"SELECT color, price WHERE flightno = ?"

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
MickeysAuthor Commented:
for_yan: it is a GET from database. Not insert so that wont work

CEHJ. I have no Idea of how that will work.



public String[] getPrices(String flightNo) {
    //String[] temp = {"Svart 1000", "Röd 800"};

A select method that goes into the a table called myflights and you have the flightnumber and want to get the color and price.

When this color and price is out I want to concanate them so it gets Black 1000 and Red 800 for example
}


0
 
for_yanCommented:

This how you do it:

I assume you know how to connect to databse - you need either
to connect inside the method of connect before and
pass sql.Statement object as one of ther parameters:

public String[] getPrices(String flightNo) {

//connection to database

ResultSet rs = statement.executeQuery("select color, price for table_name where flight_no = '" + flight_no + "'");

ArrayList<String> ar = new ArrayList<String>();
while(rs.next()){

String color = rs.getString(1);
float price = rs.getFloat(2);

ar.add(color + " " + price);


}

String [] temp = new Strring[ar.size()];
for(int j=0; j<ar.size(); j++)temp[j] = ar.get(j);


return temp;

}

Open in new window

0
 
MickeysAuthor Commented:
Table price
===========
prFlightNo, prColor, prPrice
-----------

prFlightNo       varchar(10) PK
prColor          varchar(10) PK
prPrice          int(11)

Code:
return getInfo("SELECT prColor, prPrice for price where prFlightNo = '" + flightNo + "'", 2);

Output systm.out:
SELECT prColor, prPrice for price where prFlightNo = 'SK405'


Error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'price where prFlightNo = 'SK405'' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
0
 
for_yanCommented:

SELECT prColor, prPrice for price where prFlightNo = 'SK405'

why do you have "for price"

it sghould be "from price"

if "price" is the name of the table
0
 
for_yanCommented:
sorry it was my misprint of course
0
 
MickeysAuthor Commented:
Ah stupied. I should have spotted that myself. Sometimes you just get so blind stearing at the code.

I guess I have found my self a guru in database. You are answering all my questions. :-) Very helpful
0
 
for_yanCommented:
You are always welcome.

You are right sometimes it is difficult to spot the problem.
I usually try to minimize staring at the code - if often does not help. Just trying to do something - add a printout, or something,
and eventually it crops up.  Then sometimes become ashamed of myself for doing so much work for  some misprint.
But still it is faster than just staring.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.