Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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
0
Mickeys
Asked:
Mickeys
  • 6
  • 3
1 Solution
 
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
for_yanCommented:

this is correct:

ResultSet rs = statement.executeQuery("select color, price from table_name where flight_no = '" + flight_no + "'");
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now