Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting this out of my database

Posted on 2011-09-28
10
Medium Priority
?
245 Views
Last Modified: 2012-05-12
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
Comment
Question by:Mickeys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36719513
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 36720361
Possibly the following (use PreparedStatement)
"SELECT color, price WHERE flightno = ?"

Open in new window

0
 

Author Comment

by:Mickeys
ID: 36812919
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:for_yan
ID: 36812986

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
 

Author Comment

by:Mickeys
ID: 36813065
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813073

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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813074
sorry it was my misprint of course
0
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 total points
ID: 36813076

this is correct:

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

Author Closing Comment

by:Mickeys
ID: 36813087
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813160
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question