"java.sql.SQLException: SQL string is not Query"
Konekcija treba da se iz ove stranice uspostavi na Oracle 10g XE.
Ako bi neko mogao da mi pomogne? Hvala unaprijed!
Kod fajlova: "Formatlist.jsp" i "db.jsp" sam postavio ispod.
"Formatlist.jsp"
<%@ page session="true" buffer="16kb" import="java.sql.*,java.util.*,java.text.*"%>
<%@ page import="oracle.jdbc.*" %>
<%@ page contentType="text/html; charset=UTF-8" %>
<% Locale locale = Locale.getDefault();
response.setLocale(locale);%>
<% session.setMaxInactiveInterval(30*60); %>
<%@ include file="db.jsp" %>
<%@ include file="jspmkrfn.jsp" %>
<%
int displayRecs = 20;
int recRange = 10;
%>
<%
String tmpfld = null;
String escapeString = "''";
String dbwhere = "";
String masterdetailwhere = "";
String searchwhere = "";
String a_search = "";
String b_search = "";
String whereClause = "";
int startRec = 0, stopRec = 0, totalRecs = 0, recCount = 0;
%>
<%
// Get search criteria for basic search
String pSearch = request.getParameter("psearch");
String pSearchType = request.getParameter("psearchtype");
if (pSearch != null && pSearch.length() > 0) {
pSearch = pSearch.replaceAll("'",escapeString);
if (pSearchType != null && pSearchType.length() > 0) {
while (pSearch.indexOf(" ") > 0) {
pSearch = pSearch.replaceAll(" ", " ");
}
String [] arpSearch = pSearch.trim().split(" ");
for (int i = 0; i < arpSearch.length; i++){
String kw = arpSearch[i].trim();
b_search = b_search + "(";
b_search = b_search + "\"ID_F\" LIKE '%" + kw + "%' OR ";
b_search = b_search + "\"Naziv_F\" LIKE '%" + kw + "%' OR ";
if (b_search.substring(b_search.length()-4,b_search.length()).equals(" OR ")) { b_search = b_search.substring(0,b_search.length()-4);}
b_search = b_search + ") " + pSearchType + " ";
}
}else{
b_search = b_search + "\"ID_F\" LIKE '%" + pSearch + "%' OR ";
b_search = b_search + "\"Naziv_F\" LIKE '%" + pSearch + "%' OR ";
}
}
if (b_search.length() > 4 && b_search.substring(b_search.length()-4,b_search.length()).equals(" OR ")) {b_search = b_search.substring(0, b_search.length()-4);}
if (b_search.length() > 5 && b_search.substring(b_search.length()-5,b_search.length()).equals(" AND ")) {b_search = b_search.substring(0, b_search.length()-5);}
%>
<%
// Build search criteria
if (a_search != null && a_search.length() > 0) {
searchwhere = a_search; // Advanced search
}else if (b_search != null && b_search.length() > 0) {
searchwhere = b_search; // Basic search
}
// Save search criteria
if (searchwhere != null && searchwhere.length() > 0) {
session.setAttribute("Format_searchwhere", searchwhere);
startRec = 1; // Reset start record counter (new search)
session.setAttribute("Format_REC", new Integer(startRec));
}else{
if (session.getAttribute("Format_searchwhere") != null)
searchwhere = (String) session.getAttribute("Format_searchwhere");
}
%>
<%
// Get clear search cmd
startRec = 0;
if (request.getParameter("cmd") != null && request.getParameter("cmd").length() > 0) {
String cmd = request.getParameter("cmd");
if (cmd.toUpperCase().equals("RESET")) {
searchwhere = ""; // Reset search criteria
session.setAttribute("Format_searchwhere", searchwhere);
}else if (cmd.toUpperCase().equals("RESETALL")) {
searchwhere = ""; // Reset search criteria
session.setAttribute("Format_searchwhere", searchwhere);
}
startRec = 1; // Reset start record counter (reset command)
session.setAttribute("Format_REC", new Integer(startRec));
}
// Build dbwhere
if (masterdetailwhere != null && masterdetailwhere.length() > 0) {
dbwhere = dbwhere + "(" + masterdetailwhere + ") AND ";
}
if (searchwhere != null && searchwhere.length() > 0) {
dbwhere = dbwhere + "(" + searchwhere + ") AND ";
}
if (dbwhere != null && dbwhere.length() > 5) {
dbwhere = dbwhere.substring(0, dbwhere.length()-5); // Trim rightmost AND
}
%>
<%
// Load Default Order
String DefaultOrder = "";
String DefaultOrderType = "";
// No Default Filter
String DefaultFilter = "";
// Check for an Order parameter
String OrderBy = request.getParameter("order");
if (OrderBy != null && OrderBy.length() > 0) {
if (session.getAttribute("Format_OB") != null &&
((String) session.getAttribute("Format_OB")).equals(OrderBy)) { // Check if an ASC/DESC toggle is required
if (((String) session.getAttribute("Format_OT")).equals("ASC")) {
session.setAttribute("Format_OT", "DESC");
}else{
session.setAttribute("Format_OT", "ASC");
}
}else{
session.setAttribute("Format_OT", "ASC");
}
session.setAttribute("Format_OB", OrderBy);
session.setAttribute("Format_REC", new Integer(1));
}else{
OrderBy = (String) session.getAttribute("Format_OB");
if (OrderBy == null || OrderBy.length() == 0) {
OrderBy = DefaultOrder;
session.setAttribute("Format_OB", OrderBy);
session.setAttribute("Format_OT", DefaultOrderType);
}
}
// Open Connection to the database
try{
Statement stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeQuery("ALTER SESSION SET CURRENT_SCHEMA = NIKOLA");
ResultSet rs = null;
// Build SQL
String strsql = "SELECT * FROM \"Format\"";
whereClause = "";
if (DefaultFilter.length() > 0) {
whereClause = whereClause + "(" + DefaultFilter + ") AND ";
}
if (dbwhere.length() > 0) {
whereClause = whereClause + "(" + dbwhere + ") AND ";
}
if (whereClause.length() > 5 && whereClause.substring(whereClause.length()-5, whereClause.length()).equals(" AND ")) {
whereClause = whereClause.substring(0, whereClause.length()-5);
}
if (whereClause.length() > 0) {
strsql = strsql + " WHERE " + whereClause;
}
if (OrderBy != null && OrderBy.length() > 0) {
strsql = strsql + " ORDER BY \"" + OrderBy + "\" " + (String) session.getAttribute("Format_OT");
}
//out.println(strsql);
rs = (ResultSet) stmt.executeQuery(strsql);
rs.last();
totalRecs = rs.getRow();
rs.beforeFirst();
startRec = 0;
int pageno = 0;
// Check for a START parameter
if (request.getParameter("start") != null && Integer.parseInt(request.getParameter("start")) > 0) {
startRec = Integer.parseInt(request.getParameter("start"));
session.setAttribute("Format_REC", new Integer(startRec));
}else if (request.getParameter("pageno") != null && Integer.parseInt(request.getParameter("pageno")) > 0) {
pageno = Integer.parseInt(request.getParameter("pageno"));
if (IsNumeric(request.getParameter("pageno"))) {
startRec = (pageno-1)*displayRecs+1;
if (startRec <= 0) {
startRec = 1;
}else if (startRec >= ((totalRecs-1)/displayRecs)*displayRecs+1) {
startRec = ((totalRecs-1)/displayRecs)*displayRecs+1;
}
session.setAttribute("Format_REC", new Integer(startRec));
}else {
startRec = ((Integer) session.getAttribute("Format_REC")).intValue();
if (startRec <= 0) {
startRec = 1; // Reset start record counter
session.setAttribute("Format_REC", new Integer(startRec));
}
}
}else{
if (session.getAttribute("Format_REC") != null)
startRec = ((Integer) session.getAttribute("Format_REC")).intValue();
if (startRec==0) {
startRec = 1; //Reset start record counter
session.setAttribute("Format_REC", new Integer(startRec));
}
}
%>
<%@ include file="header.jsp" %>
<p><span class="jspmaker">TABLE: Format</span></p>
<form action="Formatlist.jsp">
<table border="0" cellspacing="0" cellpadding="4">
<tr>
<td><span class="jspmaker">Quick Search (*)</span></td>
<td><span class="jspmaker">
<input type="text" name="psearch" size="20">
<input type="Submit" name="Submit" value="GO">
<a href="Formatlist.jsp?cmd=reset">Show all</a>
</span></td>
</tr>
<tr><td> </td><td><span class="jspmaker"><input type="radio" name="psearchtype" value="" checked>Exact phrase <input type="radio" name="psearchtype" value="AND">All words <input type="radio" name="psearchtype" value="OR">Any word</span></td></tr>
</table>
</form>
<form method="post">
<table border="0" cellspacing="2" cellpadding="4" bgcolor="#CCCCCC">
<tr bgcolor="#FF3300">
<td><span class="jspmaker" style="color: #FFFFFF;">
<a href="Formatlist.jsp?order=<%= java.net.URLEncoder.encode("ID_F","UTF-8") %>" style="color: #FFFFFF;">ID F (*)<% if (OrderBy != null && OrderBy.equals("ID_F")) { %><span class="ewTableOrderIndicator"><% if (((String) session.getAttribute("Format_OT")).equals("ASC")) {%>5<% }else if (((String) session.getAttribute("Format_OT")).equals("DESC")) { %>6<% } %></span><% } %></a>
</span></td>
<td><span class="jspmaker" style="color: #FFFFFF;">
<a href="Formatlist.jsp?order=<%= java.net.URLEncoder.encode("Naziv_F","UTF-8") %>" style="color: #FFFFFF;">Naziv F (*)<% if (OrderBy != null && OrderBy.equals("Naziv_F")) { %><span class="ewTableOrderIndicator"><% if (((String) session.getAttribute("Format_OT")).equals("ASC")) {%>5<% }else if (((String) session.getAttribute("Format_OT")).equals("DESC")) { %>6<% } %></span><% } %></a>
</span></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<%
// Avoid starting record > total records
if (startRec > totalRecs) {
startRec = totalRecs;
}
// Set the last record to display
stopRec = startRec + displayRecs - 1;
// Move to first record directly for performance reason
recCount = startRec - 1;
if (rs.next()) {
rs.first();
rs.relative(startRec - 1);
}
long recActual = 0;
if (startRec == 1)
rs.beforeFirst();
else
rs.previous();
while (rs.next() && recCount < stopRec) {
recCount++;
if (recCount >= startRec) {
recActual++;
%>
<%
String bgcolor = "#FFFFFF"; // Set row color
%>
<%
if (recCount%2 != 0) { // Display alternate color for rows
bgcolor = "#F5F5F5";
}
%>
<%
String x_ID_F = "";
String x_Naziv_F = "";
// Load Key for record
String key = "";
if(rs.getString("ID_F") != null){
key = rs.getString("ID_F");
}
// ID_F
if (rs.getString("ID_F") != null){
x_ID_F = rs.getString("ID_F");
}else{
x_ID_F = "";
}
// Naziv_F
if (rs.getString("Naziv_F") != null){
x_Naziv_F = rs.getString("Naziv_F");
}else{
x_Naziv_F = "";
}
%>
<tr bgcolor="<%= bgcolor %>">
<td><span class="jspmaker"><% out.print(x_ID_F); %></span> </td>
<td><span class="jspmaker"><% out.print(x_Naziv_F); %></span> </td>
<td><span class="jspmaker"><a href="<% key = rs.getString("ID_F");
if (key != null && key.length() > 0) {
out.print("Formatview.jsp?key=" + java.net.URLEncoder.encode(key,"UTF-8"));
}else{
out.print("javascript:alert('Invalid Record! Key is null');");
} %>">View</a></span></td>
<td><span class="jspmaker"><a href="<% key = rs.getString("ID_F");
if (key != null && key.length() > 0) {
out.print("Formatedit.jsp?key=" + java.net.URLEncoder.encode(key,"UTF-8"));
}else{
out.print("javascript:alert('Invalid Record! Key is null');");
} %>">Edit</a></span></td>
<td><span class="jspmaker"><a href="<% key = rs.getString("ID_F");
if (key != null && key.length() > 0) {
out.print("Formatadd.jsp?key=" + java.net.URLEncoder.encode(key,"UTF-8"));
}else{
out.print("javascript:alert('Invalid Record! Key is null');");
} %>">Copy</a></span></td>
<td><span class="jspmaker"><a href="<% key = rs.getString("ID_F");
if (key != null && key.length() > 0) {
out.print("Formatdelete.jsp?key=" + java.net.URLEncoder.encode(key,"UTF-8"));
}else{
out.print("javascript:alert('Invalid Record! Key is null');");
} %>">Delete</a></span></td>
</tr>
<%
// }
}
}
%>
</table>
</form>
<%
// Close recordset and connection
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}catch(SQLException ex){
out.println(ex.toString());
}
%>
<table border="0" cellspacing="0" cellpadding="10"><tr><td>
<%
boolean rsEof = false;
if (totalRecs > 0) {
rsEof = (totalRecs < (startRec + displayRecs));
int PrevStart = startRec - displayRecs;
if (PrevStart < 1) { PrevStart = 1;}
int NextStart = startRec + displayRecs;
if (NextStart > totalRecs) { NextStart = startRec;}
int LastStart = ((totalRecs-1)/displayRecs)*displayRecs+1;
%>
<form>
<table border="0" cellspacing="0" cellpadding="0"><tr><td><span class="jspmaker">Page</span> </td>
<!--first page button-->
<% if (startRec==1) { %>
<td><img src="images/firstdisab.gif" alt="First" width="20" height="15" border="0"></td>
<% }else{ %>
<td><a href="Formatlist.jsp?start=1"><img src="images/first.gif" alt="First" width="20" height="15" border="0"></a></td>
<% } %>
<!--previous page button-->
<% if (PrevStart == startRec) { %>
<td><img src="images/prevdisab.gif" alt="Previous" width="20" height="15" border="0"></td>
<% }else{ %>
<td><a href="Formatlist.jsp?start=<%=PrevStart%>"><img src="images/prev.gif" alt="Previous" width="20" height="15" border="0"></a></td>
<% } %>
<!--current page number-->
<td><input type="text" name="pageno" value="<%=(startRec-1)/displayRecs+1%>" size="4"></td>
<!--next page button-->
<% if (NextStart == startRec) { %>
<td><img src="images/nextdisab.gif" alt="Next" width="20" height="15" border="0"></td>
<% }else{ %>
<td><a href="Formatlist.jsp?start=<%=NextStart%>"><img src="images/next.gif" alt="Next" width="20" height="15" border="0"></a></td>
<% } %>
<!--last page button-->
<% if (LastStart == startRec) { %>
<td><img src="images/lastdisab.gif" alt="Last" width="20" height="15" border="0"></td>
<% }else{ %>
<td><a href="Formatlist.jsp?start=<%=LastStart%>"><img src="images/last.gif" alt="Last" width="20" height="15" border="0"></a></td>
<% } %>
<td><a href="Formatadd.jsp"><img src="images/addnew.gif" alt="Add new" width="20" height="15" border="0"></a></td>
<td><span class="jspmaker"> of <%=(totalRecs-1)/displayRecs+1%></span></td>
</td></tr></table>
</form>
<% if (startRec > totalRecs) { startRec = totalRecs;}
stopRec = startRec + displayRecs - 1;
recCount = totalRecs - 1;
if (rsEof) { recCount = totalRecs;}
if (stopRec > recCount) { stopRec = recCount;} %>
<span class="jspmaker">Records <%= startRec %> to <%= stopRec %> of <%= totalRecs %></span>
<% }else{ %>
<span class="jspmaker">No records found</span>
<p>
<a href="Formatadd.jsp"><img src="images/addnew.gif" alt="Add new" width="20" height="15" border="0"></a>
</p>
<% } %>
</td></tr></table>
<%@ include file="footer.jsp" %>
Konekciju sam zamislio da mi ide preko:
"db.jsp"
<%@ page import="java.sql.*"%>
<%
try{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}catch (Exception e){
out.println(e.toString());
}
String xDb_Conn_Str = "jdbc:oracle:thin:@localhost:1521:xe";
Connection conn = null;
try{
conn = DriverManager.getConnection(xDb_Conn_Str,"nikola","nick");
}catch (SQLException e){
out.println(e.toString());
}
%>