topic Re: Problem with converting DB table to RTDM TABLE with datetime column - Groovy in SAS Customer Intelligence

Problem with converting DB table to RTDM TABLE with datetime column - Groovy

RTDM_User — Tue, 24 Sep 2019 12:57:49 GMT

Hi everybody,

I'm having problems get columns and data rows of datetime value from DB and make it a rtdm table (data grid),

something is wrong in the get...() function syntex,

trying to run the code:

import org.apache.log4j.Logger

import java.sql.Connection

import java.sql.PreparedStatement

import java.sql.ResultSet

import java.sql.SQLException

import org.apache.commons.logging.Log

import org.apache.commons.logging.LogFactory

import com.sas.analytics.ph.common.RTDMTable

import com.sas.analytics.ph.common.RTDMTable.Row

import com.sas.analytics.ph.common.exp.SymbolTable

import com.sas.analytics.ph.common.jaxb.DataTypes

import com.sas.rtdm.implementation.engine.EventInfo

import javax.sql.DataSource

RTDMTable TABLE;

TABLE = new RTDMTable();

TABLE.columnAdd("stringcol", DataTypes.STRING, Collections.emptyList());

TABLE.columnAdd("floatcol", DataTypes.FLOAT, Collections.emptyList());

TABLE.columnAdd("datecol", DataTypes.DATETIME, Collections.emptyList());

/*get rows and columns by query from DB

stmt = conn.prepareStatement(queryToSubmit)

Row newRow = TABLE.rowAdd()

newRow.columnDataSet("stringcol", rs.getString(1))

newRow.columnDataSet("floatcol", rs.getDouble(2))

newRow.columnDataSet("datecol", rs.getDateTime(3))

--------

ERROR:

Caused by: groovy.lang.MissingMethodException: No signature of method: org.apache.commons.dbcp.DelegatingResultSet.getDateTime() is applicable for argument types: (java.lang.Integer) values: [3]

Possible solutions: getDate(int), getTime(int), updateTime(int, java.sql.Time), getDate(java.lang.String), getDate(int, java.util.Calendar), getTime(java.lang.String)

HELP 🙂 ?

Re: Problem with converting DB table to RTDM TABLE with datetime column - Groovy

peh — Fri, 27 Sep 2019 11:04:58 GMT

Hi,

RTDM datatype DateTime is represented as Calendar in Java/Groovy.

Here is a piece of code that can be used to retrieve Calendar from ResultSet.

import java.sql.SQLException;

import java.sql.ResultSet;

import java.util.Calendar;

import java.sql.Date;

import java.util.TimeZone;

private Calendar getCalendar(ResultSet rs, String colName) throws SQLException {

Calendar c = Calendar.getInstance(timeZone);

Date d = rs.getDate(colName);

if (rs.wasNull()) return null;

else {

c.setTime(d);

return c;

}

You need to provide a TimeZone to create Calendar.

If you want to use client timezone, you can use the following code to retrieve client timezone as string.

import com.sas.rtdm.implementation.engine.EventInfo;

String timezoneId = evtInfo.getClientTimeZoneId();

Re: Problem with converting DB table to RTDM TABLE with datetime column - Groovy

JamesAnderson — Sun, 29 Sep 2019 11:48:07 GMT

in addition to defining the calendar object you also need to use rs.getTimestamp.

Cheers

James

Re: Problem with converting DB table to RTDM TABLE with datetime column - Groovy

RTDM_User — Mon, 28 Oct 2019 13:27:18 GMT

Thank you very much for the response,

where should i put it in my code?

how do i insert this "converted" column to an RTDM Table as described above?

"newRow.columnDataSet(..." ? OR other way?

its a multiple row table with 20 columns.

Re: Problem with converting DB table to RTDM TABLE with datetime column - Groovy

peh — Mon, 28 Oct 2019 15:08:54 GMT

Yes,

once you have an Calendar object instance, you use it as a 2nd argument of newRow.columnDataSet().

Also note as 1st argument, you can use also the column name (String). e.g. newRow.columnDataSet("myDateTimeCol", myCalendarInstance);

an example I used once, uses different method of new Datagrid row creation, however shows how to parse ResultSet into Datagrid for String, Long (RTDM Integer), Double and Calendar (RTDM Datetime). You can create new empty row and use columnDataSet as well.

if (rs != null) {
List<Object> list = new ArrayList<Object>();
while (rs.next()) {
//read values from ResultSet and add them into offerRepDatagrid
list.add(getLong(rs, "Col1"));
list.add(rs.getString("Col2"));
list.add(getDouble(rs, "Col3"));
list.add(getCalendar(rs, "Col4"));
offerRepDatagrid.rowAdd(list);
list.clear();
rsCount++;
}
}
if (rs != null) rs.close();

private Long getLong(ResultSet rs, String colName) throws SQLException {
long l = rs.getLong(colName);
return rs.wasNull() ? null : l;
}

private Double getDouble(ResultSet rs, String colName) throws SQLException {
Double d = rs.getDouble(colName);
return rs.wasNull() ? null : d;
}

private Calendar getCalendar(ResultSet rs, String colName) throws SQLException {
Calendar c = Calendar.getInstance(timeZone);
Date d = rs.getDate(colName);
if (rs.wasNull()) return null;
else {
c.setTime(d);
return c;
}
}