[gdal-dev] MSSQL2008 Driver and OGR/Python

[Tamas Szekeres]

I think the second one should also work, so this problem may however be
fixed.

Best regards,

Tamas


2011/2/7 geographika <geographika at gmail.com>

>  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>
>
>>  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/6bfcd27f/attachment.html