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
Solved

Getting this out of my database

Posted on 2011-09-28
10
222 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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 2 45
Chrome and Firefox Java 5 50
Filtering characters in an SQL field 2 16
Access join syntax when converting to T-SQL query 4 33
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…

839 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