[gdal-dev] MSSQL2008 Driver and OGR/Python

[geographika]

Thanks Tamas for the quick reply.
I've been trying a few more combinations, and I now have it working.

In the geometry_columns table the first record works - when I duplicate 
the schema name in the the f_table_schema and f_table_name fields. The 
second does not.
In fact I can put any entry in the f_table_schema field - it appears to 
be ignored.


[f_table_catalog], [f_table_schema] ,[f_table_name], 
[f_geometry_column],[coord_dimension],[srid],[geometry_type]
DbName 	any value can be put here
	schemaname.mytable 	GEOMFIELD 	2 	32768 	MULTIPOLYGON
DbName 	schemaname 	mytable 	GEOMFIELD 	2 	32768 	MULTIPOLYGON


The following now works (with the first record - not the second):

conn_string = "MSSQL:server=W08-SQL08;database=DbName;Integrated 
Security=true;"
ds = ogr.Open(conn_string)
lyr = ds.GetLayerByName('schemaname.mytable') #schema name always has to 
be passed here

If this is not the intended behaviour I can create a ticket, otherwise 
thanks for your time (and drivers!).

Regards,

Seth

--
web:http://geographika.co.uk
twitter: @geographika



On 07/02/2011 13:26, Tamas Szekeres wrote:
>
>
> 2011/2/7 geographika <geographika at gmail.com 
> <mailto:geographika at gmail.com>>
>
>     Hi,
>
>     When working with the SQL Server 2008 OGR driver I presume it is
>     necessary to create the following metadata tables?
>
>     geometry_columns
>     spatial_ref_sys
>
>     There appears to be no way to do this automatically in Python, but
>     if I import a single dataset into the database it is created
>     automatically. I can then use:
>
>     conn_string = "MSSQL:server=W08-SQL08;database=dbname;Integrated
>     Security=true;"
>     ds = ogr.Open(conn_string)
>     lyr = ds.GetLayerByName('testdata')
>
>
> Hi Seth,
>
> Those metadata tables are required since MSSQL2008 doesn't have a 
> builtin way to store all of these information. When importing the 
> first table it should indeed be created from scratch. With regards to 
> spatial_ref_sys it could also be populated by using the corresponding 
> postgis script.
>
>
>     It would be nice to be able to connect to a layer without having
>     to register it with the geometry_columns table using a connection
>     string such as:
>
>     "MSSQL:server=W08-SQL08;database=dbname;Integrated
>     Security=true;tables=myschema.testdata(GEOMFIELD)"
>
>     As this is not currently possible I manually added a record to
>     geometry_columns for an existing spatial table in my database.
>     This is in a separate schema so I used the following SQL:
>
>     INSERT INTO [geometry_columns] ([f_table_catalog],
>     [f_table_schema] ,[f_table_name],
>     [f_geometry_column],[coord_dimension],[srid],[geometry_type])
>     VALUES ('DbName', '*myschema*', 'testdata', 'GEOMFIELD', 2, 32768,
>     'MULTIPOLYGON')
>
>     However using SQL Profiler when trying to connect to the layer it
>     always tries to find this layer in *dbo*.
>
>     exec DbName..sp_columns N'testdata',N'*dbo*',N'DbName',NULL
>
>     As it does not exist in dbo the connection never succeeds, and I
>     cannot connect to any of the layers in the database.
>     I can add this to trac if it is an issue - I just want to first
>     make sure I've not made any obvious errors.
>
>
> Specifying the schema in geometry_columns should be working and 
> myschema.mytable(GEOMFIELD) should also be a working option. So please 
> file a ticket with this issue if you encounter problems here.
>
>
> Best regards,
>
> Tamas
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20110207/bdb4c1ec/attachment.html