Subscribe to this thread
Home - General / All posts - 9.0.168.6 - SQL Server
rhowitt52 post(s)
#19-Dec-18 20:34

Have been working with the latest version of M9 and connecting to a SQL Server.

As per Adamw's suggestion, have created a function in the database:

CREATE function [dbo].[filter_func]( @client nvarchar(50))

returns table as

return (Select * from Geom_of_Fields

where Company_ID = @client );

and created a view using the function:

CREATE VIEW filter_rickh_1 as Select * from filter_func('011900');

With a datasource connected to the SQL Server database I have tried the following :

- created a drawing from the view, however the drawing is not editable.

- created a --native Query statement on the view, the data in the grid is not editable and there is no schema available, so a drawing can not be created.

- created a $manifold$ Query on the view, this returned a table which has a schema available, but the table is not editable. A drawing could be created from this data and it is not editable.

Is editing of native data from a SQL Server not available yet?

Also the SQL Server database is on a remote server, every now and then the system connection to the remote server times out. When this happens , Manifold 9 will not restore a connection to the database , without first closing the project and then re-opening the project. I have tried "Refresh" on the datasource.

adamw


8,401 post(s)
#20-Dec-18 08:22

The culprit is that the we cannot detect that the result table of the view has a unique field (no BTREExxx index).

There are two ways to go:

1. (Quick and dirty) Open the view (filter_rickh_1) and run it (View - Run). In the result table, right-click on the unique field and select Use as Identity. This will make the result table of the view editable, and this will carry to all components based on that view.

2. (Without views) With the SQL Server database linked as a data source, in the MAP file = on the same level as the data source, create a new query and set its text to:

--SQL9

SELECT * FROM [sqlserver]::[dbo.geom_of_fields]

 WHERE [company_id] = '011900';

...with 'sqlserver' being the name of the data source. Run the query. The result table should be editable and should have a spatial index as long as 'geom_of_fields' has it.

Modify the query to cache data for performance (can also create a second query to call the first one):

--SQL9

TABLE CALL TableCache(

 (SELECT * FROM [sqlserver]::[dbo.geom_of_fields]

  WHERE [company_id] = '011900'),

  TRUE -- this allows writing through the cache into the database

);

...then run the query to make sure it works. Right-click the query in the Project pane, select Create - New Drawing, select the geometry field, specify coordinate system, click OK.

You can now work with the drawing. It will only have objects for the company_id you specified, with the filtering for company_id being done on the database, accelerated by any indexes it might have. Opening the drawing will fetch all data for the specified company_id into the cache, with the spatial index built on just the fetched data, to make renders / pans / zooms and other operations in the map window fast. The drawing is editable, all changes go through the cache into the database. And there are no views.

Hope this helps.

adamw


8,401 post(s)
#20-Dec-18 08:27

Regarding the connection timing out (the last paragraph), do you connect to the database from other products and does it time out there as well? If it does not time out there, can it be that you are specifying a custom value for the timeout? You can do the same in 9, by editing the connection string when you create the data source (can also edit the connection string for an existing data source, it's in its Properties). The setting is '...;Connection Timeout=90' (using the value appropriate for your connection).

Dimitri


5,291 post(s)
#20-Dec-18 08:41

Is editing of native data from a SQL Server not available yet?

To answer the general question, and not just in views: sure, it's available. One of the most popular uses of 9 is editing native data within databases, like SQL Server, Oracle, PostgreSQL, etc. See Art Lembo's videos, for example.

In addition to the more sophisticated reply adamw has made... never forget simple requirements in the middle of more complex doings.

If you don't have an index on a table, you can't edit it. As the Editing Tables topic mentions,

Index Required

To be editable tables must have an index and they cannot be read-only.

One of the very nice things about 9 is that tables are tables as much as possible, with everything working as much as possible the same regardless of the data source. If you want to edit a table that's local within your project you need an index in there. If you want to edit a table that's stored in SQL Server, it's the same requirement: need an index.

Adamw has discussed the more sophisticated aspects of that with views, but given the question seems more fundamental, it's important not to lose sight of the basic requirements for editing any table: it must have an index and it cannot be read-only.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.