MariaDB - Odd Issue

1

I have a really odd issue with one table for a project I am working on. It is a MariaDB database.

The table in Clarion:

YEAR_OPTIONS FILE,DRIVER(‘ODBC’,OWNER(gConnectionString),NAME(‘wp_year_options’),PRE(YOP),BINDABLE,THREAD
ID_KEY KEY(YOP:ID),NOCASE,PRIMARY
YEAR_OPTION_CODE_KEY KEY(YOP:YEAR_ACTIVE,YOP:OPTION_CODE),NOCASE
Record RECORD,PRE()
ID LONG
YEAR_ACTIVE SHORT
OPTION_CODE CSTRING(4)
DESCRIPTION CSTRING(51)
REFERENCE BYTE
REFERENCE_LOCATION CSTRING(11)
REFERENCE_VALUE CSTRING(4)
END
END

and the table in MariaDB

CREATE TABLE IF NOT EXISTS wp_year_options (
id INT(14) AUTO_INCREMENT,
year_active SMALLINT NOT NULL,
option_code VARCHAR(3) NOT NULL,
description VARCHAR(50) NOT NULL,
reference TINYINT UNSIGNED NOT NULL DEFAULT 0,
reference_location VARCHAR(10),
reference_value VARCHAR(3),
PRIMARY KEY (ID),
UNIQUE year_option_code_key (YEAR_ACTIVE, OPTION_CODE)
);

Now for the weird part, for this table when I insert a new record, the year_active is a primed value and the option_code and description are entered. When I do that, the year_active is put into the database, but the option_code and description are blank. Then I go back into the record and change the values, then they are saved. I put on logging and no errors are reported and when I look at the values for those two columns with internal logging on insert, they are set.

UPDATE: I recreated the table and started off fresh and the same app worked. Everything is identical in the tables. I ran CHECK TABLE on the table that is giving me fits and it did not find any errors.

Any ideas for me to check?

2

Another Update: Now the new file is having the same problem. Started off adding the records fine, then it just stopped adding the entered fields but continues to use the primed values.

3

You don’t say what version of Clarion, or what template set , or how you have told the dct to get the value of the autoinc key?

I suspect you are getting records inserted but you haven’t told Clarion how to find the newly inserted record so it can’t apply your changes when it saves?? Have you looed at the raw data in the database to see what records are being created?

If you import the table into your dct, to a new table name, it might set the autoinc properties such that it is able to find the newly inserted record. You can set this property in your dct yourself, but I can’t remember what it is called and I can’t find it in the help??

EDIT: The property I was looking for is AUTOINCUSESSCOPEIDENTITY in the help, but it appears to be only for MSSQL? I thought there were similar properties for other drivers?

Maybe this is relevant to your problem…

4

Clarion version 11 build 13244. The template set is my own and has been working forever with MSSQL perfectly. I am using the settings in the dictionary.

image
image1388×608 28.2 KB

When I insert a record. It creates the data as expected.

image
image1808×314 16.8 KB

5

Hi Ron,

It appears you have Autoinc enabled in both Clarion and your MySQL database, you only need it in one of them?

I found the dct setting in the help for MySQL…

Server Side Auto incrementing

!MySQL Example
Pet FILE,DRIVER('ODBC','/AUTOINC=SELECT LAST_INSERT_ID()'),|
OWNER('menagerie,root'),NAME('pet'),PRE(pet),BINDABLE,THREAD
1 Like
6

That was it, it was causing errors. I changed some settings and it worked but I didn’t need the code to do the autoincrement.

/AUTOINC=SELECT LAST_INSERT_ID() is only needed if I want to retrieve the ID from the insert.

Thanks for keeping me honest.

7

I think I’m surprised it worked without throwing errors back at you. The Clarion autoinc gets the last ID adds one and adds a stub record with just that number and whatever else you have put in PrimeFields for the FileManager. I would have thought that without priming all those four not null fields, that autoinc would fail, and probably report an error (cannot enter NULL in …). That requirement to put junk values, even temporarily, into columns that you really want to have good values, is, IMO, a reason for giving up on Clarion autoinc.