Solved

JSP and MS SQL database (Urgent help needed)!

Posted on 2002-03-07
25
182 Views
Last Modified: 2010-03-31
When i tried to insert my data into the jsp page, i was not unable to insert the data successfully.For example, I tried to paste a whole chunk of articles into a textarea and i got the error "Error: 414 Location: /" or when i tried to insert half the article, i was not able to insert successfully.. My datatype for the database is text.
0
Comment
Question by:Gin_a
  • 13
  • 7
  • 5
25 Comments
 
LVL 6

Expert Comment

by:kotan
ID: 6846687
How do you write your sql statement?
Maybe the text got same special character: " or ' which give you the error.

Maybe you can try to use
   PreparedStatement.setString(1, "");
to insert text.
0
 

Author Comment

by:Gin_a
ID: 6846695
This is my method in my database class

 
     public boolean addNews(String title, int day, int month, int year, String source, String content, String editor, String postedBy, String lastUpdatedBy)
     {
          System.out.println(".."+content+">>");
          int row = 0;
          try
          {
               Statement stmt = con.createStatement();
               String sql = "Insert into News (Title, Day, Month, Year, Source, Content, Editor, Postedby, Lastupdatedby) values('"+title+"',"+day+","+month+","+year+",'"+source+"','"+content+"','"+editor+"','"+postedBy+"','"+lastUpdatedBy+"')";
               row = stmt.executeUpdate(sql);
               if(row == 1)
               {
                    return true;
               }
          }//try
          catch(SQLException e)
          {
               System.out.println(e);
          }
          return false;
     }
"
i have a database class and a servlet class respectively
0
 
LVL 6

Expert Comment

by:kotan
ID: 6846707
Try this,

String sql = "Insert into News(.., content, ...) values(.., ?, ..)";

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(6, content);
pstmt.executeUpdate();
 

0
 

Author Comment

by:Gin_a
ID: 6846718
sorry,i insert the wrong method
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
     {
          try
          {
               Statement stmt = con.createStatement();
               String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"' where NewsID = "+n.getID();
               int row = stmt.executeUpdate(sql);
               if (row == 1)
               {
                    return true;
               }
          }
          catch(SQLException e)
          {
               e.printStackTrace();
          }
          return false;    
     }
0
 
LVL 6

Expert Comment

by:kotan
ID: 6846731
Similar method.

String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();
0
 

Author Comment

by:Gin_a
ID: 6846734
sorry,i insert the wrong method
if update.. then how?
just now that method was insert data.. when i update..
public boolean updateNews(News n)
     {
          try
          {
               Statement stmt = con.createStatement();
               String sql = "Update News set Title = '"+n.getTitle()+"', Day = "+n.getDay()+", Month = "+n.getMonth()+", Year = "+n.getYear()+", Source = '"+n.getSource()+"', Content = '"+n.getContent()+"', Editor = '"+n.getEditor()+"', Lastupdatedby = '"+n.getLastUpdatedBy()+"' where NewsID = "+n.getID();
               int row = stmt.executeUpdate(sql);
               if (row == 1)
               {
                    return true;
               }
          }
          catch(SQLException e)
          {
               e.printStackTrace();
          }
          return false;    
     }
0
 
LVL 6

Expert Comment

by:kotan
ID: 6846747
Then,

String sql = "Update News set .., Content = ?, ...";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, n.getContent());
pstmt.executeUpdate();


Does it work?
0
 

Author Comment

by:Gin_a
ID: 6846792
still cannot work.
0
 

Author Comment

by:Gin_a
ID: 6846802
does that mean i have to use another data type. one that allows the inserting of large information.
0
 
LVL 6

Expert Comment

by:kotan
ID: 6846806
Still the same error?
---Error: 414
Where does it come from? db or web server?
0
 
LVL 6

Expert Comment

by:kotan
ID: 6846809
Did you try CLOB datatype.
0
 

Author Comment

by:Gin_a
ID: 6846812
does that mean i have to use another data type. one that allows the inserting of large information.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Gin_a
ID: 6846814
when i lesser the information of my data, i was able to insert.. so i also not sure where the error is from.
0
 

Author Comment

by:Gin_a
ID: 6846846
i use ms sql 2000. there is no clob data type
0
 

Author Comment

by:Gin_a
ID: 6846883
I try to insert 1668 characters with spaces into the textarea and i managed to insert successfully however if more than that, i will not be able to insert into the database. Besides that, when there are ' in my articles, i am not able to insert as well..
0
 
LVL 4

Expert Comment

by:m_onkey_boy
ID: 6848032
It sounds like the problem may be with your driver.  Which driver are you using?
0
 

Author Comment

by:Gin_a
ID: 6849006
u mean the database driver?
0
 
LVL 4

Expert Comment

by:m_onkey_boy
ID: 6849028
Yes.  Which database driver are you using?
0
 

Author Comment

by:Gin_a
ID: 6849044
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
0
 
LVL 6

Accepted Solution

by:
kotan earned 75 total points
ID: 6849083
I have check the error "Error: 414", It is "Request-URI Too Large" which is throw by web server.

Try to use POST instead of GET

0
 
LVL 4

Expert Comment

by:m_onkey_boy
ID: 6849140
As an expiriment, try using PreparedStatement and setString() to insert the large String instead of one long update statement.  If that doesn't work, try setCharacterStream().  The simple change of binding your variables may make all the difference.  I'll modify your update for you:

PreparedStatement pst = con.prepareStatement("Update News set Title = ?, Day = ?, Month = ?, Year = ?, Source = ?, Content = ?, Editor = ?, Lastupdateby = ? WHERE NewsID = ?");

pst.setString(1, n.getTitle());
pst.setInt(2, n.getDay());
pst.setInt(3, n.getMonth());
pst.setInt(4, n.getYear());
pst.setString(5, n.getSource());
pst.setString(6, n.getContent());
pst.setString(7, n.getEditor());
pst.setString(8, n.getLastUpdatedBy());
pst.setInt(9, n.getID());

int row = stmt.executeUpdate();

If that doesn't work, change the

pst.setString(6, n.getContent());

to:
 Reader rdr = new StringReader(n.getContent());
 pst.setCharacterStream(6, n.getContent(), n.getContent().length());

-----------------------------------------------------

BTW your datatype is text, right?

0
 
LVL 4

Expert Comment

by:m_onkey_boy
ID: 6849147
good catch, kotan.

Gina, the .newInstance() call on your Class.forname is not necessary.
0
 

Author Comment

by:Gin_a
ID: 6849249
i manage to insert the data already. however, if in the information, there are 's in between, i was not able to insert the information into the database. By the way, the data type is text.
0
 
LVL 4

Expert Comment

by:m_onkey_boy
ID: 6849280
My method will let you insert the ' characters as is.  That's one of the main advantages to it.
0
 

Author Comment

by:Gin_a
ID: 6849304
I solved all the problems already. Thank for all the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bitbucket vs gitbucket 3 57
Java Timer (static) 9 36
couple of eclipse 5 25
even odd program using while loop 3 29
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now