Setting an SQL identity column in the Studio
DF-19.1, Postgres, Mertech driver. I've just created a new Postgres SQL table in my application. I had to do it manually, creating the table in the back end first, then the .int file, etc but that's another story. I set the ID column to be an identity column but when I open that table in the Studio there's no setting in the ID column properties for an identity column. According to the help and postings in the forum there's supposed to be, and I've used it before with another application. What would cause that to be missing?
When working with the table in the back end the ID column is populated correctly but trying to add records with dbExplor throws the following error.
Any idea what's wrong? No idea what "OVERRIDING SYSTEM VALUE" means, the help doesn't seem to say or I can't find it.
c:\program files (x86)\dataflex 19.1\bin\dbexplor.exe
No error text: Can't open dferr006.dat ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.
Error: 25000
MSG_ERROR_REPORT (433) - oErrorDialog (90) - at address 67177
MSG_REQUEST_SAVE (375) - operformance_DD (2192) - at address 17137
GET_SAVESELECTEDROW (7882) - oObject (2232) - at address 63713
GET_SAVESELECTEDROW (7882) - oDatatable (2204) - at address 61817
GET_SAVESELECTEDROW (7882) - oDatatable (2204) - at address 65498
MSG_REQUEST_SAVE (375) - oDatatable (2204) - at address 64900
MSG_REQUEST_SAVE (375) - oso_numGridColumn (2320) - in native code
MSG_REQUEST_SAVE (375) - oObject (2379) - in native code
MSG_ONEXECUTE (9293) - oSaveCurrentRecordButton (342) - at address 72770
MSG_EXECUTE (9292) - oSaveCurrentRecordButton (342) - at address 56972
MSG_ONCOMEXECUTE (8415) - oRebar (199) - at address 55962
[start] - at address 103722
Re: Setting an SQL identity column in the Studio
Open Table Explorer, highlight the field, properties pane, df_field_auto_increment.
Odd - now that I've posted that, I see some that are not set in properties, but is in the DB. Maybe it just sets it in the INT? BTW - what I was looking at was Mertech with MySQL.
1 Attachment(s)
Re: Setting an SQL identity column in the Studio
Isn't that the same as opening the table, clicking on the ID column and then opening the properties? For me what's shown is the same in both cases and neither shows df_field_autoincrement. This is what I see:
[ATTACH=CONFIG]14580[/ATTACH]
1 Attachment(s)
Re: Setting an SQL identity column in the Studio
I'm in 19.0 - but I see this:
[ATTACH=CONFIG]14581[/ATTACH]
Re: Setting an SQL identity column in the Studio
Yup, I've used that before. So how do I make this one look like that? :confused: Is this a Studio issue or a driver issue?
Re: Setting an SQL identity column in the Studio
Bob,
The Studio uses a file called mdspgsql_DriverDef.xml that is in your DataFlex Bin folder.
By default the DAW installer puts a very basic one in there that supports the very basic things (about 5kB in size, version is probably 8.0).
When you install the driver from Mertech for your DataFlex version, it updates the DriverDef files for the Studio and the drivers.
You can then do things like change native data types for the DataFlex data type.
This (and more) is handled via that DriverDef file.
I haven't looked at it in a long time, but last one I worked on was version 12.2 from the looks of it.
There's DriverDef files for each of Mertech's drivers and you want these in place for improved support from the Studio.
--
Wil
Re: Setting an SQL identity column in the Studio
[QUOTE]c:\program files (x86)\dataflex 19.1\bin\dbexplor.exe
No error text: Can't open dferr006.dat ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.[/QUOTE]
The error means that df ( or mertech driver) is trying to provide a value to the column.
But as its defined as generated always column, only the sql engine can provide it’s value ( normally from an internal sustem incremental sequence).
So, df and mertech are not talking properly in this sense, and df is not aware the column is really an identity column.
The hint “OVERRIDING SYSTEM VALUE” is just postgree trying to help you. It means you can specify that clause in your Identify column definition, in order to allow application to provide a value to the column, and the engine will allow the app to override the sustem provided value.
But I believe this is not what you want, and it won’t help you to solve the real problem.
Re: Setting an SQL identity column in the Studio
[QUOTE][COLOR=#333333]When you install the driver from Mertech for your DataFlex version, it updates the DriverDef files for the Studio and the drivers.[/COLOR][/QUOTE]
That might be the problem Wil, I didn't "install" the driver on this new PC I'm using, I just copied over the DLLs & config files from the old box. Though the .xml file should also have been copied. At least this points me in a direction to try, thanks. I see that Mertech just released their newest 32-bit driver & Flex2SQL, I should probably give that a go. Thanks!
Re: Setting an SQL identity column in the Studio
The answer proved to be to simply add
[CODE]FIELD_AUTOINCREMENT_TYPE 1[/CODE]
to the .int file