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

Discussion:

(too old to reply)

bazzer

2009-07-06 15:53:48 UTC

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.

Basically I have a program getting a certain range of data from a
particular table in a SQL server database, copying that data into a
ResultSet, and then inserting that data from resultset into an oracle
database. The range of data selected from the SQL database, is based
on timestamps. So I basically select a range of data between 2
timestamps. This all works fine if i do it for small difference in
timestamps, say a 3 or 4 hours. The problem is when I try do this for
a longer period, maybe 10hours+.

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.

code sample:

//setup the drivers
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver
());
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

//connect to the MySQL database
Connection connSQL =
DriverManager.getConnection(
"jdbc:odbc:Owenreagh", "sws", "sws");
System.out.println("connected to SQL DB successfully");

//connect to the Oracle database
Connection connOra =
DriverManager.getConnection(
"jdbc:oracle:thin:@sws-oper-10:1521:SRC", "source",
"password");
System.out.println("connected to Oracle DB successfully");

//create statement for SQL connection
Statement mySQLstat = connSQL.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

//create statement for Oracle connection
Statement myOrastat = connOra.createStatement();

//create ResultSet for SQL data, and populate using SELECT
query
ResultSet rs = mySQLstat.executeQuery(
"SELECT * FROM Owenreagh.dbo.T_WTG06_10MINDATA " +
"WHERE ((T_WTG06_10MINDATA.TTimeStamp>{ts '2009-07-05
05:00:00'}) AND (T_WTG06_10MINDATA.TTimeStamp<{ts '2009-07-05
17:00:00'}))" +
"ORDER BY T_WTG06_10MINDATA.Id ASC");

//create ResultSetMetaData to get MetaData from ResultSet
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
System.out.println("number of columns = " + numberOfColumns);

//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"))
+ ")");

}//end of while(rs.next())

//close both statements
mySQLstat.close();
myOrastat.close();

//disconnect from SQL database
connSQL.close();
System.out.println("connection to SQL server closed
successfully");

//disconnect from Oracle database
connOra.close();
System.out.println("connection to Oracle server closed
successfully");

}//end of main

Lew

2009-07-07 00:05:01 UTC

Permalink

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

a***@clir.eco

2020-05-04 10:07:24 UTC

Permalink

Post by Lew

Presumably because the SQL query contains an "ORDER BY" clause, correct?

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

Hi Lew,

Do you know what the column Sys_Stats_TrbStat refers to? Looking at turbine data, and I'm curious if this corresponds to derating. Thanks!

Sabine Dinis Blochberger

2009-07-07 08:24:33 UTC

Permalink

Post by bazzer
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?

Post by bazzer
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.

Post by bazzer
//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

2009-07-07 15:42:38 UTC

Permalink

Thank you. In that case my program is working fine.

Thanks again.

Bazzer

a***@gmail.com

2015-10-02 13:17:18 UTC

Permalink

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

Post by Sabine Dinis Blochberger

Not sure what you are saying. Maybe you need to commit after each
insert?

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.

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>

a***@clir.eco

2020-05-04 10:06:48 UTC

Permalink

Post by a***@gmail.com
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!

Post by a***@gmail.com
Andreas

Post by Sabine Dinis Blochberger

Not sure what you are saying. Maybe you need to commit after each
insert?

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.

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>