Support for non-dbo schemas

gbenett

I'm running into some difficulty using Editor 1.5.6 and WebAPI with SQL Server tables in a schema ('proto') other than the default ('dbo). For one thing, it's not possible to define a model with classes named "proto.myTable." Instead I am specifying everything with Fields, which seems to work, although the Options method is not reliably populating a select box in the UI. In an earlier answer (https://datatables.net//forums/discussion/comment/83867/#Comment_83867) it was suggested that an Editor.Schema() method might eventually be added to support non-dbo schemas. Any update on this, or workaround in the interim?

allan

Hi,

I'm currently working on Editor 1.6, in fact in a related area for this today, so I'll take a look at exactly what is involved and let you know about the progress.

Regards,
Allan

Mundaring

Hi gbenett,

I am having same issue that you. I would like to know how you make it works when you said "you are specifying with fields".

Thanks
Wilson

Mundaring

Hi Allan,

When do you reckon version 1.6 will be release or at least this issue will be solved.

Thanks,
Wilson

allan

I would like to know how you make it works when you said "you are specifying with fields".

Use new Field( "mySchema.myField" )

When do you reckon version 1.6 will be release

Within the next couple of months. I've not got a firm release date yet. This issue won't be resolved until that release.

Allan

gbenett

Hi Mundaring, I wasn't checking posts here so missed your question. As Allan says, using the fully qualified field names works; however, because Editor's Model function doesn't allow classes named <schema>.<table>, we currently can't leverage that nice functionality and need to explicitly call out every field we need. It gets ugly fast, especially with joins.

Frankly it got to be bad enough that I moved my tables back into the default dbo schema. Not every developer will have that option, and not every DBA will allow it, so the schema flexibility Allan promises down the road will be a real boon. Looking forward to it.

pangelino

Hi Allan, were you able to add support for specifying the database schema in version 1.6? I see you're on version 1.6.2 so I'd really like to leverage it if it's available. I'm not seeing anything relevant in the documentation but maybe I'm missing it. Thanks in advance.

allan

I'm afraid it didn't make it into 1.6. The aim is for it to be in 1.7, but as before I don't have a release date for that yet as I'm working on other aspects of DataTables at the moment.

In the mean time, try appending the schema name before the table name in your constructor - e.g. schemaName.tableName.

Allan

pangelino

Allan, that does work for the main table being edited, but doesn't support the nested models that I need for foreign key relationships. I get errors saying 'The multi-part identifier <table>.<column> could not be bound.' Would love to see that schema support in 1.7!

jcromero82@hotmail.com

Hi friends!!!, i solved this problem using a View in sql server, probably it works on other databases.

pangelino

Hello again @allan. I'm about to start a new project and am considering using Editor again. I see you're on version 1.7.2. Were you able to add support yet to specify the database schema?

allan

There isn't a specific method for it yet. You would still need to use schemaName.tableName like you might in SQL if the search path wasn't set to the schema you want.

Allan

svenos

Hi all, Hi @allan ,

I'm using Datatables with a non standard schema and it works almost perfect with the proposed solution:
schemaName.tableName.fieldName

It's ok like this and - in my opinion - there is no urgent need to improve that.

However....
it does not work when I want to update two tables at the same time. Let's say a "User" table and a "UserSetting" table.

Before using schema's it worked very well. As soon as putted those prefixes "schemaName" in front of the table names, it just stopped working without any visible errors.
When I look at the SQL server log, it shows the update just for the first table without any left joined tables.

I'm using PHP, MS SQL or MySQL (depending on the customer) with Datatables 1.10.16.

Is there a workaround for that?

Thank you in advance!

svenos

I think I've found a workaround. It might be not so elegant... but it works.

I'm using pre/post/writeCreate, pre/post/writeEdit and preRemove events to edit the second table with a self written function.

svenos

@Alan
Sorry to write again. Editing my orginal post is not possible anymore.

I thought I've solved everything. But it looks like I even can't delete rows anymore when using a schema prefix.

I have to write custom functions to delete my data. It's possible since we're talking about a small amount of data and just a few tables, but somehow it feels wrong. Is there a smarter way?

allan

Not yet I'm afraid. It would be worth adding ->debug(true) to the chain to see what the SQL that the Editor PHP class is creating, to see where its going wrong.

The Editor NodeJS libraries has support for schemas now, but not yet the PHP / .NET libs.

Allan

Blommer

Do we support linking other databases ? Example: [Database].[schema].[table].[Field] in this format.
I need to link tables across databases and be able to edit only base table but show necessary fields from other table in different database. Help is highly appreciated.

tangerine

There are various posts in here about using two databases. For example:
https://datatables.net/forums/discussion/35947

Blommer

Do we support linking other databases ? Example: [Database].[schema].[table].[Field] in this format.
I need to link tables across databases and be able to edit only base table but show necessary fields from other table in different database. Help is highly appreciated.

allan

Currently no - I'm sorry to say not in the Editor server-side libraries. It works to some extent in the PHP ones, but I've not tried it for the .NET and NodeJS libs yet. What I would suggest instead is that you create a VIEW for the more complex queries that you need such as this. Editor can read from the VIEW just like from any other table. It can also be told to read from a VIEW (->readTable()) but write to some other table (although this precludes the ability to write across databases).

Allan

miguelhughes

on the sql server end, can't we just use

select @@IDENTITY

after the query?
doc: https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver15

What's the need for creating a temp table and inserting the id into it?
I'd be more than willing issue a pull request.

(sorry if this comes on more than once, but i couldn't get my comment to display properly)

allan

Its because an identity isn't always used as the primary key. I did actually use that originally, but found I had to update it to the current method to handle the schema that some developers are using with Editor.

Allan

Matt Jano

Hi @allen: I still cannot add a new record to a table in a schema other than dbo. I get the "Cannot retrieve inserted id - no primary key was found" error.

Is there any progress on this?

allan

Could you confirm what version of the DataTables.dll you are using please and also show me how you are initialising and using the Editor class instance?

Thanks,
Allan

colin

@Matt Jano Is this with Editor? Our accounts aren't showing that you have a license. Is the license registered to another email address? Please can let us know so we can update our records and provide support.

Thanks,

Colin