Подскажите пож-та новичку где в цикле ошибка



Выводит следущее:

INSERT INTO Rep3_2 (val, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20) VALUES (1, (SELECT R3F1(d1,d2,1,1,1)), (SELECT R3F1(d1,d2,1,2,1)), (SELECT R3F1(d1,d2,1,3,1)), (SELECT R3F1(d1,d2,1,4,1)), (SELECT R3F1(d1,d2,1,5,1)), (SELECT R3F1(d1,d2,1,6,1)), (SELECT R3F1(d1,d2,1,7,1)), (SELECT R3F1(d1,d2,1,8,1)), (SELECT R3F1(d1,d2,1,9,1)), (SELECT R3F1(d1,d2,1,10,1)), (SELECT R3F1(d1,d2,1,11,1)), (SELECT R3F1(d1,d2,1,12,1)), (SELECT R3F1(d1,d2,1,13,1)), (SELECT R3F1(d1,d2,1,14,1)), (SELECT R3F1(d1,d2,1,15,1)), (SELECT R3F1(d1,d2,1,16,1)), (SELECT R3F1(d1,d2,1,17,1)), (SELECT R3F1(d1,d2,1,18,1)), (SELECT R3F1(d1,d2,1,19,1)), (SELECT R3F1(d1,d2,1,20,1)));

INSERT INTO Rep3_2 (val, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20) VALUES (1, (SELECT R3F1(d1,d2,1,1,1)), (SELECT R3F1(d1,d2,1,2,1)), (SELECT R3F1(d1,d2,1,3,1)), (SELECT R3F1(d1,d2,1,4,1)), (SELECT R3F1(d1,d2,1,5,1)), (SELECT R3F1(d1,d2,1,6,1)), (SELECT R3F1(d1,d2,1,7,1)), (SELECT R3F1(d1,d2,1,8,1)), (SELECT R3F1(d1,d2,1,9,1)), (SELECT R3F1(d1,d2,1,10,1)), (SELECT R3F1(d1,d2,1,11,1)), (SELECT R3F1(d1,d2,1,12,1)), (SELECT R3F1(d1,d2,1,13,1)), (SELECT R3F1(d1,d2,1,14,1)), (SELECT R3F1(d1,d2,1,15,1)), (SELECT R3F1(d1,d2,1,16,1)), (SELECT R3F1(d1,d2,1,17,1)), (SELECT R3F1(d1,d2,1,18,1)), (SELECT R3F1(d1,d2,1,19,1)), (SELECT R3F1(d1,d2,1,20,1))); (2, (SELECT R3F1(d1,d2,2,1,1)), (SELECT R3F1(d1,d2,2,2,1)), (SELECT R3F1(d1,d2,2,3,1)), (SELECT R3F1(d1,d2,2,4,1)), (SELECT R3F1(d1,d2,2,5,1)), (SELECT R3F1(d1,d2,2,6,1)), (SELECT R3F1(d1,d2,2,7,1)), (SELECT R3F1(d1,d2,2,8,1)), (SELECT R3F1(d1,d2,2,9,1)), (SELECT R3F1(d1,d2,2,10,1)), (SELECT R3F1(d1,d2,2,11,1)), (SELECT R3F1(d1,d2,2,12,1)), (SELECT R3F1(d1,d2,2,13,1)), (SELECT R3F1(d1,d2,2,14,1)), (SELECT R3F1(d1,d2,2,15,1)), (SELECT R3F1(d1,d2,2,16,1)), (SELECT R3F1(d1,d2,2,17,1)), (SELECT R3F1(d1,d2,2,18,1)), (SELECT R3F1(d1,d2,2,19,1)), (SELECT R3F1(d1,d2,2,20,1)));

INSERT INTO Rep3_2 (val, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20) VALUES (1, (SELECT R3F1(d1,d2,1,1,1)), (SELECT R3F1(d1,d2,1,2,1)), (SELECT R3F1(d1,d2,1,3,1)), (SELECT R3F1(d1,d2,1,4,1)), (SELECT R3F1(d1,d2,1,5,1)), (SELECT R3F1(d1,d2,1,6,1)), (SELECT R3F1(d1,d2,1,7,1)), (SELECT R3F1(d1,d2,1,8,1)), (SELECT R3F1(d1,d2,1,9,1)), (SELECT R3F1(d1,d2,1,10,1)), (SELECT R3F1(d1,d2,1,11,1)), (SELECT R3F1(d1,d2,1,12,1)), (SELECT R3F1(d1,d2,1,13,1)), (SELECT R3F1(d1,d2,1,14,1)), (SELECT R3F1(d1,d2,1,15,1)), (SELECT R3F1(d1,d2,1,16,1)), (SELECT R3F1(d1,d2,1,17,1)), (SELECT R3F1(d1,d2,1,18,1)), (SELECT R3F1(d1,d2,1,19,1)), (SELECT R3F1(d1,d2,1,20,1))); (2, (SELECT R3F1(d1,d2,2,1,1)), (SELECT R3F1(d1,d2,2,2,1)), (SELECT R3F1(d1,d2,2,3,1)), (SELECT R3F1(d1,d2,2,4,1)), (SELECT R3F1(d1,d2,2,5,1)), (SELECT R3F1(d1,d2,2,6,1)), (SELECT R3F1(d1,d2,2,7,1)), (SELECT R3F1(d1,d2,2,8,1)), (SELECT R3F1(d1,d2,2,9,1)), (SELECT R3F1(d1,d2,2,10,1)), (SELECT R3F1(d1,d2,2,11,1)), (SELECT R3F1(d1,d2,2,12,1)), (SELECT R3F1(d1,d2,2,13,1)), (SELECT R3F1(d1,d2,2,14,1)), (SELECT R3F1(d1,d2,2,15,1)), (SELECT R3F1(d1,d2,2,16,1)), (SELECT R3F1(d1,d2,2,17,1)), (SELECT R3F1(d1,d2,2,18,1)), (SELECT R3F1(d1,d2,2,19,1)), (SELECT R3F1(d1,d2,2,20,1))); (3, (SELECT R3F1(d1,d2,3,1,1)), (SELECT R3F1(d1,d2,3,2,1)), (SELECT R3F1(d1,d2,3,3,1)), (SELECT R3F1(d1,d2,3,4,1)), (SELECT R3F1(d1,d2,3,5,1)), (SELECT R3F1(d1,d2,3,6,1)), (SELECT R3F1(d1,d2,3,7,1)), (SELECT R3F1(d1,d2,3,8,1)), (SELECT R3F1(d1,d2,3,9,1)), (SELECT R3F1(d1,d2,3,10,1)), (SELECT R3F1(d1,d2,3,11,1)), (SELECT R3F1(d1,d2,3,12,1)), (SELECT R3F1(d1,d2,3,13,1)), (SELECT R3F1(d1,d2,3,14,1)), (SELECT R3F1(d1,d2,3,15,1)), (SELECT R3F1(d1,d2,3,16,1)), (SELECT R3F1(d1,d2,3,17,1)), (SELECT R3F1(d1,d2,3,18,1)), (SELECT R3F1(d1,d2,3,19,1)), (SELECT R3F1(d1,d2,3,20,1)));
.....

0FD,

ах да, моя ошибка.
ошибка выходит после того как вставляю значения d1, d2:
for (int j=1; j<=cols; j++) {
v += ", (SELECT R3F1(to_date("+"'"+request.getParameter("d1")+"'"+","+"'"+"yyyy-mm-dd"+"'"+"),"+
"to_date("+"'"+request.getParameter("d2")+"'"+","+"'"+"yyyy-mm-dd"+"'"+")," +i+ "," +j+ ",1))";
}


ошибка:

SEVERE: Servlet.service() for servlet [jsp] in context with path [/MedStatPro] threw exception [An exception occurred processing JSP page /RepExcel3.jsp at line 77

76: stI = con.createStatement();
77: rsI = stI.executeUpdate(ins);

ошибка здесь конечно в синтаксисе, но на Базе работает.

Проверял так
SELECT R3F1(to_date('2013-11-01','yyyy-mm-dd'),to_date('2013-11-15','yyyy-mm-dd'),1,1,1)

0FD,

Да это и есть первоначальная проблема.
Не могу понять почему

INSERT INTO Rep3_2 (val, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20) VALUES

ставится только один раз

0FD,
Спасибо.

загнал в цикл:
String ins = "INSERT INTO Rep3_2 (" + u + ") VALUES ";

Теперь другая проблема, но таже ошибка:

org.apache.jasper.JasperException: An exception occurred processing JSP page /RepExcel3.jsp at line 78

77: stI = con.createStatement();
78: rsI = stI.executeUpdate(ins);

вадя прав, нельзя запускать несколько Insert в одном executeUpdate(ins)

вадя,

Eclipse IDE.
Да Вы правы нельзя запускать несколько Insert в одном executeUpdate
Не подскажете как делается пошаговая отладка на Eclipse?

Как быть с executeUpdate(ins)?

вадя,

SELECT R3F1(to_date('2013-11-01','yyyy-mm-dd'),to_date('2013-11-15','yyyy-mm-dd'),1,1,1)
запрос на Postgresql базе работает.

0FD,

ноя 26, 2013 2:18:33 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [jsp] in context with path [/MedStatPro] threw exception [An exception occurred processing JSP page /RepExcel3.jsp at line 78

75: // ins = ins.length()-1 + ");";
76: //out.println(ins);
77: stI = con.createStatement();
78: rsI = stI.executeUpdate(ins);
79:
80: Statement st = null;
81: ResultSet rs = null;


Stacktrace:] with root cause
org.postgresql.util.PSQLException: ERROR: syntax error at or near ";"
Позиция: 154
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
at org.apache.jsp.RepExcel3_jsp._jspService(RepExcel3_jsp.java:145)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.nodir.toshmi.EncodingFilter.doFilter(EncodingFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

0FD,
Сделал, но все еще ругается

org.apache.jasper.JasperException: An exception occurred processing JSP page /RepExcel3.jsp at line 78

77: stI = con.createStatement();
78: rsI = stI.executeUpdate(ins);


Может все таки executeUpdate(ins) как-то присвоить на переменную для каждого инсерта?

0FD,

так вот же 78-строка:
78: rsI = stI.executeUpdate(ins);

полностью ошибка вот:

ноя 26, 2013 2:36:33 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [jsp] in context with path [/MedStatPro] threw exception [An exception occurred processing JSP page /RepExcel3.jsp at line 78

75: // ins = ins.length()-1 + ");";
76: //out.println(ins);
77: stI = con.createStatement();
78: rsI = stI.executeUpdate(ins);
79:
80: Statement st = null;
81: ResultSet rs = null;


Stacktrace:] with root cause
org.postgresql.util.PSQLException: ERROR: syntax error at or near ";"
Позиция: 154
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
at org.apache.jsp.RepExcel3_jsp._jspService(RepExcel3_jsp.java:145)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.nodir.toshmi.EncodingFilter.doFilter(EncodingFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

0FD,

давно убрал
ins = ins +v+ ")";

Ругается именно на 78: rsI = stI.executeUpdate(ins);


Вобще можно все инсерты одним executeUpdate(ins) выполнять?

0FD,

Вот пож-та

JDBC_Con obj=new JDBC_Con();
Connection con;
con=obj.connect();

Statement stD = null;
String del = "Delete from Rep3_2;";
stD = con.createStatement();
int rsD = stD.executeUpdate(del);

Statement stI = null;
int rsI=0;
int cols=20;
int rows=16;
String v ="";
String ins = "";
String u = "val"; for (int i=1; i<=cols; i++) { u += ", S" + i; }
for (int i=1; i<=rows; i++) {
ins = "INSERT INTO Rep3_2 ("+u+") VALUES ";
v = "("+i;
for (int j=1; j<=cols; j++) {
v += ", (SELECT R3F1(to_date("+"'"+request.getParameter("d1")+"'"+","+"'"+"yyyy-mm-dd"+"'"+"),"+
"to_date("+"'"+request.getParameter("d2")+"'"+","+"'"+"yyyy-mm-dd"+"'"+")," +i+ "," +j+ ",1))";
}
ins = ins +v+ ")";
}
stI = con.createStatement();
rsI = stI.executeUpdate(ins);

А это весь jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" " http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*, java.util.*"%>
<%@ page import="org.nodir.toshmi.JDBC_Con" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SMTH</title>
</head>
<body LINK="blue">
<%
String exportToExcel = request.getParameter("exportToExcel");
if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "inline; filename=" + "Region_Otdel.xls");
}
%>
<table width=1100 align="center">
<th colspan="21">Худудлар ва бўлимлар бўйича шифохонадан чиқарилганлар. Бюджет.<br>В разрезе областей и отделов. Бюджет.</th>
</table><br>

<table width=1100 align="center" border="1">
<thead>
<tr>
<td align="center"></td>
<td align="center"><b>Ревматология</b></td>
<td align="center"><b>Эндокринология</b></td>
<td align="center"><b>терепия</b></td>
<td align="center"><b>Кардиология</b></td>
<td align="center"><b>Кардиореанимация</b></td>
<td align="center"><b>SMTH</b></td>
<td align="center"><b>Взрос.инфекция</b></td>
<td align="center"><b>Детская.инфекция</b></td>
<td align="center"><b>SMTH</b></td>
<td align="center"><b>Взрослая ЛОР</b></td>
<td align="center"><b>Детская ЛОР</b></td>
<td align="center"><b>Взр.хир.стом.</b></td>
<td align="center"><b>Дет.хир.стом.</b></td>
<td align="center"><b>Гинекология</b></td>
<td align="center"><b>Хир.реанимация</b></td>
<td align="center"><b>К-ка реабилитация</b></td>
<td align="center"><b>Гинекология реанимация</b></td>
<td align="center"><b>Нефрология</b></td>
<td align="center"><b>Неврология</b></td>
<td align="center"><b>Дет.хир.стом.(SMTH)</b></td>
</tr>
</thead>
<tbody>
<%
JDBC_Con obj=new JDBC_Con();
Connection con;
con=obj.connect();

Statement stD = null;
String del = "Delete from Rep3_2;";
stD = con.createStatement();
int rsD = stD.executeUpdate(del);

Statement stI = null;
int rsI=0;
int cols=20;
int rows=16;
String v ="";
String ins = "";
String u = "val"; for (int i=1; i<=cols; i++) { u += ", S" + i; }
for (int i=1; i<=rows; i++) {
ins = "INSERT INTO Rep3_2 ("+u+") VALUES ";
v = "("+i;
for (int j=1; j<=cols; j++) {
v += ", (SELECT R3F1(to_date("+"'"+request.getParameter("d1")+"'"+","+"'"+"yyyy-mm-dd"+"'"+"),"+
"to_date("+"'"+request.getParameter("d2")+"'"+","+"'"+"yyyy-mm-dd"+"'"+")," +i+ "," +j+ ",1))";
}
ins = ins +v+ ")";
}
stI = con.createStatement();
rsI = stI.executeUpdate(ins);

Statement st = null;
ResultSet rs = null;
String sel = "SELECT r1.uz_, r1.ru_, r1.val, r2.S1, r2.S2, r2.S3, r2.S4, r2.S5, r2.S6, r2.S7, r2.S8, r2.S9, r2.S10, "+
" r2.S11, r2.S12, r2.S13, r2.S14, r2.S15, r2.S16, r2.S17, r2.S18, r2.S19, r2.S20" +
" FROM Rep3_1 r1 "+
"FULL OUTER JOIN Rep3_2 r2 "+
" ON r1.val=r2.val "+
"ORDER BY r1.val;";
st = con.createStatement();
rs = st.executeQuery(sel);
while (rs.next()) {
%>
<tr><td><b><%=rs.getString(1)%></b></td>
<td><%=rs.getInt(4)%></td><td><%=rs.getInt(5)%></td><td><%=rs.getInt(6)%></td><td><%=rs.getInt(7)%></td><td><%=rs.getInt(8)%></td>
<td><%=rs.getInt(9)%></td><td><%=rs.getInt(10)%></td><td><%=rs.getInt(11)%></td><td><%=rs.getInt(12)%></td><td><%=rs.getInt(13)%></td>
<td><%=rs.getInt(14)%></td><td><%=rs.getInt(15)%></td><td><%=rs.getInt(16)%></td><td><%=rs.getInt(17)%></td><td><%=rs.getInt(18)%></td>
<td><%=rs.getInt(19)%></td><td><%=rs.getInt(20)%></td><td><%=rs.getInt(21)%></td><td><%=rs.getInt(22)%></td><td><%=rs.getInt(23)%></td></tr>
<% } %>
</tbody>
</table>
<br>  
<% if (exportToExcel == null) { %>
<a href="DepRegionsRep.jsp?exportToExcel=YES"><Font size="5" color="blue">Export to Excel</Font></a>
<% } %>
</body>
</html>
<%
try {
//con.commit();
con.close();
} catch (SQLException e) {
e.printStackTrace();
} %>