Insert Update, Null values - DateTime

KW · Post by KW » Tue 27 May 2008 20:32

I'm having a a rough time getting Updates and Deletes working using the generated Insert and Delete queries. I think it has something to do with null values - actually I'm quite positive. I am really hoping to get this ironed out.

Mysql version: 5.0.45 Debian_lubuntu3.3-log Debian etch distro

Here is an insert query that works fine:

Code: Select all

TRACEPOINT:BeforeEvent
MysqlType: VarChar
Source Column: BookedID
Param Value: f89a1d59-70b5-4f76-928e-c95adb510185
Name: BookedID

MysqlType: VarChar
Source Column: ReservationID
Param Value: A9CC118B-46FB-4E76-B7F4-13884FEF47EB
Name: ReservationID

MysqlType: VarChar
Source Column: RoomID
Param Value: 
Name: RoomID

MysqlType: DateTime
Source Column: Arrival Date
Param Value: 
Name: Arrival_Date

MysqlType: DateTime
Source Column: Departure Date
Param Value: 
Name: Departure_Date

MysqlType: DateTime
Source Column: Arrival Time
Param Value: 
Name: Arrival_Time

MysqlType: DateTime
Source Column: Departure Time
Param Value: 
Name: Departure_Time

MysqlType: VarChar
Source Column: Notes
Param Value: 
Name: Notes

MysqlType: TinyInt
Source Column: Confirmed
Param Value: 0
Name: Confirmed

MysqlType: DateTime
Source Column: Confirmed Date
Param Value: 
Name: Confirmed_Date

MysqlType: TinyInt
Source Column: Cancelled
Param Value: 0
Name: Cancelled

MysqlType: DateTime
Source Column: Cancelled Date
Param Value: 
Name: Cancelled_Date

MysqlType: VarChar
Source Column: Requests
Param Value: 
Name: Requests

MysqlType: VarChar
Source Column: Guarantees
Param Value: 
Name: Guarantees

MysqlType: VarChar
Source Column: CondoType
Param Value: 
Name: CondoType

MysqlType: VarChar
Source Column: BedType
Param Value: 
Name: BedType

MysqlType: VarChar
Source Column: FilterRoomIDByResort
Param Value: 
Name: FilterRoomIDByResort

MysqlType: TinyInt
Source Column: Mattress
Param Value: 0
Name: Mattress

 INSERT INTO `Meridian_dbo`.`Bookings` (`BookedID`, `ReservationID`, `RoomID`, `Arrival Date`, `Departure Date`, `Arrival Time`, `Departure Time`, `Notes`, `Confirmed`, `Confirmed Date`, `Cancelled`, `Cancelled Date`, `Requests`, `Guarantees`, `CondoType`, `BedType`, `FilterRoomIDByResort`, `Mattress`) VALUES (:BookedID, :ReservationID, :RoomID, :Arrival_Date, :Departure_Date, :Arrival_Time, :Departure_Time, :Notes, :Confirmed, :Confirmed_Date, :Cancelled, :Cancelled_Date, :Requests, :Guarantees, :CondoType, :BedType, :FilterRoomIDByResort, :Mattress)

Insert statements works.

Code: Select all

MysqlType: VarChar
Source Column: BookedID
Param Value: f89a1d59-70b5-4f76-928e-c95adb510185
Name: Original_BookedID

MysqlType: Int
Source Column: ReservationID
Param Value: 0
Name: IsNull_ReservationID

MysqlType: VarChar
Source Column: ReservationID
Param Value: A9CC118B-46FB-4E76-B7F4-13884FEF47EB
Name: Original_ReservationID

MysqlType: Int
Source Column: RoomID
Param Value: 1
Name: IsNull_RoomID

MysqlType: VarChar
Source Column: RoomID
Param Value: 
Name: Original_RoomID

MysqlType: Int
Source Column: Arrival Date
Param Value: 1
Name: IsNull_Arrival_Date

MysqlType: DateTime
Source Column: Arrival Date
Param Value: 
Name: Original_Arrival_Date

MysqlType: Int
Source Column: Departure Date
Param Value: 1
Name: IsNull_Departure_Date

MysqlType: DateTime
Source Column: Departure Date
Param Value: 
Name: Original_Departure_Date

MysqlType: Int
Source Column: Arrival Time
Param Value: 1
Name: IsNull_Arrival_Time

MysqlType: DateTime
Source Column: Arrival Time
Param Value: 
Name: Original_Arrival_Time

MysqlType: Int
Source Column: Departure Time
Param Value: 1
Name: IsNull_Departure_Time

MysqlType: DateTime
Source Column: Departure Time
Param Value: 
Name: Original_Departure_Time

MysqlType: Int
Source Column: Notes
Param Value: 0
Name: IsNull_Notes

MysqlType: VarChar
Source Column: Notes
Param Value: 
Name: Original_Notes

MysqlType: Int
Source Column: Confirmed
Param Value: 0
Name: IsNull_Confirmed

MysqlType: TinyInt
Source Column: Confirmed
Param Value: 0
Name: Original_Confirmed

MysqlType: Int
Source Column: Confirmed Date
Param Value: 1
Name: IsNull_Confirmed_Date

MysqlType: DateTime
Source Column: Confirmed Date
Param Value: 
Name: Original_Confirmed_Date

MysqlType: Int
Source Column: Cancelled
Param Value: 0
Name: IsNull_Cancelled

MysqlType: TinyInt
Source Column: Cancelled
Param Value: 0
Name: Original_Cancelled

MysqlType: Int
Source Column: Cancelled Date
Param Value: 1
Name: IsNull_Cancelled_Date

MysqlType: DateTime
Source Column: Cancelled Date
Param Value: 
Name: Original_Cancelled_Date

MysqlType: Int
Source Column: Requests
Param Value: 0
Name: IsNull_Requests

MysqlType: VarChar
Source Column: Requests
Param Value: 
Name: Original_Requests

MysqlType: Int
Source Column: Guarantees
Param Value: 0
Name: IsNull_Guarantees

MysqlType: VarChar
Source Column: Guarantees
Param Value: 
Name: Original_Guarantees

MysqlType: Int
Source Column: CondoType
Param Value: 0
Name: IsNull_CondoType

MysqlType: VarChar
Source Column: CondoType
Param Value: 0
Name: Original_CondoType

MysqlType: Int
Source Column: BedType
Param Value: 0
Name: IsNull_BedType

MysqlType: VarChar
Source Column: BedType
Param Value: 
Name: Original_BedType

MysqlType: Int
Source Column: FilterRoomIDByResort
Param Value: 1
Name: IsNull_FilterRoomIDByResort

MysqlType: VarChar
Source Column: FilterRoomIDByResort
Param Value: 
Name: Original_FilterRoomIDByResort

MysqlType: Int
Source Column: Mattress
Param Value: 0
Name: IsNull_Mattress

MysqlType: TinyInt
Source Column: Mattress
Param Value: 0
Name: Original_Mattress

 DELETE FROM `Meridian_dbo`.`Bookings` WHERE ((`BookedID` = :Original_BookedID) AND ((:IsNull_ReservationID = 1 AND `ReservationID` IS NULL) OR (`ReservationID` = :Original_ReservationID)) AND ((:IsNull_RoomID = 1 AND `RoomID` IS NULL) OR (`RoomID` = :Original_RoomID)) AND ((:IsNull_Arrival_Date = 1 AND `Arrival Date` IS NULL) OR (`Arrival Date` = :Original_Arrival_Date)) AND ((:IsNull_Departure_Date = 1 AND `Departure Date` IS NULL) OR (`Departure Date` = :Original_Departure_Date)) AND ((:IsNull_Arrival_Time = 1 AND `Arrival Time` IS NULL) OR (`Arrival Time` = :Original_Arrival_Time)) AND ((:IsNull_Departure_Time = 1 AND `Departure Time` IS NULL) OR (`Departure Time` = :Original_Departure_Time)) AND ((:IsNull_Notes = 1 AND `Notes` IS NULL) OR (`Notes` = :Original_Notes)) AND ((:IsNull_Confirmed = 1 AND `Confirmed` IS NULL) OR (`Confirmed` = :Original_Confirmed)) AND ((:IsNull_Confirmed_Date = 1 AND `Confirmed Date` IS NULL) OR (`Confirmed Date` = :Original_Confirmed_Date)) AND ((:IsNull_Cancelled = 1 AND `Cancelled` IS NULL) OR (`Cancelled` = :Original_Cancelled)) AND ((:IsNull_Cancelled_Date = 1 AND `Cancelled Date` IS NULL) OR (`Cancelled Date` = :Original_Cancelled_Date)) AND ((:IsNull_Requests = 1 AND `Requests` IS NULL) OR (`Requests` = :Original_Requests)) AND ((:IsNull_Guarantees = 1 AND `Guarantees` IS NULL) OR (`Guarantees` = :Original_Guarantees)) AND ((:IsNull_CondoType = 1 AND `CondoType` IS NULL) OR (`CondoType` = :Original_CondoType)) AND ((:IsNull_BedType = 1 AND `BedType` IS NULL) OR (`BedType` = :Original_BedType)) AND ((:IsNull_FilterRoomIDByResort = 1 AND `FilterRoomIDByResort` IS NULL) OR (`FilterRoomIDByResort` = :Original_FilterRoomIDByResort)) AND ((:IsNull_Mattress = 1 AND `Mattress` IS NULL) OR (`Mattress` = :Original_Mattress)))

Delete Statements doesn't work.

Code: Select all

CREATE TABLE  `Meridian_dbo`.`Bookings` (
  `BookedID` varchar(64) collate utf8_bin NOT NULL,
  `ReservationID` varchar(64) collate utf8_bin default NULL,
  `RoomID` varchar(64) collate utf8_bin default NULL,
  `Arrival Date` datetime default NULL,
  `Departure Date` datetime default NULL,
  `Arrival Time` datetime default NULL,
  `Departure Time` datetime default NULL,
  `Notes` varchar(256) collate utf8_bin default NULL,
  `Confirmed` tinyint(1) default NULL,
  `Confirmed Date` datetime default NULL,
  `Cancelled` tinyint(1) default NULL,
  `Cancelled Date` datetime default NULL,
  `Requests` varchar(500) collate utf8_bin default NULL,
  `Guarantees` varchar(500) collate utf8_bin default NULL,
  `CondoType` varchar(50) collate utf8_bin default NULL,
  `BedType` varchar(50) collate utf8_bin default NULL,
  `FilterRoomIDByResort` varchar(64) collate utf8_bin default NULL,
  `Mattress` tinyint(1) default NULL,
  PRIMARY KEY  (`BookedID`)

I'd like the auto generated Insert, Update and delete statements to just work.

It *seems to be null values. But I'm not sure. Do you have any advice on making the genearted update and delete statements work correctly?

Also, I'm having trouble with some tables creating Delete and Update statements for the adapters, for instance this table did not create a delete or update statement:

Code: Select all


CREATE TABLE  `Meridian_dbo`.`Customer_Book` (
  `BookedID` varchar(64) collate utf8_bin NOT NULL,
  `CustomerID` varchar(64) collate utf8_bin NOT NULL,
  `GuaranteeFee` decimal(10,0) NOT NULL,
  `PetFee` decimal(10,0) NOT NULL,
  `PetDeposit` decimal(10,0) NOT NULL,
  `SecurityDeposit` decimal(10,0) NOT NULL)
   ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

It may have something to do with the fact that is has two primary keys and additional columns.

Most of my kinks are worked out - so bare with me.

Alexey.mdr · Post by **Alexey.mdr** » Thu 29 May 2008 13:57

Command builder for the table Meridian_dbo.Bookings with PRIMARY KEY (`BookedID`)
by default should generate a delete command, like the following:

Code: Select all

DELETE FROM `Meridian_dbo`.`Bookings` WHERE (`BookedID` = :Original_BookedID)

Please provide me a sample code or steps to reproduce to get such command.
I managed to get this command by explicitly setting all columns as keys in Command builder,
which means I want to pass all parameters' values to the command.

As to the Meridian_dbo.Customer_Book table, we could not reproduce the problem, please send a block of code or a test project to reproduce the issue.