bazzer wrote:
Hi
I am trying to insert the values of a resultset into an oracle
database. The problem is that they seem to be inserting in random
groups, as oppose to the way they are in the resultset.
Not sure what you are saying. Maybe you need to commit after each
insert?
The data appears in the correct order in the ResultSet. But in the
oracle DB, they appear in groups of timestamps. The oracle DB is
always empty before I insert anything into it. I cannot figure out
whey they do not insert into oracle in same order as in resultset.
Please see below a sample of my code. In case anyone needs to know the amount of data, the table in the SQL DB has 147 columns(all of which I
am selecting), and for 10hours of data, there are about 60 rows. Any
help is greatly appreciated.
The (physical) order of records in a given table in a RDBMS is not
defined, and you shouldn't count on a certain order. That is why ORDER
BY exists.
code sample:
//loop through each row of the ResultSet
while(rs.next()) {
//execute statement to copy the column values in
ResultSet, to corresponding column in the Oracle database
myOrastat.executeUpdate(
"INSERT INTO VESTASTURBINEDATA(ID, PROTOCOLVERSIONID, TTIMESTAMP, PCTIMESTAMP, PPMTIMESTAMP, GENRPMMAX, GENRPMMIN,
GENRPMAVG, GENRPMSTD, GENBEARTEMPAVG, GENPHASE1TEMPAVG," +
"GENPHASE2TEMPAVG, GENPHASE3TEMPAVG,
HYDOILTEMPAVG, GEAROILTEMPAVG, GEARBEARTEMPAVG, NACTEMPAVG, RTRRPMMAX, RTRRPMMIN, RTRRPMAVG," +
"AMBWINDSPEEDMAX, AMBWINDSPEEDMIN,
AMBWINDSPEEDAVG, AMBWINDSPEEDSTD, AMBWINDDIRRELATIVEAVG,
AMBWINDDIRABSAVG, AMBTEMPAVG," +
"PRODLATESTAVGACTPWRGEN0, PRODLATESTAVGACTPWRGEN1, PRODLATESTAVGACTPWRGEN2, PRODLATESTAVGTOTACTPWR, PRODLATESTAVGREACTPWRGEN0," +
"PRODLATESTAVGREACTPWRGEN1,
PRODLATESTAVGREACTPWRGEN2, PRODLATESTAVGTOTREACTPWR,
HVTRAFOPHASE1TEMPAVG, HVTRAFOPHASE2TEMPAVG," +
"HVTRAFOPHASE3TEMPAVG, GRDINVERTERPHASE1TEMPAVG, CONTGRNDTEMPAVG, CONTTOPTEMPAVG, CONTHUBTEMPAVG, CONTVCPTEMPAVG, GENSLIPRINGTEMPAVG," +
"SPINTEMPAVG, BLDSPITCHANGLEMIN,
BLDSPITCHANGLEMAX, BLDSPITCHANGLEAVG, BLDSPITCHANGLESTD, CONTVCPCHOKCOILTEMPAVG, GRDRTRINVPHASE1TEMPAVG," +
"GRDRTRINVPHASE2TEMPAVG, GRDRTRINVPHASE3TEMPAVG, CONTVCPWTRTEMPAVG, GRDPRODPWRAVG, GRDPRODCOSPHIAVG, GRDPRODFREQAVG, GRDPRODVOLTPHSE1AVG," +
"GRDPRODVOLTPHSE2AVG, GRDPRODVOLTPHSE3AVG, GRDPRODCURPHSE1AVG, GRDPRODCURPHSE2AVG, GRDPRODCURPHSE3AVG,
GRDPRODPWRMAX, GRDPRODPWRMIN," +
"GRDBUSBARTEMPAVG, HCNTAVGTOT, HCNTAVGGRDON, HCNTAVGGRDOK, HCNTAVGTRBOK, HCNTAVGRUN, HCNTAVGGEN1, HCNTAVGGEN2, HCNTAVGYAW, HCNTAVGSRVON," +
"HCNTAVGAMBOK, HCNTAVGWINDOK, RTRRPMSTD, AMBWINDSPEEDESTAVG, GRDPRODPWRSTD, GRDPRODREACTPWRAVG,
GRDPRODREACTPWRMAX, GRDPRODREACTPWRMIN," +
"GRDPRODREACTPWRSTD, GRDPRODPSBLEPWRAVG, GRDPRODPSBLEPWRMAX, GRDPRODPSBLEPWRMIN, GRDPRODPSBLEPWRSTD, GRDPRODPSBLEINDAVG, GRDPRODPSBLEINDMAX," +
"GRDPRODPSBLEINDMIN, GRDPRODPSBLEINDSTD, GRDPRODPSBLECAPAVG, GRDPRODPSBLECAPMAX, GRDPRODPSBLECAPMIN, GRDPRODPSBLECAPSTD, GENBEAR2TEMPAVG," +
"GEAROILTEMPBASISAVG, GEAROILTEMPLVL1AVG, GEAROILTEMPLVL23AVG, GEARBEARTEMPHSRTRENDAVG, GEARBEARTEMPHSGENENDAVG, GEARBEARTEMPHSMIDAVG," +
"GEARBEARTEMPHLWSHFTRTRAVG,
GEARBEARTEMPHLWSHFTGENAVG, HVTRAFOAIROUTLETTEMPAVG,
GENCOOLINGWATERTEMPAVG, SPINTEMPSLIPRINGAVG, HCNTAVGALARMACT," +
"SYSLOGSFIRSTACTALARMNO, SYSLOGSFIRSTACTALARMPAR1, SYSLOGSFIRSTACTALARMPAR2, NACDIRECTIONAVG, GRDSETACTPWRREFVALUE10MIN, GRDSETACTPWRSOURCE10MIN," +
"GRDSETREACTPWRREFVALUE10MIN, GRDSETREACTPWRFTRVALUE10MIN, GRDSETREACTPWRSOURCE10MIN,
HCNTTOTACCUMTOT, HCNTTOTACCUMGRDON, HCNTTOTACCUMGRDOK," +
"HCNTTOTACCUMTRBOK, HCNTTOTACCUMRUN,
HCNTTOTACCUMGEN1, HCNTTOTACCUMGEN2, HCNTTOTACCUMYAW,
HCNTTOTACCUMSRVON, HCNTTOTACCUMAMBOK, HCNTTOTACCUMWINDOK," +
"PRODTOTACCUMACTPWRGEN0, PRODTOTACCUMACTPWRGEN1, PRODTOTACCUMACTPWRGEN2, PRODTOTACCUMTOTACTPWR,
PRODTOTACCUMREACTPWRGEN0, PRODTOTACCUMREACTPWRGEN1," +
"PRODTOTACCUMREACTPWRGEN2,
PRODTOTACCUMTOTREACTPWR, GRDPRODPWRINTERNALDRTSTAT, GRDPRODPWRINTERNALDRTCHANGES, GRDPRODPWRINTERNALDRTTIME," +
"SYSSTATSTRBSTAT, NACDAMPERTEMPAVG, GEARLUBRICATIONOILTEMPAVG, GRDSETACTPWRMAXREFVALUE10MIN, GRDSETACTPWRMINREFVALUE10MIN, GRDSETACTPWRDERATETIME10MIN," +
"GRDSETACTPWRRMTDRTTIME10MIN) " +
"VALUES(" + (rs.getInt("Id")) + "," + (rs.getInt ("ProtocolVersionId")) +
",to_timestamp('" + (rs.getTimestamp
("TTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
",to_timestamp('" + (rs.getTimestamp
("PCTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
",to_timestamp('" + (rs.getTimestamp
("PPMTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
"," + (rs.getInt("Gen_RPM_Max")) + ", " + (rs.getInt("Gen_RPM_Min")) + ", " + (rs.getInt("Gen_RPM_Avg")) + ", "
+
(rs.getInt("Gen_RPM_Std")) + ", " + (rs.getInt ("Gen_Bear_Temp_Avg")) + ", " + (rs.getInt("Gen_Phase1_Temp_Avg")) +
", " + (rs.getInt("Gen_Phase2_Temp_Avg")) + ", " + (rs.getInt ("Gen_Phase3_Temp_Avg")) + ", " + (rs.getInt("Hyd_Oil_Temp_Avg")) + ",
" +
(rs.getInt("Gear_Oil_Temp_Avg")) + ", " + (rs.getInt("Gear_Bear_Temp_Avg")) + ", " + (rs.getInt("Nac_Temp_Avg"))
+ ", " + (rs.getInt("Rtr_RPM_Max")) + ", " + (rs.getInt
("Rtr_RPM_Min")) + ", " + (rs.getInt("Rtr_RPM_Avg")) + ", " + (rs.getInt("Amb_WindSpeed_Max")) + ", " +
(rs.getInt("Amb_WindSpeed_Min")) + ", " + (rs.getInt("Amb_WindSpeed_Avg")) + ", " + (rs.getInt
("Amb_WindSpeed_Std")) + ", " + (rs.getInt
("Amb_WindDir_Relative_Avg")) + ", " + (rs.getInt
("Amb_WindDir_Abs_Avg")) + ", " + (rs.getInt("Amb_Temp_Avg")) + ", "
+
(rs.getInt("Prod_LatestAvg_ActPwrGen0")) + ", " + (rs.getInt("Prod_LatestAvg_ActPwrGen1")) + ", " + (rs.getInt ("Prod_LatestAvg_ActPwrGen2")) + ", " + (rs.getInt ("Prod_LatestAvg_TotActPwr")) + ", " + (rs.getInt ("Prod_LatestAvg_ReactPwrGen0")) + ", " +
(rs.getInt("Prod_LatestAvg_ReactPwrGen1")) + ", "
+ (rs.getInt("Prod_LatestAvg_ReactPwrGen2")) + ", " + (rs.getInt ("Prod_LatestAvg_TotReactPwr")) + ", " + (rs.getInt ("HVTrafo_Phase1_Temp_Avg")) + ", " + (rs.getInt ("HVTrafo_Phase2_Temp_Avg")) + ", " +
(rs.getInt("HVTrafo_Phase3_Temp_Avg")) + ", " + (rs.getInt("Grd_InverterPhase1_Temp_Avg")) + ", " + (rs.getInt ("Cont_Grnd_Temp_Avg")) + ", " + (rs.getInt("Cont_Top_Temp_Avg")) + ",
" + (rs.getInt("Cont_Hub_Temp_Avg")) + ", " + (rs.getInt ("Cont_VCP_Temp_Avg")) + ", " +
(rs.getInt("Gen_SlipRing_Temp_Avg")) + ", " + (rs.getInt("Spin_Temp_Avg")) + ", " + (rs.getInt
("Blds_PitchAngle_Min")) + ", " + (rs.getInt("Blds_PitchAngle_Max")) +
", " + (rs.getInt("Blds_PitchAngle_Avg")) + ", " + (rs.getInt ("Blds_PitchAngle_Std")) + ", " +
(rs.getInt("Cont_VCP_ChokcoilTemp_Avg")) + ", " + (rs.getInt("Grd_RtrInvPhase1_Temp_Avg")) + ", " + (rs.getInt ("Grd_RtrInvPhase2_Temp_Avg")) + ", " + (rs.getInt ("Grd_RtrInvPhase3_Temp_Avg")) + ", " + (rs.getInt ("Cont_VCP_WtrTemp_Avg")) + ", " +
(rs.getInt("Grd_Prod_Pwr_Avg")) + ", " + (rs.getInt ("Grd_Prod_CosPhi_Avg")) + ", " + (rs.getInt("Grd_Prod_Freq_Avg")) +
", " + (rs.getInt("Grd_Prod_VoltPhse1_Avg")) + ", " + (rs.getInt ("Grd_Prod_VoltPhse2_Avg")) + ", " + (rs.getInt
("Grd_Prod_VoltPhse3_Avg")) + ", " +
(rs.getInt("Grd_Prod_CurPhse1_Avg")) + ", " + (rs.getInt("Grd_Prod_CurPhse2_Avg")) + ", " + (rs.getInt ("Grd_Prod_CurPhse3_Avg")) + ", " + (rs.getInt("Grd_Prod_Pwr_Max")) +
", " + (rs.getInt("Grd_Prod_Pwr_Min")) + ", " + (rs.getInt ("Grd_Busbar_Temp_Avg")) + ", " +
(rs.getInt("HCnt_Avg_Tot")) + ", " + (rs.getInt ("HCnt_Avg_GrdOn")) + ", " + (rs.getInt("HCnt_Avg_GrdOk")) + ", " + (rs.getInt("HCnt_Avg_TrbOk")) + ", " + (rs.getInt("HCnt_Avg_Run")) +
", " + (rs.getInt("HCnt_Avg_Gen1")) + ", " + (rs.getInt
("HCnt_Avg_Gen2")) + ", " +
(rs.getInt("HCnt_Avg_Yaw")) + ", " + (rs.getInt ("HCnt_Avg_SrvOn")) + ", " + (rs.getInt("HCnt_Avg_AmbOk")) + ", " + (rs.getInt("HCnt_Avg_WindOk")) + ", " + (rs.getInt("Rtr_RPM_Std")) +
", " + (rs.getInt("Amb_WindSpeed_Est_Avg")) + ", " + (rs.getInt ("Grd_Prod_Pwr_Std")) + ", " +
(rs.getInt("Grd_Prod_ReactPwr_Avg")) + ", " + (rs.getInt("Grd_Prod_ReactPwr_Max")) + ", " + (rs.getInt ("Grd_Prod_ReactPwr_Min")) + ", " + (rs.getInt
("Grd_Prod_ReactPwr_Std")) + ", " + (rs.getInt
("Grd_Prod_PsblePwr_Avg")) + ", " + (rs.getInt
("Grd_Prod_PsblePwr_Max")) + ", " +
(rs.getInt("Grd_Prod_PsblePwr_Min")) + ", " + (rs.getInt("Grd_Prod_PsblePwr_Std")) + ", " + (rs.getInt ("Grd_Prod_PsbleInd_Avg")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Max")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Min")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Std")) + ", " +
(rs.getInt("Grd_Prod_PsbleCap_Avg")) + ", " + (rs.getInt("Grd_Prod_PsbleCap_Max")) + ", " + (rs.getInt ("Grd_Prod_PsbleCap_Min")) + ", " + (rs.getInt
("Grd_Prod_PsbleCap_Std")) + ", " + (rs.getInt("Gen_Bear2_Temp_Avg"))
+ ", " + (rs.getInt("Gear_Oil_TempBasis_Avg")) + ", " +
(rs.getInt("Gear_Oil_TempLvl1_Avg")) + ", " + (rs.getInt("Gear_Oil_TempLvl2_3_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSRtrEnd_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSGenEnd_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSMid_Avg")) + ", " +
(rs.getInt("Gear_Bear_TempHlwShftRtr_Avg")) + ", "
+ (rs.getInt("Gear_Bear_TempHlwShftGen_Avg")) + ", " + (rs.getInt ("HVTrafo_AirOutlet_Temp_Avg")) + ", " + (rs.getInt ("Gen_CoolingWater_Temp_Avg")) + ", " + (rs.getInt ("Spin_Temp_SlipRing_Avg")) + ", " +
(rs.getInt("HCnt_Avg_AlarmAct")) + ", " + (rs.getInt("Sys_Logs_FirstActAlarmNo")) + ", " + (rs.getInt ("Sys_Logs_FirstActAlarmPar1")) + ", " + (rs.getInt ("Sys_Logs_FirstActAlarmPar2")) + ", " + (rs.getInt
("Nac_Direction_Avg")) + ", " +
(rs.getInt("Grd_Sets_ActPwr_ReferenceValue10Min"))
+ ", " + (rs.getInt("Grd_Sets_ActPwr_Source10Min")) + ", " + (rs.getInt ("Grd_Sets_ReactPwr_ReferenceValue10min")) + ", " + (rs.getInt ("Grd_Sets_ReactPwr_FactorValue10Min")) + ", " +
(rs.getInt("Grd_Sets_ReactPwr_Source10Min")) + ",
" + (rs.getInt("HCnt_TotAccumulated_Tot")) + ", " + (rs.getInt ("HCnt_TotAccumulated_GrdOn")) + ", " + (rs.getInt ("HCnt_TotAccumulated_GrdOk")) + ", " + (rs.getInt ("HCnt_TotAccumulated_TrbOk")) + ", " +
(rs.getInt("HCnt_TotAccumulated_Run")) + ", " + (rs.getInt("HCnt_TotAccumulated_Gen1")) + ", " + (rs.getInt ("HCnt_TotAccumulated_Gen2")) + ", " + (rs.getInt ("HCnt_TotAccumulated_Yaw")) + ", " + (rs.getInt ("HCnt_TotAccumulated_SrvOn")) + ", " +
(rs.getInt("HCnt_TotAccumulated_AmbOk")) + ", " + (rs.getInt("HCnt_TotAccumulated_WindOk")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen0")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen1")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen2")) + ", " +
(rs.getInt("Prod_TotAccumulated_TotActPwr")) + ",
" + (rs.getInt("Prod_TotAccumulated_ReactPwrGen0")) + ", " + (rs.getInt ("Prod_TotAccumulated_ReactPwrGen1")) + ", " + (rs.getInt ("Prod_TotAccumulated_ReactPwrGen2")) + ", " + (rs.getInt ("Prod_TotAccumulated_TotReactPwr")) + ", " +
(rs.getInt("Grd_Prod_Pwr_InternalDerateStat")) +
", " + (rs.getInt("Grd_Prod_Pwr_InternalDerateChanges")) + ", " + (rs.getInt("Grd_Prod_Pwr_InternalDerateTime")) + ", " + (rs.getInt ("Sys_Stats_TrbStat")) + ", " + (rs.getInt("Nac_Damper_Temp_Avg")) +
", " +
(rs.getInt("Gear_LubricationOil_Temp_Avg")) + ", "
+ (rs.getInt("Grd_Sets_ActPwr_MaxReferenceValue10Min")) + ", " + (rs.getInt("Grd_Sets_ActPwr_MinReferenceValue10Min")) + ", " + (rs.getInt("Grd_Sets_ActPwr_DerateTime10Min")) + ", " +
(rs.getInt("Grd_Sets_ActPwr_RmtDerateTime10Min"))
+ ")");
Much better/safer/easier to debug then String concatenation is to use a parametrized statement. <http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html>
Hello,
by chance I stumbled upon this post, and I would like to know if you have a full description of the columns in your code. E.g. "Gear_LubricationOil_Temp_Avg" is the average temperature of...
Or can you send me a link etc.?
Thanks a lot.
Andreas
On Tuesday, July 7, 2009 at 10:24:33 AM UTC+2, Sabine Dinis Blochberger wrote:
bazzer wrote:
Hi
I am trying to insert the values of a resultset into an oracle
database. The problem is that they seem to be inserting in random
groups, as oppose to the way they are in the resultset.
Not sure what you are saying. Maybe you need to commit after each
insert?
The data appears in the correct order in the ResultSet. But in the
oracle DB, they appear in groups of timestamps. The oracle DB is
always empty before I insert anything into it. I cannot figure out
whey they do not insert into oracle in same order as in resultset.
Please see below a sample of my code. In case anyone needs to know the amount of data, the table in the SQL DB has 147 columns(all of which I
am selecting), and for 10hours of data, there are about 60 rows. Any
help is greatly appreciated.
The (physical) order of records in a given table in a RDBMS is not
defined, and you shouldn't count on a certain order. That is why ORDER
BY exists.
code sample:
//loop through each row of the ResultSet
while(rs.next()) {
//execute statement to copy the column values in
ResultSet, to corresponding column in the Oracle database
myOrastat.executeUpdate(
"INSERT INTO VESTASTURBINEDATA(ID, PROTOCOLVERSIONID, TTIMESTAMP, PCTIMESTAMP, PPMTIMESTAMP, GENRPMMAX, GENRPMMIN,
GENRPMAVG, GENRPMSTD, GENBEARTEMPAVG, GENPHASE1TEMPAVG," +
"GENPHASE2TEMPAVG, GENPHASE3TEMPAVG,
HYDOILTEMPAVG, GEAROILTEMPAVG, GEARBEARTEMPAVG, NACTEMPAVG, RTRRPMMAX, RTRRPMMIN, RTRRPMAVG," +
"AMBWINDSPEEDMAX, AMBWINDSPEEDMIN,
AMBWINDSPEEDAVG, AMBWINDSPEEDSTD, AMBWINDDIRRELATIVEAVG, AMBWINDDIRABSAVG, AMBTEMPAVG," +
"PRODLATESTAVGACTPWRGEN0, PRODLATESTAVGACTPWRGEN1, PRODLATESTAVGACTPWRGEN2, PRODLATESTAVGTOTACTPWR, PRODLATESTAVGREACTPWRGEN0," +
"PRODLATESTAVGREACTPWRGEN1, PRODLATESTAVGREACTPWRGEN2, PRODLATESTAVGTOTREACTPWR, HVTRAFOPHASE1TEMPAVG, HVTRAFOPHASE2TEMPAVG," +
"HVTRAFOPHASE3TEMPAVG, GRDINVERTERPHASE1TEMPAVG, CONTGRNDTEMPAVG, CONTTOPTEMPAVG, CONTHUBTEMPAVG, CONTVCPTEMPAVG, GENSLIPRINGTEMPAVG," +
"SPINTEMPAVG, BLDSPITCHANGLEMIN,
BLDSPITCHANGLEMAX, BLDSPITCHANGLEAVG, BLDSPITCHANGLESTD, CONTVCPCHOKCOILTEMPAVG, GRDRTRINVPHASE1TEMPAVG," +
"GRDRTRINVPHASE2TEMPAVG, GRDRTRINVPHASE3TEMPAVG, CONTVCPWTRTEMPAVG, GRDPRODPWRAVG, GRDPRODCOSPHIAVG, GRDPRODFREQAVG, GRDPRODVOLTPHSE1AVG," +
"GRDPRODVOLTPHSE2AVG, GRDPRODVOLTPHSE3AVG, GRDPRODCURPHSE1AVG, GRDPRODCURPHSE2AVG, GRDPRODCURPHSE3AVG, GRDPRODPWRMAX, GRDPRODPWRMIN," +
"GRDBUSBARTEMPAVG, HCNTAVGTOT, HCNTAVGGRDON, HCNTAVGGRDOK, HCNTAVGTRBOK, HCNTAVGRUN, HCNTAVGGEN1, HCNTAVGGEN2, HCNTAVGYAW, HCNTAVGSRVON," +
"HCNTAVGAMBOK, HCNTAVGWINDOK, RTRRPMSTD, AMBWINDSPEEDESTAVG, GRDPRODPWRSTD, GRDPRODREACTPWRAVG, GRDPRODREACTPWRMAX, GRDPRODREACTPWRMIN," +
"GRDPRODREACTPWRSTD, GRDPRODPSBLEPWRAVG, GRDPRODPSBLEPWRMAX, GRDPRODPSBLEPWRMIN, GRDPRODPSBLEPWRSTD, GRDPRODPSBLEINDAVG, GRDPRODPSBLEINDMAX," +
"GRDPRODPSBLEINDMIN, GRDPRODPSBLEINDSTD, GRDPRODPSBLECAPAVG, GRDPRODPSBLECAPMAX, GRDPRODPSBLECAPMIN, GRDPRODPSBLECAPSTD, GENBEAR2TEMPAVG," +
"GEAROILTEMPBASISAVG, GEAROILTEMPLVL1AVG, GEAROILTEMPLVL23AVG, GEARBEARTEMPHSRTRENDAVG, GEARBEARTEMPHSGENENDAVG, GEARBEARTEMPHSMIDAVG," +
"GEARBEARTEMPHLWSHFTRTRAVG, GEARBEARTEMPHLWSHFTGENAVG, HVTRAFOAIROUTLETTEMPAVG, GENCOOLINGWATERTEMPAVG, SPINTEMPSLIPRINGAVG, HCNTAVGALARMACT," +
"SYSLOGSFIRSTACTALARMNO, SYSLOGSFIRSTACTALARMPAR1, SYSLOGSFIRSTACTALARMPAR2, NACDIRECTIONAVG, GRDSETACTPWRREFVALUE10MIN, GRDSETACTPWRSOURCE10MIN," +
"GRDSETREACTPWRREFVALUE10MIN, GRDSETREACTPWRFTRVALUE10MIN, GRDSETREACTPWRSOURCE10MIN,
HCNTTOTACCUMTOT, HCNTTOTACCUMGRDON, HCNTTOTACCUMGRDOK," +
"HCNTTOTACCUMTRBOK, HCNTTOTACCUMRUN, HCNTTOTACCUMGEN1, HCNTTOTACCUMGEN2, HCNTTOTACCUMYAW,
HCNTTOTACCUMSRVON, HCNTTOTACCUMAMBOK, HCNTTOTACCUMWINDOK," +
"PRODTOTACCUMACTPWRGEN0, PRODTOTACCUMACTPWRGEN1, PRODTOTACCUMACTPWRGEN2, PRODTOTACCUMTOTACTPWR,
PRODTOTACCUMREACTPWRGEN0, PRODTOTACCUMREACTPWRGEN1," +
"PRODTOTACCUMREACTPWRGEN2,
PRODTOTACCUMTOTREACTPWR, GRDPRODPWRINTERNALDRTSTAT, GRDPRODPWRINTERNALDRTCHANGES, GRDPRODPWRINTERNALDRTTIME," +
"SYSSTATSTRBSTAT, NACDAMPERTEMPAVG, GEARLUBRICATIONOILTEMPAVG, GRDSETACTPWRMAXREFVALUE10MIN, GRDSETACTPWRMINREFVALUE10MIN, GRDSETACTPWRDERATETIME10MIN," +
"GRDSETACTPWRRMTDRTTIME10MIN) " +
"VALUES(" + (rs.getInt("Id")) + "," + (rs.getInt ("ProtocolVersionId")) +
",to_timestamp('" + (rs.getTimestamp
("TTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
",to_timestamp('" + (rs.getTimestamp
("PCTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
",to_timestamp('" + (rs.getTimestamp ("PPMTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" +
"," + (rs.getInt("Gen_RPM_Max")) + ", " + (rs.getInt("Gen_RPM_Min")) + ", " + (rs.getInt("Gen_RPM_Avg")) + ", "
+
(rs.getInt("Gen_RPM_Std")) + ", " + (rs.getInt ("Gen_Bear_Temp_Avg")) + ", " + (rs.getInt("Gen_Phase1_Temp_Avg")) +
", " + (rs.getInt("Gen_Phase2_Temp_Avg")) + ", " + (rs.getInt ("Gen_Phase3_Temp_Avg")) + ", " + (rs.getInt("Hyd_Oil_Temp_Avg")) + ",
" +
(rs.getInt("Gear_Oil_Temp_Avg")) + ", " + (rs.getInt("Gear_Bear_Temp_Avg")) + ", " + (rs.getInt("Nac_Temp_Avg"))
+ ", " + (rs.getInt("Rtr_RPM_Max")) + ", " + (rs.getInt
("Rtr_RPM_Min")) + ", " + (rs.getInt("Rtr_RPM_Avg")) + ", " + (rs.getInt("Amb_WindSpeed_Max")) + ", " +
(rs.getInt("Amb_WindSpeed_Min")) + ", " + (rs.getInt("Amb_WindSpeed_Avg")) + ", " + (rs.getInt ("Amb_WindSpeed_Std")) + ", " + (rs.getInt
("Amb_WindDir_Relative_Avg")) + ", " + (rs.getInt ("Amb_WindDir_Abs_Avg")) + ", " + (rs.getInt("Amb_Temp_Avg")) + ", "
+
(rs.getInt("Prod_LatestAvg_ActPwrGen0")) + ", " + (rs.getInt("Prod_LatestAvg_ActPwrGen1")) + ", " + (rs.getInt ("Prod_LatestAvg_ActPwrGen2")) + ", " + (rs.getInt ("Prod_LatestAvg_TotActPwr")) + ", " + (rs.getInt ("Prod_LatestAvg_ReactPwrGen0")) + ", " +
(rs.getInt("Prod_LatestAvg_ReactPwrGen1")) + ", "
+ (rs.getInt("Prod_LatestAvg_ReactPwrGen2")) + ", " + (rs.getInt ("Prod_LatestAvg_TotReactPwr")) + ", " + (rs.getInt ("HVTrafo_Phase1_Temp_Avg")) + ", " + (rs.getInt ("HVTrafo_Phase2_Temp_Avg")) + ", " +
(rs.getInt("HVTrafo_Phase3_Temp_Avg")) + ", " + (rs.getInt("Grd_InverterPhase1_Temp_Avg")) + ", " + (rs.getInt ("Cont_Grnd_Temp_Avg")) + ", " + (rs.getInt("Cont_Top_Temp_Avg")) + ",
" + (rs.getInt("Cont_Hub_Temp_Avg")) + ", " + (rs.getInt ("Cont_VCP_Temp_Avg")) + ", " +
(rs.getInt("Gen_SlipRing_Temp_Avg")) + ", " + (rs.getInt("Spin_Temp_Avg")) + ", " + (rs.getInt
("Blds_PitchAngle_Min")) + ", " + (rs.getInt("Blds_PitchAngle_Max")) +
", " + (rs.getInt("Blds_PitchAngle_Avg")) + ", " + (rs.getInt ("Blds_PitchAngle_Std")) + ", " +
(rs.getInt("Cont_VCP_ChokcoilTemp_Avg")) + ", " + (rs.getInt("Grd_RtrInvPhase1_Temp_Avg")) + ", " + (rs.getInt ("Grd_RtrInvPhase2_Temp_Avg")) + ", " + (rs.getInt ("Grd_RtrInvPhase3_Temp_Avg")) + ", " + (rs.getInt ("Cont_VCP_WtrTemp_Avg")) + ", " +
(rs.getInt("Grd_Prod_Pwr_Avg")) + ", " + (rs.getInt ("Grd_Prod_CosPhi_Avg")) + ", " + (rs.getInt("Grd_Prod_Freq_Avg")) +
", " + (rs.getInt("Grd_Prod_VoltPhse1_Avg")) + ", " + (rs.getInt ("Grd_Prod_VoltPhse2_Avg")) + ", " + (rs.getInt ("Grd_Prod_VoltPhse3_Avg")) + ", " +
(rs.getInt("Grd_Prod_CurPhse1_Avg")) + ", " + (rs.getInt("Grd_Prod_CurPhse2_Avg")) + ", " + (rs.getInt ("Grd_Prod_CurPhse3_Avg")) + ", " + (rs.getInt("Grd_Prod_Pwr_Max")) +
", " + (rs.getInt("Grd_Prod_Pwr_Min")) + ", " + (rs.getInt ("Grd_Busbar_Temp_Avg")) + ", " +
(rs.getInt("HCnt_Avg_Tot")) + ", " + (rs.getInt ("HCnt_Avg_GrdOn")) + ", " + (rs.getInt("HCnt_Avg_GrdOk")) + ", " + (rs.getInt("HCnt_Avg_TrbOk")) + ", " + (rs.getInt("HCnt_Avg_Run")) +
", " + (rs.getInt("HCnt_Avg_Gen1")) + ", " + (rs.getInt ("HCnt_Avg_Gen2")) + ", " +
(rs.getInt("HCnt_Avg_Yaw")) + ", " + (rs.getInt ("HCnt_Avg_SrvOn")) + ", " + (rs.getInt("HCnt_Avg_AmbOk")) + ", " + (rs.getInt("HCnt_Avg_WindOk")) + ", " + (rs.getInt("Rtr_RPM_Std")) +
", " + (rs.getInt("Amb_WindSpeed_Est_Avg")) + ", " + (rs.getInt ("Grd_Prod_Pwr_Std")) + ", " +
(rs.getInt("Grd_Prod_ReactPwr_Avg")) + ", " + (rs.getInt("Grd_Prod_ReactPwr_Max")) + ", " + (rs.getInt ("Grd_Prod_ReactPwr_Min")) + ", " + (rs.getInt
("Grd_Prod_ReactPwr_Std")) + ", " + (rs.getInt
("Grd_Prod_PsblePwr_Avg")) + ", " + (rs.getInt
("Grd_Prod_PsblePwr_Max")) + ", " +
(rs.getInt("Grd_Prod_PsblePwr_Min")) + ", " + (rs.getInt("Grd_Prod_PsblePwr_Std")) + ", " + (rs.getInt ("Grd_Prod_PsbleInd_Avg")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Max")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Min")) + ", " + (rs.getInt
("Grd_Prod_PsbleInd_Std")) + ", " +
(rs.getInt("Grd_Prod_PsbleCap_Avg")) + ", " + (rs.getInt("Grd_Prod_PsbleCap_Max")) + ", " + (rs.getInt ("Grd_Prod_PsbleCap_Min")) + ", " + (rs.getInt
("Grd_Prod_PsbleCap_Std")) + ", " + (rs.getInt("Gen_Bear2_Temp_Avg"))
+ ", " + (rs.getInt("Gear_Oil_TempBasis_Avg")) + ", " +
(rs.getInt("Gear_Oil_TempLvl1_Avg")) + ", " + (rs.getInt("Gear_Oil_TempLvl2_3_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSRtrEnd_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSGenEnd_Avg")) + ", " + (rs.getInt ("Gear_Bear_TempHSMid_Avg")) + ", " +
(rs.getInt("Gear_Bear_TempHlwShftRtr_Avg")) + ", "
+ (rs.getInt("Gear_Bear_TempHlwShftGen_Avg")) + ", " + (rs.getInt ("HVTrafo_AirOutlet_Temp_Avg")) + ", " + (rs.getInt ("Gen_CoolingWater_Temp_Avg")) + ", " + (rs.getInt ("Spin_Temp_SlipRing_Avg")) + ", " +
(rs.getInt("HCnt_Avg_AlarmAct")) + ", " + (rs.getInt("Sys_Logs_FirstActAlarmNo")) + ", " + (rs.getInt ("Sys_Logs_FirstActAlarmPar1")) + ", " + (rs.getInt ("Sys_Logs_FirstActAlarmPar2")) + ", " + (rs.getInt ("Nac_Direction_Avg")) + ", " +
(rs.getInt("Grd_Sets_ActPwr_ReferenceValue10Min"))
+ ", " + (rs.getInt("Grd_Sets_ActPwr_Source10Min")) + ", " + (rs.getInt ("Grd_Sets_ReactPwr_ReferenceValue10min")) + ", " + (rs.getInt ("Grd_Sets_ReactPwr_FactorValue10Min")) + ", " +
(rs.getInt("Grd_Sets_ReactPwr_Source10Min")) + ",
" + (rs.getInt("HCnt_TotAccumulated_Tot")) + ", " + (rs.getInt ("HCnt_TotAccumulated_GrdOn")) + ", " + (rs.getInt ("HCnt_TotAccumulated_GrdOk")) + ", " + (rs.getInt ("HCnt_TotAccumulated_TrbOk")) + ", " +
(rs.getInt("HCnt_TotAccumulated_Run")) + ", " + (rs.getInt("HCnt_TotAccumulated_Gen1")) + ", " + (rs.getInt ("HCnt_TotAccumulated_Gen2")) + ", " + (rs.getInt ("HCnt_TotAccumulated_Yaw")) + ", " + (rs.getInt ("HCnt_TotAccumulated_SrvOn")) + ", " +
(rs.getInt("HCnt_TotAccumulated_AmbOk")) + ", " + (rs.getInt("HCnt_TotAccumulated_WindOk")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen0")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen1")) + ", " + (rs.getInt ("Prod_TotAccumulated_ActPwrGen2")) + ", " +
(rs.getInt("Prod_TotAccumulated_TotActPwr")) + ",
" + (rs.getInt("Prod_TotAccumulated_ReactPwrGen0")) + ", " + (rs.getInt ("Prod_TotAccumulated_ReactPwrGen1")) + ", " + (rs.getInt ("Prod_TotAccumulated_ReactPwrGen2")) + ", " + (rs.getInt ("Prod_TotAccumulated_TotReactPwr")) + ", " +
(rs.getInt("Grd_Prod_Pwr_InternalDerateStat")) +
", " + (rs.getInt("Grd_Prod_Pwr_InternalDerateChanges")) + ", " + (rs.getInt("Grd_Prod_Pwr_InternalDerateTime")) + ", " + (rs.getInt ("Sys_Stats_TrbStat")) + ", " + (rs.getInt("Nac_Damper_Temp_Avg")) +
", " +
(rs.getInt("Gear_LubricationOil_Temp_Avg")) + ", "
+ (rs.getInt("Grd_Sets_ActPwr_MaxReferenceValue10Min")) + ", " + (rs.getInt("Grd_Sets_ActPwr_MinReferenceValue10Min")) + ", " + (rs.getInt("Grd_Sets_ActPwr_DerateTime10Min")) + ", " +
(rs.getInt("Grd_Sets_ActPwr_RmtDerateTime10Min"))
+ ")");
Much better/safer/easier to debug then String concatenation is to use a parametrized statement. <http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html>
bazzer wrote:
I am trying to insert the values of a resultset [sic] into an oracle [sic] database. The problem is that they seem to be inserting in random
groups, as oppose to the way they are in the resultset [sic].
...
The data appears in the correct order in the ResultSet. But in the
Presumably because the SQL query contains an "ORDER BY" clause, correct?
oracle [sic] DB, they appear in groups of timestamps. The oracle [sic] DB is
always empty before I insert anything into it. I cannot figure out
whey they do not insert into oracle [sic] in same order as in resultset [sic].
Relational tables, such as those implemented in an Oracle DB, do not have an intrinsic order. Any logic or design that depends on a specific row order within a table is doomed to failure. SQL engines are free to store data in any order they choose within each table, to change that order from time to time, and to retrieve rows in any order not necessarily matching the internal table row order, absent an "ORDER BY" clause in the query.
The only way to guarantee a row order from a relational table is to query that
table (or group of tables) with an "ORDER BY" clause.
This is a fundamental truth of relational databases.
--
Lew
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 56:16:15 |
Calls: | 6,652 |
Calls today: | 4 |
Files: | 12,200 |
Messages: | 5,330,867 |