Hi Justin,
> Sorry for the late reply. The error message is a bit confusing. Any chance
> you can share the code being used to make the connection to H2?
sure, I can try to extract all the needed parts. The following example
will be for a point geomatry table.
1) I create the database table the manual way, since with different
schema than public I had problems (for now this workaround is ok, it
is not the most important). Browsing the geotools code I came to the
following code:
First I create the aliases:
SQLQuery sqlQuery = session
.createSQLQuery("CREATE ALIAS GeoToolsVersion for
\"org.geotools.data.h2.JTS.GeoToolsVersion\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS AsWKT for
\"org.geotools.data.h2.JTS.AsWKT\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS AsText for
\"org.geotools.data.h2.JTS.AsWKT\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS EnvelopeAsText for
\"org.geotools.data.h2.JTS.EnvelopeAsText\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS GeomFromText for
\"org.geotools.data.h2.JTS.GeomFromText\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS GeomFromWKB for
\"org.geotools.data.h2.JTS.GeomFromWKB\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS Envelope for
\"org.geotools.data.h2.JTS.Envelope\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS GetSRID FOR
\"org.geotools.data.h2.JTS.GetSRID\"");
sqlQuery.executeUpdate();
sqlQuery = session
.createSQLQuery("CREATE ALIAS GeometryType for
\"org.geotools.data.h2.JTS.GeometryType\"");
sqlQuery.executeUpdate();
were session is a hibernate session.
Then I create the tables:
Transaction transaction = session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery("drop table " +
schemaName + "." + tableName
+ " if exists");
sqlQuery.executeUpdate();
sqlQuery = session.createSQLQuery("CREATE TABLE " + schemaName
+ "." + tableName + " ( "
+ THE_GEOM + " BLOB COMMENT 'POINT', " + ID + " BIGINT
NOT NULL)");
sqlQuery.executeUpdate();
String addPkQuery = "ALTER TABLE " + schemaName + "." +
tableName + " ADD PRIMARY KEY ("
+ ID + ")";
sqlQuery = session.createSQLQuery(addPkQuery);
sqlQuery.executeUpdate();
String addFkQuery = "ALTER TABLE " + schemaName + "." +
tableName + " ADD CONSTRAINT "
+ tableName + "_" + fkTable + "_id FOREIGN KEY (" + ID
+ ") REFERENCES "
+ fkSchemaName + "." + fkTable + " (" + ID + ")";
sqlQuery = session.createSQLQuery(addFkQuery);
sqlQuery.executeUpdate();
transaction.commit();
2) Then I create a datastore:
Map params = new HashMap();
params.put(JDBCDataStoreFactory.DBTYPE.key, "h2");
params.put(JDBCDataStoreFactory.SCHEMA.key, schemaName);
params.put(JDBCDataStoreFactory.DATABASE.key, database);
params.put(JDBCDataStoreFactory.PORT.key, port);
params.put(JDBCDataStoreFactory.HOST.key, host);
params.put(JDBCDataStoreFactory.USER.key, user);
params.put(JDBCDataStoreFactory.PASSWD.key, passwd);
spatialDataStore = DataStoreFinder.getDataStore(params);
3) Then I create a point and try to commit it to H2:
SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder();
b.setName(tableName);
b.setCRS(wgs84CRS);
b.add(THE_GEOM, Point.class);
b.add(ID, Long.class);
SimpleFeatureType type = b.buildFeatureType();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(type);
Object[] values = new Object[]{reporjectedPoint, id};
builder.addAll(values);
SimpleFeature feature = builder.buildFeature(tableName + "." + id);
FeatureCollection<SimpleFeatureType, SimpleFeature>
newCollection = FeatureCollections
.newCollection();
newCollection.add(feature);
transaction = new DefaultTransaction();
DataStore spatialDataStore = h2SessionFactory.getSpatialDataStore();
FeatureStore<SimpleFeatureType, SimpleFeature>
featureStore = (FeatureStore<SimpleFeatureType, SimpleFeature>)
spatialDataStore
.getFeatureSource(tableName); // ******HERE I GET
THE EXCEPTION ******
featureStore.setTransaction(transaction);
featureStore.addFeatures(newCollection);
transaction.commit();
I get an exception at the getFeatureSource line.
I am developing exactly the same to work also with postgres and the
above works properly. So I am not sure if this is a problem I
introduce by creating the tables manually.
Do you see something strange?
Thanks,
Andrea
>
> -Justin
>
> andrea antonello wrote:
>>
>> A small update,
>> when using all lowercase schemas and tables and no quotes in queries,
>> the connection seems to occurr.
>> The problem I get now, is that the password is wrong:
>> java.lang.RuntimeException: Unable to obtain connection
>>
>> thrown because of:
>>
>> Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create
>> PoolableConnectionFactory (Wrong user name or password [8004-118])
>> at
>> org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
>> at
>> org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
>> at
>> org.geotools.jdbc.JDBCDataStore.createConnection(JDBCDataStore.java:1373)
>> ...
>>
>> I am quite sure the user and passwd are right, since I am using the
>> default of H2, so the parameters get sa and an empty string for the
>> passwd.
>>
>> My guess is that something else is going on, but I can't figure out
>> what. Any idea?
>>
>> Ciao
>> Andrea
>>
>>
>>
>> On Wed, Sep 30, 2009 at 9:13 AM, andrea antonello
>> <[email protected]> wrote:
>>>
>>> Hi Justin,
>>> thanks for the reply.
>>>
>>>> One thing that strikes me as off is:
>>>>
>>>>> params.put(JDBCDataStoreFactory.NAMESPACE.key, "/home/moovida/TMP/");
>>>>
>>>> If you are trying to specify the location of the file just pout in the
>>>> DATABASE key. So:
>>>>
>>>> params.put(JDBCDataStoreFactory.DATABASE.key,
>>>> "/home/moovida/TMP/database");
>>>>
>>>> So what it is doing is creating the db in the current working directory,
>>>> and
>>>> it could be that that db does indeed include the table. I would try
>>>> cleaning
>>>> the .db files and trying again.
>>>
>>> Yes, at some point I noticed that there was a new database in my
>>> workspace :)
>>> Anyways, I then made it like you suggest, but I had huge problems with
>>> case sensitivity of the schema in which the table was created.
>>> I am trying out this thing on turn on H2 and Postgres/GIS, and it
>>> doesn't work creating the schema uppercase and then using the same for
>>> the table reference, i.e. I need to use the schema name lowercase.
>>> Probably this is a known thing, and I just never fell into it because
>>> using the public schema?
>>>
>>> Thanks,
>>> Andrea
>>>
>>>
>>>> andrea antonello wrote:
>>>>>
>>>>> Hi, I am having troubles creating a h2 connection.
>>>>> Can anyone please confirm me that the following is the proper way to
>>>>> go:
>>>>>
>>>>> Map params = new HashMap();
>>>>> params.put(JDBCDataStoreFactory.DBTYPE.key, "h2");
>>>>> params.put(JDBCDataStoreFactory.DATABASE.key, "database");
>>>>> params.put(JDBCDataStoreFactory.NAMESPACE.key,
>>>>> "/home/moovida/TMP/");
>>>>> params.put(JDBCDataStoreFactory.PORT.key, port);
>>>>> params.put(JDBCDataStoreFactory.HOST.key, host);
>>>>> params.put(JDBCDataStoreFactory.USER.key, user);
>>>>> params.put(JDBCDataStoreFactory.PASSWD.key, passwd);
>>>>>
>>>>> spatialDataStore = DataStoreFinder.getDataStore(params);
>>>>>
>>>>> The problem is that is when I try to create a table with:
>>>>>
>>>>> SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder();
>>>>> b.setName("testgeom");
>>>>> b.setCRS(DefaultGeographicCRS.WGS84);
>>>>> b.add("the_geom", Point.class);
>>>>> b.add("id", Long.class);
>>>>> SimpleFeatureType featureType = b.buildFeatureType();
>>>>> spatialDataStore.createSchema(featureType);
>>>>>
>>>>> it complains that:
>>>>>
>>>>> Caused by: org.h2.jdbc.JdbcSQLException: Table testgeom already
>>>>> exists; SQL statement:
>>>>> CREATE TABLE "testgeom" ( "fid" int AUTO_INCREMENT(1) PRIMARY KEY,
>>>>> "the_geom" BLOB COMMENT 'POINT', "id" BIGINT ) [42101-118]
>>>>>
>>>>> The table doesn't exist, since the command should create it.
>>>>> Any idea about what I am doing wrong?
>>>>>
>>>>> Thanks,
>>>>> Andrea
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Come build with us! The BlackBerry® Developer Conference in SF, CA
>>>>> is the only developer event you need to attend this year. Jumpstart
>>>>> your
>>>>> developing skills, take BlackBerry mobile applications to market and
>>>>> stay
>>>>> ahead of the curve. Join us from November 9-12, 2009. Register
>>>>> now!
>>>>> http://p.sf.net/sfu/devconf
>>>>> _______________________________________________
>>>>> Geotools-gt2-users mailing list
>>>>> [email protected]
>>>>> https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
>>>>
>>>> --
>>>> Justin Deoliveira
>>>> OpenGeo - http://opengeo.org
>>>> Enterprise support for open source geospatial.
>>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Come build with us! The BlackBerry® Developer Conference in SF, CA
>> is the only developer event you need to attend this year. Jumpstart your
>> developing skills, take BlackBerry mobile applications to market and stay
>> ahead of the curve. Join us from November 9-12, 2009. Register now!
>> http://p.sf.net/sfu/devconf
>> _______________________________________________
>> Geotools-gt2-users mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
>
> --
> Justin Deoliveira
> OpenGeo - http://opengeo.org
> Enterprise support for open source geospatial.
>
------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Geotools-gt2-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
