This week's book giveaway is in the JavaFX forum.
We're giving away four copies of Introducing JavaFX 8 Programming and have Herbert Schildt on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes how to execute complex queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Introducing JavaFX 8 Programming this week in the JavaFX forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how to execute complex queries" Watch "how to execute complex queries" New topic

how to execute complex queries

Sivaraman Lakshmanan
Ranch Hand

Joined: Aug 02, 2003
Posts: 231
hai all,
I am writing an application where I am using JDBC to connect to access database. I am using the DSN to connect to the Database.
I use the method createStatement() to create a statement object thro which I get a resultset by passing a query to the executeQuery() method.
Whenever I give a simple query everything works fine but when I give a complex query like the one below I just get a error.
The Error that I get is
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(
at sun.jdbc.odbc.JdbcOdbc.standardError(
---- rest ignored ---
SELECT DISTINCTROW Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia;
The how to execute the complex queries like the one above in java. Even my application hangs when I give a cartesion query.
please help...
thanking you,

Joe Ess

Joined: Oct 29, 2001
Posts: 9150

First thing I'd try is to run the query in Access to see if the query syntax is correct.

[How To Ask Questions On JavaRanch]
Sarath Mohan
Ranch Hand

Joined: Mar 17, 2001
Posts: 213
I feel the error is due to passing invalid number of parameters to the SQL querry. Please check the query directly thru MS-Access database interface.
I suggest it's better to use PreparedStatement instead of simple Statement when we need to execute Complex queries.

Sarath Mohan
Mani Ram
Ranch Hand

Joined: Mar 11, 2002
Posts: 1140
As Joe mentioned, try the query in Access and see if it works.
Or another problem might be with the quotes ("). Try escaping the quotes.
I have got these sort of errors, because of missing quotes.

Quaerendo Invenietis
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Usually this error message occurs if you spell a column or table name wrong in your query. Cut and paste it into the Access SQL editor to execute, it'll give you an error message that actually might make sense. Good old Microsoft!
Sivaraman Lakshmanan
Ranch Hand

Joined: Aug 02, 2003
Posts: 231
hai all,
As u said i tried this query in MSAccess. it work fine. but only when i send this query to my app, an exception is thrown
help me pl
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Hi Sivaraman
Originally posted by Sivaraman Lakshmanan:

[FirstName] & " " & [LastName] AS Salesperson,

Most likely & " " & is causing the exception. It will not work when you say

You may try this

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I hope you don't include the semi-colon at the end of your sql statement in your java sql string?
String sql = "SELECT DISTINCTROW Orders.... ON Shippers.ShipperID = Orders.ShipVia;"; //the inner semi-colon would be bad!!
I agree. Here's the link:
subject: how to execute complex queries
It's not a secret anymore!