Cannot pass COLUMN value function.NAME functions

[katfood182]

Added a custom function, POW, exactly like the example. When I pass a COLUMN as a value it is either empty string or null.

What gives?

p.s. function will take hard coded values with no problem.

[simoc]

The following test program works correctly for me. Is this what you are trying to do?

package org.relique.jdbc.csv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Properties;

public class Test3
{
	public static double add(double a, double b)
	{
		return a + b;
	}
	
	public static String wrap(String a)
	{
		return "|<" + a + ">|";
	}
	
	public static void main(String[] args) throws SQLException, ClassNotFoundException, ParseException
	{
		Class.forName("org.relique.jdbc.csv.CsvDriver");
		
		String url = "jdbc:relique:csv:" + "/home/simon/workspace1/csvjdbc/src/testdata";
	    
	    Properties props = new Properties();
	    props.put("function.ADD", "org.relique.jdbc.csv.Test3.add(double, double)");
	    props.put("function.WRAP", "org.relique.jdbc.csv.Test3.wrap(String)");
		
	    Connection conn = DriverManager.getConnection(url, props);
	    
	    Statement stmt = conn.createStatement();
	    
	    String sql2 = "SELECT C1, C2, ADD(C1, C2), ADD(C1, 10), WRAP(C1) FROM numeric";
	    ResultSet results = stmt.executeQuery(sql2);
	    while (results.next())
	    {
	    	System.out.println(results.getDouble(1) + " " + results.getDouble(2) + " " +
	    		results.getDouble(3) + " " + results.getDouble(4) + " " + results.getString(5));
	    }
	}
}

Output is:

99.0 -1010.0 -911.0 109.0 |<99>|
-22.0 15.0 -7.0 -12.0 |<-22>|

[katfood182]

I call getMetaData after the executeQuery. This function is messing up the result set.

[simoc]

My example works correctly with CsvJdbc version 1.0.36, as well as with the most recent version 1.0.40.

My example also works correctly when Java.sql.Statement.execute() is called instead of executeQuery().

In general, this functionality does work, but maybe you have found a special case that does not work. Please provide a simple example that does not work.

[katfood182]

I call getMetaData after the executeQuery. This function is messing up the result set.

[simoc]

Thank you for the hint about where the problem is.

I get the following exception if I add a ResultSetMetaData meta = results.getMetaData(); call after executing the query in my test program above:

Exception in thread "main" java.sql.SQLException: ADD(null,null)
	at org.relique.jdbc.csv.SQLUserFunction.eval(SQLUserFunction.java:139)
	at org.relique.jdbc.csv.CsvResultSet.getMetaData(CsvResultSet.java:1503)
	at org.relique.jdbc.csv.Test3.main(Test3.java:41)
Caused by: java.lang.IllegalArgumentException
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.relique.jdbc.csv.SQLUserFunction.eval(SQLUserFunction.java:135)
	... 2 more

[simoc]

In this situation, CsvJdbc does not correctly determine what data types are passed to, and returned from, the Java method used as an SQL function.

A workaround for this problem is to define the data type of each column. After adding the following line to the test program, it works correctly:

props.put("columnTypes.numeric", "double,double,double,double,double,double,double");

[katfood182]

I really would like to see a TO_CHAR and TO_DATE function. What if I provide these or modify the getMetaData to scan the first 100 or so rows and not invalidate the user defined functions? Would you add these to the code base?

[simoc]

Yes, a TO_DATE(str, format) SQL function (like in PostgreSQL or Oracle) would be welcome.

I created #73 for this work.

[simoc]

Yes, a TO_CHAR(date/timestamp, format) SQL function (like in PostgreSQL or Oracle) would be welcome.

I created #74 for this work.

[simoc]

CsvJdbc normally keeps only one row in memory. If ORDER BY, GROUP BY, SQL aggregrate functions or JDBC scrollable result sets are used, then all rows are held in memory, because that is the simplest way to solve these operations.

Reading the first 100 rows in advance in CsvJdbc and combining these rows with the existing logic would be difficult to implement.

[katfood182]

I added the functions to the code base in my repo. Oddly enough getMetaData fails for all functions that do not take a String type. I am working on a fix to correctly identify the types.