mallox007 / sqlsheet Goto Github PK

https://groups.google.com/forum/?fromgroups#!topic/scriptella/wr_dkDaQsKQ

When I try to load a spreadsheet that is only 20MB, it uses a massive amount of 
memory and eventually I get a java.lang.OutOfMemoryError exception thrown. I 
went into the scriptella bin and added -Xmx1536m to try and get scriptella to 
use more memory but even with 1.5GB of memory, it stills throws this exception 
on only a 20MB spreadsheet. 

Apr 26, 2012 11:33:18 AM <SEVERE> Script scripts/integration/npcDrop.etl.xml 
execution failed.
Java heap space
Apr 26, 2012 11:33:18 AM <SEVERE> Scriptella bug report. Submit to issue 
tracker.
Scriptella version: 1.1-SNAPSHOT
Exception: 
scriptella.execution.EtlExecutorException: Java heap space
    at scriptella.execution.EtlExecutor.execute(EtlExecutor.java:190)
    at scriptella.tools.launcher.EtlLauncher.execute(EtlLauncher.java:276)
    at scriptella.tools.launcher.EtlLauncher.launch(EtlLauncher.java:193)
    at scriptella.tools.launcher.EtlLauncher.main(EtlLauncher.java:321)
Caused by: java.lang.OutOfMemoryError: Java heap space

Deploy to Maven Central Repository

What steps will reproduce the problem?
1. Set up a connection to an excel sheet
2. create a prepared statement like "SELECT * FROM ..."
3. call XlsStreamResultSet.getString("[FIRST_COLUMN_NAME]") where 
[FIRST_COLUMN_NAME] is the name of the first column in the excel sheet.

What is the expected output? What do you see instead?
I expect to get the string value from the first column in the current row but I 
get java.lang.ArrayIndexOutOfBoundsException: -1. For other columns the 
received value will be from the column right before.

What version of the product are you using? On what operating system?
6.3

Please provide any additional information below.
The problematic source can be found at XlsStreamResultSet:83
Remove the -1 and it will work.

If you give me commit rights, then I can provide a patch.

What steps will reproduce the problem?
1. Open a streaming connection to a .xlsx
The table has the following content

PARENT CHILD MIN MAX
[]     FOO   1   1           (where [] is an empty cell)

2. create a PreparedStatement "SELECT * FROM ..." and execute it
3. read the values with: resultSet.getString("PARENT"), 
resultSet.getString("CHILD"), resultSet.getString("MIN") and 
resultSet.getString("MAX")

What is the expected output? What do you see instead?
The expected results are
resultSet.getString("PARENT") > null
resultSet.getString("CHILD")  > "FOO"
resultSet.getString("MIN")    > "1"
resultSet.getString("MAX")    > "1"

but was
resultSet.getString("PARENT") > "FOO"
resultSet.getString("CHILD")  > "1"
resultSet.getString("MIN")    > "1"
resultSet.getString("MAX")    > null

Is there some "skip-empty-cells" mechanism in Sqlsheet?

What version of the product are you using? On what operating system?
6.3 and 6.5-SNAPSHOT

<connection id="xlsx" 
url="jdbc:xls:file:${quest.reward.filename}?readStreaming=true"/>
...
<query connection-id="xlsx">
    SELECT * FROM Quests;
    <script connection-id="log">
        ${etl.getParameter('Quest ID')} $EXP
    </script>
</query>

The data in the spreadsheet looks like this...

Zone ID Level   Quest ID    GUID    Quest Name  EXP Gold    Bag Type    Item ID Item 
Rarity  Item Name   Quantity    Class

9   30  902 QUEST-9-902 Sic Semper 
Proditores  144000  -   class   15722   Uncommon    Infiltrator's Gauntlets 1   lancer
9   30  902 QUEST-9-902 



15722   Uncommon    Infiltrator's Gauntlets 1   berserker
9   30  902 QUEST-9-902 



15725   Uncommon    Infiltrator's Grips 1   warrior

And I get this for the output..

902 144000
902 Uncommon
902 Uncommon

So when that EXP column is empty/null, for some reason it returns data from the 
Item Rarity column

I am not sure if this should be categorized as issue or an additional feature.
If you have a date formatted cell, the ResultSet.getString method returns the 
representation value of String and not the date value in standard format.

Consider a date "22. Dezember 1963" - a german localized date then you get 
exact string. When it should return the date which can be used directly to 
store in Database. 

Now there is another method getTimeStamp to return date from resultset but it 
throws null pointer exception if the cell is empty. This should be null value 
instead.

Any empty cell  withResultSet.get<Number> methods (e.g. getInt(), getFloat(), 
...).

In JDBC with real database return zero value not throwing NullPointerException 
or return null value.

It seems it can be fixed by simplely check "cell.doubleValue == null" in 
get<Number> methods like this:

return (<type>) ((cell == null) ? 0.0D : cell.doubleValue.doubleValue());
  --> 
return (<type>) ((cell == null || cell.doubleValue == null) ? 0.0D : 
cell.doubleValue.doubleValue());

It's a cool project , but , 

only support select * ?
Not support select count(*) ?

http://mvnrepository.com/artifact/net.sf.jsqlparser/jsqlparser/0.8.0

Right now first row must always contain names of the column. If I don't have 
such row I'll have issues with driver. 
So the switch can be as following:
- Column names will be taken from first row
- Column names will be taken from EXCEL file(A,B,C.. etc)

What steps will reproduce the problem? Do you have sample xls(x) file?
1. create connection to .xlsx
2. create some statement and execute it
3. depending on the value of "readStreaming" in the connection string you get a 
com.googlecode.sqlsheet.stream.XlsStreamResultSet (true) or a 
net.pcal.sqlsheet.XlsResultSet (false)
4. perform getString() where the undelying cell has numeric value

What is the expected output? What do you see instead?
Both implementations act different. c.g.s.s.XlsStreamResult.getString() results 
in some string representation of the number.
n.p.s.XlsResultSet.getString() thorws a SQLException (type mismatch)


Found that while fixing Issue 7. Now with commit rights should I check in test 
cases that show the problem but breakt the build?

What steps will reproduce the problem?
    Class.forName("net.pcal.sqlsheet.XlsDriver");
        Connection excelConnection = DriverManager.getConnection("jdbc:xls:file:///C:\\Test.xlsx");
        Statement xlsStatement = excelConnection.createStatement();
        ResultSet resultSet = null;
        resultSet = xlsStatement.executeQuery("select * from Test");
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int iNumCols = resultSetMetaData.getColumnCount();
        for (int i = 1; i <= iNumCols; i++) {
            System.out.println(resultSetMetaData.getColumnLabel(i)
                    + "  " +
                    resultSetMetaData.getColumnTypeName(i));
        }

What is the expected output? What do you see instead?
column datatype is null

What version of the product are you using? On what operating system?

sqlsheet 5
Please provide any additional information below.

https://groups.google.com/forum/?fromgroups#!topic/scriptella/wr_dkDaQsKQ

Sometimes after reading a spreadsheet, after all of the statements have been 
executed and it attempts to close the connection on the xls driver, it seems 
like it rewrites the entire xls file? I have noticed at times the file size 
will shrink which I assume is due to a difference in compression in how the 
sheet is being saved as; but the big issue here is that if anything happens 
during this period, or if there is an exception during the processing phase, 
the context is not closed properly and the xls file is basically truncated and 
you're left with a spreadsheet that has 0 bytes. Is the only way to avoid this 
making all of the source files read-only?