Insert Return Value "DataType" only allowed int Data Type

Ali Adnan

Dear Allan,
I am using DataTables Editor .Net version and now I come to error where my Table Primary Key is not String istead of number.

After investigation I come to know the return value DataType is allowed only INT32 data type, please see the code below taken from

I:\Development\Resources\DataTableEditor_Purchaed\Editor-NET-1.6.1\Editor-NET-1.6.1\DataTables\DataBaseUtil\Oracle\Query.cs

protected override void _Prepare(string sql)
        {
            var provider = DbProviderFactories.GetFactory(_db.Adapter());
            var cmd = provider.CreateCommand();
            
            // Oracle.DataAccess.Client (and managed) bind by position by default(!)
            // So we need to force it to bind by name if used
            var bindByName = cmd.GetType().GetProperty("BindByName");
            bindByName?.SetValue(cmd, true, null);
            
            // Need to reliably get the primary key value
            if (_type == "insert" && _pkey != null)
            {
                // Add a returning parameter statement into an output parameter
                sql += " RETURNING " + _pkey[0] + " INTO :dtvalue";
            }

            cmd.CommandText = sql;
            cmd.Connection = _db.Conn();
            cmd.Transaction = _db.DbTransaction;

            // Need to reliably get the primary key value
            if (_type == "insert" && _pkey != null)
            {
                var outParam = cmd.CreateParameter();
                outParam.ParameterName = ":dtvalue";
                outParam.Direction = ParameterDirection.Output;
                outParam.DbType = DbType.Int32;
                cmd.Parameters.Add(outParam);
                cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
            }

See

outParam.DbType = DbType.Int32;

how can I dynamically provide my primary key DataType and what will happen in case of composit primary keys ?

Best Regards

allan

Hi,

I'm afraid this looks like a limitation in the Editor .NET Oracle implementation at the moment.

The SQL Server implementation gets the type of the data from the database directly. I will attempt to see if I can do that with Oracle as well. I'm afraid this isn't something I'm going to be able to resolve overnight, but I'll replace back here when fixed. It shouldn't be too long!

Thanks,
Allan

Ali Adnan

ok I will wait

Ali Adnan

Dear Allan,

As you comment It shouldn't be too long!, can you update me is it on the way ?

as a side note you can use below Oracle query to get this.

SELECT ac.constraint_name, acc.column_name, acc.position
     , atc.data_type, atc.data_length
  FROM all_constraints ac JOIN all_cons_columns acc 
                               ON (ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME)
                          JOIN all_tab_cols atc ON (ac.owner = atc.owner AND 
                               ac.table_name = atc.TABLE_NAME AND 
                               acc.COLUMN_NAME = atc.COLUMN_NAME)
 WHERE ac.table_name = 'table_name'
   AND ac.constraint_type = 'P'
 ORDER BY acc.position;

replace table name with the actual

Best regards

allan

I'll update this thread when its done. I can't say for certain when it will be done I'm afraid as I'm working on a number of other aspects at the moment.

That query will give the primary key? That's super useful, - thanks!

Allan

Ali Adnan

That query will give the primary key?
YES with Data Type (varchar2,number,date etc)

Ali Adnan

Dear Allan,
Any chance to get updated one?

tangerine

I'll update this thread when its done.

allan

The resolution for this has now been committed and will be in 1.6.4 which will drop soon.

Allan