mySQL 5.5 Does Not Support SHORT

[jesawyers]

Hello All,

Been getting more and more proficient with DF and have a question about setting up a alarm database using mySQL 5.5.

I've got the ALARMS setup to log to a database and have the ODBC connection setup and working fine. Problem is when DF tries to create the table for ALARMS, it appears that DF is wanting to create a column with a data type of SHORT which mySQL5.5 does not support that I can see. I manually created a table in mySQL and the SHORT data type is not listed as an option.

Here is the actual error reported by DF...

C1021 Could not create table for alarm logging. Msg: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHORT, Status TEXT(1))' at line 1

Can this be fixed?

John

[AzeoTech]

Sure, here's the exact SQL statement DF uses:

CREATE TABLE ALARMLOG (TheTime DATE, Name TEXT(100), Description TEXT(255), Priority SHORT, Status TEXT(1));

Do the same thing in mySQL, adjusting the values appropriately. You can probably replace SHORT with INT and TEXT with VARCHAR.

[jesawyers]

Got the alarm table created using the following SQL command in mySQL Workbench...

CREATE TABLE ALARMLOG (TheTime DATE, Name VARCHAR(100), Description VARCHAR(255), Priority TinyINT, Status VARCHAR(1))

However when an alarm fires I still get the error that was previously reported.

Any thoughts?

John

[jesawyers]

Have not heard anything about my reply. Again I got the alarm table created, but DF is still reporting the error I orginally reported in my first post.

Thanks...john

[AzeoTech]

I can't say. It'll give you that error if the insert fails. The insert line looks like:

INSERT INTO ALARMLOG (TheTime, Name, Description, Priority, Status) VALUES (%f,'%s','%s',%d,'%s');

Where %f, %s and %d are the various values (from a printf())

[jesawyers]

Thanks for the INSERT command format. I'll play with it to see what I can figure out.

John

[jesawyers]

Ok..found the problem. When creating the alarmlog table I tried using TinyInt for the priority. Set the priority column in the alarmlog table to INT and it's working.

[AzeoTech]

Yeah, that'd probably do it. Just as an aside, unless you are doing gigantic databases (billions of records), the days of having to worry about conserving integer sizes is kind of long gone. Hell, even if you have a billion records, the difference in space between a TINYINT (which is 1 byte) and an INT (which is 4 bytes) is only 3 gig of hard drive space. Considering 1 TB (1000 gig) drives are about $100, its really not worth the trouble.

[jesawyers]

Thanks for the info. I'm from the days of main frame computers and conserving memory is just something I have never gotten over in all of these years of programming.

Thanks again....John

[AzeoTech]

Yeah, I know. I'm the same way and often have to remind myself.