07-06-2016 02:55 PM
I haven't done MySQL before. I'm trying and hit a roadblock pretty early.
I'd like to insert new records and have the primary key autogenerated/autoincrementing.
So, I've attached a few screenshots. The first is a screenshot of the MySQL settings for the table I am trying to write. The second is the Labview code I threw together that I'm trying to run.
The full error I receive is:
>Database Connection.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.7.11-log]Field 'techniciansID' doesn't have a default value in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Database Connection.vi
I know this error is stating that the techniciansID does not have a default value and it doesn't want to write because of this...but since it is an auto-incrementing field, it seems like it should work.
Any help would be appreciated. Thanks,
Thad
Solved! Go to Solution.
07-06-2016 03:48 PM
What's your table engine type? I think this may only be supported in MyISAM without some additional configurations. This link may help if you are using InnoDB.
http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
07-06-2016 08:42 PM
07-07-2016 08:24 AM
I've attached a portion of the SQL code that is generated for this table's definition. I removed the definitions for other tables.
---------------------------------------------------------------------------------------
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema spinnerDB
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema spinnerDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `spinnerDB` DEFAULT CHARACTER SET utf8 ;
USE `spinnerDB` ;
-- -----------------------------------------------------
-- Table `spinnerDB`.`technicians`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `spinnerDB`.`technicians` (
`techniciansID` INT NOT NULL AUTO_INCREMENT,
`technicianName` VARCHAR(45) NOT NULL,
PRIMARY KEY (`techniciansID`),
UNIQUE INDEX `techniciansID_UNIQUE` (`techniciansID` ASC))
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
------------------------------------------------------------------------------------------------------------------------------------
I also tried running the SQL using the 'Execute Query' VI. Same result. Here is the full error:
NI_Database_API.lvlib:Conn Execute.vi->Database Connection.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.7.11-log]Field 'techniciansID' doesn't have a default value in NI_Database_API.lvlib:Conn Execute.vi->Database Connection.vi
07-07-2016 08:41 AM
07-07-2016 08:42 AM
I'm new to MySQL, that's why 🙂 I tried it both ways and it doesn't work whether defined as unique or not.
07-07-2016 08:51 AM
I actually can't get this to work in MySQL workbench, which I hadn't tried.
07-07-2016 08:55 AM
Okay. So if you go into MySQL where you define the table, and give the technician's ID a default value, does anything start working? Give it a default value of 0 and see what happens.
07-07-2016 09:25 AM
Setting to 0 didn't work, but I did figure this out. For some reason, when I was regenerating my tables in MySQL server and using the "Forward Engineer" feature, the changes weren't propagating. I had to drop the schema from the database (attached image) and then do a "Forward Engineer". I was making changes, but the tables and columns still had the original attributes...guess they can't be updated by the script when running.
After doing this and the original settings I had (auto-incrementing primary key), the insert works in MySQL and in Labview.
Thanks for all the responses. They got me thinking and checking some things I hadn't before.
- Thad
07-07-2016 12:41 PM