No such column error jdbc

jdbcsqlite

Suppose I have a table:

SERVICE={SID,NAME,PORT}

and with the help of a Java program, I want to select the SID of the known port with the help of a SQL query:

Select SID from SERVICE where PORT=port

But when I write the code snippet:

String sql="Select SID from SERVICE where PORT=port;";

where port is an integer variable but on execution I get the following error:

No such column port".How to get rid of this error?

Best Answer

It is generally a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

String[] port = new String[] {"3306"};
    PreparedStatement statement = connection.prepareStatement("String sql="Select SID from SERVICE where PORT= ?");

    for (String name: names) {
        statement.setString(1, port);    
        ResultSet resultSet = statement.executeQuery();
        ...
        ...
        statement.clearParameters();
    }

Related Solutions

Nesting MAX for SELECT

It can be done with a query:

SELECT 
    s.*
FROM
  ( SELECT DISTINCT series_id
    FROM series
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season =
          ( SELECT si.season
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC
            LIMIT 1
          ) 
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC, si.episode DESC
            LIMIT 1
          ) ;

Tested at SQL-Fiddle

With an index on (series_id, season, episode), it should be quite efficient, if the number of series_id is small.

CREATE UNIQUE INDEX uix
  ON series
  (series_id, season, episode) ;

There are several ways to do this query but all get rather complicated because the maximum needed is based on two columns (season and episode). Here's another one, which is the query above, simplified a bit:

SELECT 
    s.*
FROM
  ( SELECT series_id, MAX(season) AS season
    FROM series
    GROUP BY series_id
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season = ds.season
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
              AND si.season = ds.season
            ORDER BY si.episode DESC
            LIMIT 1
          ) ;

and another using NOT EXISTS:

SELECT s.*
FROM series AS s
WHERE NOT EXISTS
      ( SELECT 1
        FROM series AS sb
        WHERE sb.series_id = s.series_id
          AND ( sb.season > s.season
             OR sb.season = s.season AND sb.episode > s.episode
              )
      ) ;

SQLite Error – No Such Column TABLE.ROWID

your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM
        Message 
    INNER JOIN 
        ( Contact INNER JOIN contact_m2m_message 
            ON Contact.contactID = contact_m2m_message.contactID
        )
      ON Message.msgID = contact_m2m_message.messageID ;

Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.

The 2nd query is not valid because you have not provided an alias for the derived table:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select Contact.contactNumber, Contact.contactName 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        )     --<-------------------------------------- no alias
            ON Message.msgID = contact_m2m_message.messageID;

To make it work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID in the SELECT list of the derived table (and remove columns that are not used):

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select -- Contact.contactNumber, Contact.contactName,
                 contact_m2m_message.messageID 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        ) AS cm
            ON Message.msgID = cm.messageID;

But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:

SELECT 
    m.messageContent, m.messageDateTime 
FROM 
    Message  AS m
  INNER JOIN
    contact_m2m_message  AS cm
      ON m.msgID = cm.messageID 
  INNER JOIN
    Contact  AS c 
      ON  c.contactID = cm.contactID ;

Best Answer

Related Solutions

Nesting MAX for SELECT

SQLite Error – No Such Column TABLE.ROWID

Related Question