Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #760

Re: statement.executeUpdate(String sql) - not inserting results in correct order

Show all headers | View raw

On Friday, October 2, 2015 at 3:17:21 PM UTC+2, andrea...@gmail.com wrote:
> 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.
> 


Hi Andreas,

I also am interested. Did you get a reply?
Working on turbine data!


> 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>

Back to comp.lang.java.databases | Previous | Next — Previous in thread | Find similar

Thread

Re: statement.executeUpdate(String sql) - not inserting results in  correct order andreas.mengel@gmail.com - 2015-10-02 06:17 -0700
  Re: statement.executeUpdate(String sql) - not inserting results in correct order aram@clir.eco - 2020-05-04 03:06 -0700

csiph-web