Supported datatypes

Inside SPWrapper there is a sort of stack of type descriptors. At the bottom there is the net.sourceforge.spwrapper.StandardMappings.xml content. Each change to this file affects the code created unless more specific configuration file exists for the current database.

The Oracle introspector has it own types, or overrides the StandardMappings types. You could browse the net.sourceforge.spwrapper.oracle.OracleMappings.xml for details.

Moreover Aliases table have been introduced to replaces db specific types with those described into StandardMappings.xml.

This patter is reapeated for each language binding, so you will find a StandardMappings.xml for Java, one for Python and so on.

This page describes the available types database by database. At the end o the page you will find a Python specific section.

Standard Mappings [JAVA]

These types are the minimum commom dividers between all the databases, you can always try to use them if you have mapping problems. As you can see it is the same table reported into add database

Oracle [JAVA]

The datatypes supported are the same supproted by the stored procedure wrapper generator. Many tests have been done with Oracle 10, but it seems working also with older releases.

Dyntable explained

Often Oracle stored procedure returns result set as output parameters.

SPWrapper previous releases only allowed to call a callback function to read these results, this because no information in provided by the db on the returned result set before procedure execution. The result set content could also vary depending on procedure parameters.

DYNTABLE make it easyer to deal with these result set. You simply have to provide an overry rule in the ant makefile to turn a result set output into a dyntable output.

For each dyntable parameter this snippet is added to the generated source code

                java.sql.ResultSet r_X_RIGHE_CONTAB=(narrowedStatement).getCursor(8);
                if (r_X_RIGHE_CONTAB != null) {
			ResultSetMetaData meta = r_X_RIGHE_CONTAB.getMetaData();
			while (r_X_RIGHE_CONTAB.next()) {
				HashMap<String, Object> row = new HashMap<String, Object>();
				int colNo = meta.getColumnCount();
				for (int i = 1; i <= colNo; i++) {
					String name = meta.getColumnName(i);
					row.put(name, r_X_RIGHE_CONTAB.getObject(i));
				}
				X_RIGHE_CONTAB.add(row);
			}
		}

X_RIGHE_CONTAB is the output parameter name, the result set. The result set is iterated and a new hashmap is created for each row, then each column object is read out with getObject, and pushed into the hashmap.

Notice that the Oracle JDBC driver returns column names all uppercase, so the Hash key are all uppercase strings.

The actual hashmap value is always the object returned by the jdbc driver, so some surprise can occour. For instance Number types are converted to BigDecimals!

If you are using the code inside Tomcat you have one furter step to do before using this code. You have to make the underlying connection directly available to SPWrapper. In fact Tomcat hides the native connection inside its pooling object, but we need the proprietary getCursor method to retrive the data.

Open you context file and add change the Resource tag as follows

                    <Resource auth="Container" 
                        driverClassName="oracle.jdbc.OracleDriver" 
                        name="jdbc/xxxx" 
                        password="xxxx" 
                        type="javax.sql.DataSource" 
                        url="jdbc:oracle:thin:@10.10.10.10:1521:ORCL" 
                        username="yyyyy"
                        accessToUnderlyingConnectionAllowed='true'
                />

The key is to add the accessToUnderlyingConnectionAllowed='true' attribute to the resource.

Now you are ready to use DYNTABLEs inside your web pages.

Postgresql [JAVA]

The reference release used is 8.2, we used the postgresql-8.2-504.jdbc3.jar jdbc driver. No custom mapping file has been provided, therefore refer to standard mappings for the list of available types.

The table below reports the curent alias map:

MySql [JAVA]

The reference release used is 5.0.37, we used the mysql-connector-java-5.0.5-bin.jar jdbc driver. No custom mapping file has been provided, therefore refer to standard mappings for the list of available types.

No alias map has been provided, everything seems work correctly with strings, integers, float and dates.

Very little test have been done with this platform

SqlServer [JAVA]

The reference release used is SqlServer 2005, we used the sqljdbc_1.1\ita\sqljdbc.jar jdbc driver. No custom mapping file has been provided, therefore refer to standard mappings for the list of available types.

Very little test have been done with this platform

The table below reports the curent alias map:

Standard Mappings [PYTHON]

The standar mappings for python is in the src/net/sourceforge/spwrapper/python directory

Note about timestamps/times with time zones: I need some valid examples about timezone before adding the support for these types. Can you help me? :)

Oracle [PYTHON]

NUMBER, VARCHAR2, LONG and BYNARY_DOUBLE are also supported. Timestamp with time zone or local time zone are simply mapped to timestamps, sorry.

Postgresql [PYTHON]

The same alias table of Java is used, the accessible types are the same