CODEWITHSUNDEEP
javadatabasems-accessjackcess
MCMeyersman
MCMeyersman

Reputation: 37

Jackcess columns out of order?

I have an application which downloads financial data from the YQL table yahoo.finance.quotes in an xml format, and then writes this data to an MS Access database. I am using Jackcess to interact with the database but I am having a strange issue with adding rows.

When I call .addRow() all of the fields are written to the file correctly except a few which are switched. Here's an example of an xml file I would download and use (with JAXB)... 

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:created="2013-08-01T18:03:53Z" yahoo:lang="en-US">
  <results>
    <quote>
      <BookValue>236.698</BookValue>
      <EarningsShare>34.565</EarningsShare>
      <EPSEstimateCurrentYear>43.54</EPSEstimateCurrentYear>
      <EPSEstimateNextYear>51.34</EPSEstimateNextYear>
      <EPSEstimateNextQuarter>11.82</EPSEstimateNextQuarter>
      <DaysLow>895.00</DaysLow>
      <DaysHigh>903.07</DaysHigh>
      <YearLow>623.41</YearLow>
      <YearHigh>928.00</YearHigh>
      <LastTradePriceOnly>901.83</LastTradePriceOnly>
      <FiftydayMovingAverage>893.586</FiftydayMovingAverage>
      <TwoHundreddayMovingAverage>856.999</TwoHundreddayMovingAverage>
      <Open>895.67</Open>
      <PreviousClose>887.75</PreviousClose>
      <PriceSales>5.30</PriceSales>
      <PriceBook>3.75</PriceBook>
      <PERatio>25.68</PERatio>
      <PEGRatio>1.41</PEGRatio>
      <PriceEPSEstimateCurrentYear>20.39</PriceEPSEstimateCurrentYear>
      <PriceEPSEstimateNextYear>17.29</PriceEPSEstimateNextYear>
      <ShortRatio>1.90</ShortRatio>
      <OneyrTargetPrice>985.32</OneyrTargetPrice>
      <Volume>1460983</Volume>
      <Ask>901.90</Ask>
      <AverageDailyVolume>2330360</AverageDailyVolume>
      <Bid>901.54</Bid>
    </quote>
  </results>
</query>
<!--  total: 222  -->
<!--  engine4.yql.ne1.yahoo.com  -->

And here's an xml export of the resulting access database... 

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2013-08-01T13:28:55">
  <GOOG>
    <ID>1375380249345</ID>
    <BOOK_VALUE>901.9</BOOK_VALUE>
    <EARNINGS_SHARE>3.75</EARNINGS_SHARE>
    <EPS_ESTIMATE_CURRENT_YEAR>34.565</EPS_ESTIMATE_CURRENT_YEAR>
    <ESP_ESTIMATE_NEXT_YEAR>43.54</ESP_ESTIMATE_NEXT_YEAR>
    <EPS_ESTIMATE_NEXT_QUARTER>51.34</EPS_ESTIMATE_NEXT_QUARTER>
    <DAYS_LOW>11.82</DAYS_LOW>
    <DAYS_HIGH>895</DAYS_HIGH>
    <YEAR_LOW>903.07</YEAR_LOW>
    <YEAR_HIGH>623.41</YEAR_HIGH>
    <LAST_TRADE_PRICE_ONLY>1.9</LAST_TRADE_PRICE_ONLY> <!-- Offending column -->
    <FIFTYDAY_MOVING_AVERAGE>928</FIFTYDAY_MOVING_AVERAGE>
    <TWO_HUNDREDDAY_MOVING_AVERAGE>901.83</TWO_HUNDREDDAY_MOVING_AVERAGE>
    <OPEN>893.586</OPEN>
    <PRICE_SALES>856.999</PRICE_SALES>
    <PRICE_BOOK>895.67</PRICE_BOOK>
    <PE_RATIO>5.3</PE_RATIO>
    <PEG_RATIO>3.75</PEG_RATIO>
    <PRICE_EPSESTIMATE_CURRENT_YEAR>25.68</PRICE_EPSESTIMATE_CURRENT_YEAR>
    <PRICE_EPS_ESTIMATE_NEXT_YEAR>1.41</PRICE_EPS_ESTIMATE_NEXT_YEAR>
    <ONEYR_TARGET_PRICE>985.32</ONEYR_TARGET_PRICE>
    <SHORT_RATIO>1460983</SHORT_RATIO> <!-- Offending column -->
    <VOLUME>2330360</VOLUME> <!-- Offending column -->
    <ASK>20.39</ASK>
    <AVERAGE_DAILY_VOLUME>901</AVERAGE_DAILY_VOLUME> <!-- Offending column -->
    <BID>17.29</BID>
  </GOOG>
</dataroot>

Here's my code to add the values from the xml to the database if it helps... 

public synchronized void storeData(Query query) throws InterruptedException, IOException {
    long idL = System.currentTimeMillis();
    BigDecimal id = new BigDecimal(idL);
    double bookValue = query.results.quote.getPriceBook();
    double earningsShare = query.results.quote.getEarningsShare();
    double epsEstimateCurrentYear = query.results.quote.getEPSEstimateCurrentYear();
    double epsEstimateNextYear = query.results.quote.getEPSEstimateNextYear();
    double epsEstimateNextQuarter = query.results.quote.getEPSEstimateNextQuarter();
    double daysLow = query.results.quote.getDaysLow();
    double daysHigh = query.results.quote.getDaysHigh();
    double yearLow = query.results.quote.getYearLow();
    double yearHigh = query.results.quote.getYearHigh();
    double lastTradePriceOnly = query.results.quote.getLastTradePriceOnly();
    double fiftydayMovingAverage = query.results.quote.getFiftydayMovingAverage();
    double twoHundreddayMovingAverage = query.results.quote.getTwoHundreddayMovingAverage();
    double open = query.results.quote.getOpen();
    double priceSales = query.results.quote.getPriceSales();
    double priceBook = query.results.quote.getPriceBook();
    double peRatio = query.results.quote.getPERatio();
    double pegRatio = query.results.quote.getPEGRatio();
    double priceEPSEstimateCurrentYear = query.results.quote.getPriceEPSEstimateCurrentYear();
    double priceEPSEstimateNextYear = query.results.quote.getPriceEPSEstimateNextYear();
    double shortRatio = query.results.quote.getShortRatio();
    double oneyrTargetPrice = query.results.quote.getOneyrTargetPrice();
    int volume = query.results.quote.getVolume();
    double ask = query.results.quote.getAsk();
    int averageDailyVolume = query.results.quote.getAverageDailyVolume();
    double bid = query.results.quote.getBid();
    dBTable.addRow(id, bookValue, earningsShare, epsEstimateCurrentYear, epsEstimateNextYear, epsEstimateNextQuarter, daysLow, daysHigh,
        yearLow, yearHigh, close, fiftydayMovingAverage, twoHundreddayMovingAverage, open, priceSales, priceBook,
            peRatio, pegRatio, priceEPSEstimateCurrentYear, priceEPSEstimateNextYear, shortRatio, oneyrTargetPrice, volume, ask, averageDailyVolume, bid);
}

As you can see the Last trade price only, Short ratio, Volume and Average daily volume are mixed around.

Could anyone tell me why this is and how to correct it? Thank you.

Upvotes: 1

Views: 375

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

At least part of your problem appears to be that the addRow() method in Jackcess requires the values to be provided "in the order of the Columns of the Table", (emphasis theirs, ref: the "Adding a Row" section here). Your addRow() call specifies

... shortRatio, oneyrTargetPrice, volume ...

but the XML dump of the Access table suggests that the column order is

<ONEYR_TARGET_PRICE>985.32</ONEYR_TARGET_PRICE>
<SHORT_RATIO>1460983</SHORT_RATIO> <!-- Offending column -->
<VOLUME>2330360</VOLUME> <!-- Offending column -->

You may also want to double-check the variable types against the column types to make sure that they match as well.

Upvotes: 3

Related Questions