.Net Editor using non-dbo schema

.Net Editor using non-dbo schema

nightmare6mnightmare6m Posts: 1Questions: 1Answers: 0

Hi there, I think this question has been asked, but so far it seems like the answer has been to use the dbo schema. In my case, that is not possible so I am hoping there is an answer.

I have a controller that looks like this:

var response = new Editor(db, "schemaName.tableName", "schemaName.TableName.Field_A_Name")
.Field(new Field("schemaName.TableName.Field_A_Name"))
.Field(new Field("schemaName.TableName.Field_B_Name"))
.Field(new Field("schemaName.TableName.Field_C_Name"))
.Field(new Field("schemaName.TableName.Field_D_Name"))
.Process(request)
.Data();

return Json(response);

This loads the data in correctly. However, When I try to insert a row, I get this response:
Cannot retrieve inserted id - no primary key was found.

Field_A_Name is the primary key of the table and is a varchar data type.

Do you have any suggestions on what I can do to resolve this?

Thank you

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Try dropping schemaName from everywhere but the second parameter for the new Editor() constructor - i.e.:

    var response = new Editor(db, "schemaName.tableName", "TableName.Field_A_Name")
    .Field(new Field("TableName.Field_A_Name"))
    .Field(new Field("TableName.Field_B_Name"))
    .Field(new Field("TableName.Field_C_Name"))
    .Field(new Field("TableName.Field_D_Name"))
    .Process(request)
    .Data();
    
    return Json(response);
    

    There is another option. Create a new database user which has schemaName defined as the default schema rather than dbo. That will let it work as expected.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    I followed your suggestion, it is still not working. Any ideas allan? Thanks!

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi @clrncrao ,

    Did you try both the suggestions - the change to the syntax and the dropping of the table? The second should work, so I'm surprised it's not. Are you seeing errors?

    Cheers,

    Colin

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 2019

    Hi @colin,

    I've tried the "Try dropping schemaName from everywhere but the second parameter for the new Editor() constructor - i.e.:" suggestion.
    The create new database user is not an option for my current project.

    Thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you chance the default schema for the existing user? Unfortunately per schema support is not something the Editor .NET libraries have ready yet.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    Hi @allen,
    I've tried set the user's default the schema to the one that my table belongs to and it still doesn't work unfortunately. Everything works when the table is in the dbo schema but not in another schema.

    Thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    That's really odd - have you dropped the schema name from the list of fields when its setup like that and just used the column names (or table + columns if you are doing joins)?

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 2019

    @allan I have it set up like this

    var response = new Editor(db, "schemaName.tableName", "TableName.Field_A_Name")
    .Field(new Field("TableName.Field_A_Name"))
    .Field(new Field("TableName.Field_B_Name"))
    .Field(new Field("TableName.Field_C_Name"))
    .Field(new Field("TableName.Field_D_Name"))
    .Process(request)
    .Data();

    return Json(response);

    I can update existing rows fine. I just can't add new rows.

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi @clrncrao ,

    When you say you can't add new rows, are you seeing browser console or server-side errors? Are you able to link to your page?

    Cheers,

    Colin

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 2019

    So going back to @allan earlier statement. Are you saying that this Data Tables .NET Library doesn't support any other schema beside dbo?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    That is currently correct, yes. I suspect the problem is here in the primary key detection.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    @allan

    Thanks allan. I'll take a look at the source code to see if I can do some modification to fit my solution.

  • ITAppData@HomeStreet.comITAppData@HomeStreet.com Posts: 54Questions: 13Answers: 2

    Hey, just began running into this issue myself and wanted to see if there were any updates on this?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    A workaround would be to have a database user who's default search path is set to the schema you want to use. Unfortunately we've not yet implemented a .schema() method for the libraries.

    Allan

  • ITAppData@HomeStreet.comITAppData@HomeStreet.com Posts: 54Questions: 13Answers: 2

    Thanks Allan.

    I set the default schema for the user that is used to connect to the SQL database to the appropriate schema but it still wasn't functioning. As stated, everything else works, just not creating a record.

    I'm currently using the schema name in both the table and field parts. Not sure if that should change. Thanks :smile:

                        var editor = new Editor(_db, "gl.VALID_TRANSACTIONTYPE", "gl.VALID_TRANSACTIONTYPE.LOSTransactionTypeCode").Model<VALID_TRANSACTIONTYPE>();
                        editor.Debug(true);
                        editor.Field(new Field("gl.VALID_TRANSACTIONTYPE.LOSTransactionTypeCode").Validator(Validation.Unique(new ValidationOpts { Message = "LOS Transaction Type Code Must Be Unique" })).Validator(Validation.Required(new ValidationOpts { Message = "LOS Transaction Type Code is Required" })));
                        editor.Field(new Field("gl.VALID_TRANSACTIONTYPE.TxnTypeDescription"));
                        editor.Field(new Field("gl.VALID_TRANSACTIONTYPE.TxnDefaultBalanceAffect").Validator(Validation.Numeric()));
                        return Json(editor.Process(formData).Data(), JsonRequestBehavior.AllowGet);
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    interesting - I'm not sure why that wouldn't work I'm afraid! Does the gl. schema prefix address the issue for you at the moment (at the expense of having gl. all over the place!)?

    Allan

  • ITAppData@HomeStreet.comITAppData@HomeStreet.com Posts: 54Questions: 13Answers: 2

    Unfortunately not. It won't allow for insertions unfortunately. We also tried to do a Synonym on the SQL side (using the synonym to connect to the dbo schema) and connect Editor to that, but some issue occurred.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    When you do an insert, what is the return from the server? Since you have the Debug(true) option, the JSON should include the SQL that editor tried to build and execute (and hopefully also an error message saying what is wrong with it!).

    Thanks,
    Allan

  • ITAppData@HomeStreet.comITAppData@HomeStreet.com Posts: 54Questions: 13Answers: 2

    As another follow up, also attempted to utilize a SQL view in the dbo schema and ReadTable functionality, but still received the same error on insertion.

    I'm not currently sure there is a way to workaround this, but it does seem to be related to the portion you linked to in the source code earlier (here).

    My guess is the table parameter being sent via the _table[0] must include the schema name, which is causing it not to have a match, since the query returns the table_name field without a schema. I would think if you could add the ability to pass a schema name and it was used to remove the schema from the table name/or used to expand matching on table AND schema from the query that would probably fix it:

                    // We need to find out what the primary key column name and type is
                    pkeyCmd.CommandText = @"
                        SELECT
                            KCU.table_name as table_name,
                            KCU.column_name as column_name,
                            C.DATA_TYPE as data_type,
                            C.CHARACTER_MAXIMUM_LENGTH as data_length
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON
                            TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                            TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND
                            KCU.TABLE_NAME = @table
                        JOIN
                            INFORMATION_SCHEMA.COLUMNS as C ON
                                C.table_name = KCU.table_name AND
                                C.column_name = KCU.column_name
                        ORDER BY KCU.TABLE_NAME, KCU.ORDINAL_POSITION
                    ";
                    pkeyCmd.Connection = _db.Conn();
                    pkeyCmd.Transaction = _db.DbTransaction;
    
                    param = pkeyCmd.CreateParameter();
                    param.ParameterName = "@table";
                    param.Value = _table[0];
                    pkeyCmd.Parameters.Add(param);
    

    Something along these lines

                    // We need to find out what the primary key column name and type is
                    pkeyCmd.CommandText = @"
                        SELECT
                            KCU.table_name as table_name,
                            KCU.column_name as column_name,
                            C.DATA_TYPE as data_type,
                            C.CHARACTER_MAXIMUM_LENGTH as data_length,
                            TC.TABLE_SCHEMA AS [schema_name]
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON
                            TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                            TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND
                            KCU.TABLE_NAME = @table AND TC.TABLE_SCHEMA = @schema
                        JOIN
                            INFORMATION_SCHEMA.COLUMNS as C ON
                                C.table_name = KCU.table_name AND
                                C.column_name = KCU.column_name
                        ORDER BY KCU.TABLE_NAME, KCU.ORDINAL_POSITION
                    ";
                    pkeyCmd.Connection = _db.Conn();
                    pkeyCmd.Transaction = _db.DbTransaction;
    
                    param = pkeyCmd.CreateParameter();
                    //STRIP PASSED schema name here
                    param.ParameterName = "@table";
                    param.Value = _table[0];
                    pkeyCmd.Parameters.Add(param);
                    ///Add new parameter for schema
                   param2 = pkeyCmd.CreateParameter();
                   param2.ParameterName = "@schema";
                   param2.Value = _schema[0] //or something
                   pkeyCmd.Parameters.Add(param2);
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Just to say that this has finally been committed in. It will be in Editor 1.9.5 :).

    Allan

This discussion has been closed.