At application startup, the SQL repository accesses the database to determine the JDBC type of each property in the repository template. This enables the SQL repository to use the appropriate JDBC type in creating SQL queries. For large database schemas, however, getting the JDBC type of every column in your schema can take an unacceptably long time. If you find this to be a problem, there are several approaches you can take:
Set the sql-type attribute for All Repository Item Properties
You can explicitly set the sql-type or sql-types attribute for every non-transient property in your repository template. These attributes are attributes of the <property> element. For example:
<property name="winning_number" data-type="long" sql-type="numeric" />
If you set the sql-type or sql-types attribute for every non-transient property in your repository template, then the SQL repository does not attempt to check the JDBC type of your properties.
One drawback of this approach is that the sql-type can vary depending on your database software vendor. If you set the sql-type attribute explicitly, you will need to review and update the value if you move your repository implementation to a different vendor’s database software.
Set the safeSQLTypes Property in the GSARepository Component
The GSARepository class includes a property named safeSQLTypes. This property can be set to a comma-separated list of SQL types for which the repository will always use the default type. You can set this property either to string values of SQL types, like varchar, or to the corresponding integer values specified in the class java.sql.Types (e.g. -4). The default value of this property is empty.
The SQL repository will not attempt to determine the JDBC types for properties in a database table if every property either has its sql-type attribute set explicitly or is of a type included in the safeSQLTypes property for that repository.
Cache the Schema Information
You can configure the SQL repository to cache information about your repository database schemas. Do this by setting the updateSchemaInfoCache property in the GSARepository component to true. The default value for this property is false. If you set this property to true, once you re-assemble, redeploy, and restart your application, the server creates the directory {atg.dynamo.home}/data/schemaInfoCache/. This directory contains a series of files with names like repositoryName.properties, one file for each repository in your application. Each file in the /data/schemaInfoCache directory specifies the SQL type of each column in that repository’s schema, in the following format:
tablename.colname=SQLtype
The SQL type in this case is the integer type code specified in the class java.sql.Types. For example, setting the updateSchemaInfoCache to true in the ProfileAdapterRepository component might generate a file that begins like this:
## SchemaInfoCache - auto generated #Tue May 13 17:32:25 EDT 2003 dps_user_scenario.scenarioInstances=12 paf_usr_pgfld.creationDate=93 dps_email_address.emailAddresses=12
These files can be generated against one database and copied to another as long as the schemas are compatible. If you change the schema and this property is enabled, you need to remove the /data/schemaInfoCache directory so it is regenerated. The /data/schemaInfoCache directory does get regenerated automatically if you add a property that refers to a new column or a new table. If those files exist, they are used even if the updateSchemaInfoCache property is set to false. This is so that on a live server, you can use the schema info cache generated by a different staging server. This will, for example, help you in the event that one server in your cluster has some problem that prevents it from generating the schema information.
