Mailing-List: contact tomcat-dev-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: tomcat-dev@jakarta.apache.org
Message-ID: <3AD1E79B.B68DF058@mhsoftware.com>
Date: Mon, 09 Apr 2001 10:47:23 -0600
From: Christopher Cain <ccain@mhsoftware.com>
MIME-Version: 1.0
To: tomcat-dev@jakarta.apache.org
Subject: Re: database access problem
References: <JFEMIAGAMJPJFLEODNEECEAGDIAA.marc.saegesser@apropos.com>
 <002301c0c054$32f9a020$0f0aa8c0@hostel1.giki.edu.pk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Asim:

This is not the appropriate list for a JDBC question. This list is for
Tomcat-related questions. However, in answer to your question ...

I am surprised that this code works even in Access. The SQL you are
using actually should not return any rows at all. It is an INSERT
operation, not a SELECT statement. Given the SQL, the operation on g
would correctly be:

int iRecordsInserted = g.executeUpdate();

Using the executeQuery() method with an INSERT statement is more or less
invalid. Some driver implementations may try to handle it, which is why
your Access tests may have worked, but in general it is a very bad idea.

If you need to iterate the inserted records, you will need to do one of
two things. One way is simply to issue a SELECT query after the INSERT
which selects the inserted records. The other, and better, option would
be to create a stored procedure that does the INSERT and then returns
the records you want. See the prepareCall() methods on Connection for
more info on calling stored procedures in JDBC.

- Christopher


Asim Ghaffar wrote:
> 
> Hello Friends!
> I am in real difficult situation so i need some quick advice. I am making a
> search engine. I did all my testing on MS Acess. So far so good. But now
> when i switched to Oracle and SQL Server i am getting a very odd problem
> (same in oravle and SQL).
> here is my code.
> 
> 1 try    {
> 2     ResultSet results = g.executeQuery(); //
> 3     while (results.next()) {
> 4         String u = results.getString("u");
> 5         String t = results.getString("T");
> 6         String s = (new Integer(results.getInt("S"))).toString();
> 7         String k = results.getString("K");            // Exception is
> thrown here at this line
> 8         String a = results.getString("P");
> 9         String d = results.getString("D");
> 10       String m = (new Integer(results.getInt("I"))).toString();
> 11       String l = results.getString("L");
> 12       String o = (new Integer(results.getInt("O"))).toString();
> 13       String st = results.getTimestamp("St").toString();
> 14    }
> 15 }catch (SQLException sqle){
>     sqle.printStackTrace();
>  }
> 
> where g = connection.prepareStatement("INSERT INTO HITLIST SELECT SITES.*,1
> AS SCORE FROM SITES WHERE SITES.PAGETEXT LIKE '%pakistan%'");
> 
> Now on line # 7 i am getting the exception
> in SQL Server it is  [Microsoft][ODBC SQL Server Driver]Invalid Descriptor
> Index
> in Oracle it is some jdbc exeption about invalid column number
> 
> Now if i switch the wors with each other error again come on same line
> number not statement.
> I debugged using jbuilder and in the middle of execution all
> resultset.getDataType("columnName") become inavlid even those which are on
> lines above. So badically whole result set gets corrupted.
> Same code is running fine in MS Access. Any clue what can be the reason for
> this. Where else i can get help from.
> thanking you in advance. I u have any clue do email me.
> bye