Issues with using start transaction

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
michaelh613
Forum Commoner
Posts: 38
Joined: Sun Mar 16, 2008 1:35 pm

Issues with using start transaction

Post by michaelh613 »

MySQL server version 5.0.51a
raw MySQL statement in question

Code: Select all

 
INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')
 
errors that MySQL returns to the client
Error number is 0
Error description is

the table structure & column indexes of the relevant tables
'profiles', 'CREATE TABLE `profiles` (
`id` int(11) NOT NULL auto_increment,
`card_id` int(11) NOT NULL,
`profileID` varchar(12) collate latin1_general_ci NOT NULL,
`productCode` varchar(5) collate latin1_general_ci NOT NULL,
`createDate` date NOT NULL,
`startDate` date NOT NULL,
`nextPaymentDate` date NOT NULL,
`frequency` enum('WEEK','MONT','YEAR') collate latin1_general_ci NOT NULL,
`amount` decimal(6,2) NOT NULL,
`status` enum('ACTIVE','DEACTIVE') collate latin1_general_ci NOT NULL default 'ACTIVE',
`customerName` varchar(150) collate latin1_general_ci NOT NULL,
`email` varchar(150) collate latin1_general_ci NOT NULL,
`externalID` int(11) NOT NULL,
`accountNo` varchar(40) collate latin1_general_ci NOT NULL,
`schedule` time NOT NULL,
`temp` tinyint(2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `prof_id` (`profileID`),
KEY `card_id` (`card_id`),
KEY `FK_profiles` (`productCode`),
KEY `nextPaymentDate` (`nextPaymentDate`),
CONSTRAINT `profiles_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=39988 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci'

Now when I run the query directly in the PHP MyAdmin I get this error

#1364 - Field 'profileID' doesn't have a default value

Seems simple as profileID has a not null value. But it should get created later in my code

Code: Select all

 
function addProfile($card,$productCode,$customerName,$email,$externalID=0,$accountNo=0) {
        
        $number = $card['number']; 
        $cvv = $card['cvv']; 
        $name_on_card = $card['name_on_card']; 
        $exp_date=$card['exp_date']; 
        $type = $card['type'];
 
        echo "number is $number<br>";
        echo "cvv is $cvv<br>";
        echo "name on care is $name_on_card<br>";
        echo "exp date is $exp_date<br>";
        echo "type is $type<br>";           
 
    
        global $db;
        $db->query("START TRANSACTION");
        $sql1 = "INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
        VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')";
        echo "sql query is  $sql1 <br>";
        
        $r1 = $db->query($sql1);             
        $prof_id = $db->last_id;
        
        
        
        $sql2 = "INSERT INTO ccards(profile_id,card_type,card_number,card_cvv,name_on_card,exp_date)
        VALUES ($prof_id','$type','$number','$cvv','$name_on_card','$exp_date')";
        echo "2nd query is $sql2 <br>";
        $r2 = $db->query($sql2);
        $card_id = $db->last_id;
        $sql3 = "UPDATE profiles SET card_id='$card_id' WHERE id='$prof_id'";
        echo "third query is $sql3";
        $r3 = $db->query($sql3);
                
        $ret = new Message();
        //die("R1: $r1 $sql1 R2: $r2 $sql2 R3: $r3");
        if ($r1 && $r2 && $r3 && $db->affected) {
            $db->query("COMMIT");
            $sql = "SELECT profileID FROM profiles WHERE id=$prof_id";
            $r = $db->results($sql);
            $profileID = $r[0]['profileID'];
            $ret->result = 0;
            $ret->text = "Success.";
            $ret->profileID = $profileID;
        } else {
            $db->query("ROLLBACK");
            $ret->result = 10;
            $ret->text = "Error. Couldn't create the profile";
            $ret->profileID = 0;
        }
        return $ret;
    }   
 
and the code for function query is

Code: Select all

 
    function query($sql) {
        $start = $this->getmicrotime();
        $q = @mysql_query($sql,$this->mysql_link) or die("Error number is ".mysql_errno()."<br> Error description is ".mysql_error()."<br>Query is  " .$sql."<br>"); ;
        $end = $this->getmicrotime();
        $this->qlist[] = $sql;
        if ($q) {
            $this->nrquery += 1;
            $this->last_query_time = $end - $start;
            $this->total_time += $this->last_query_time;
            $this->affected = mysql_affected_rows($this->mysql_link);
            $this->last_id = mysql_insert_id($this->mysql_link);
            return true;
        }
        else return  false;
    }
 

This trigger exists in the system

Code: Select all

 
DROP TRIGGER IF EXISTS `recurring`.`ins_profile`//
CREATE TRIGGER `recurring`.`ins_profile` BEFORE INSERT ON `recurring`.`profiles`
 FOR EACH ROW BEGIN
    DECLARE am DECIMAL(6,2);
    DECLARE fr CHAR(4);
    DECLARE sa INT;
    SELECT price, frequency, startAfter INTO am,fr,sa FROM products WHERE code=NEW.productCode;
    IF NEW.createDate='0000-00-00' THEN
        SET NEW.createDate = CURDATE();
    END IF;
    SET NEW.amount=am;
    SET NEW.frequency=fr;
    SET NEW.startDate=ADDDATE(NEW.createDate,sa);
    SET NEW.nextPaymentDate=ADDDATE(NEW.createDate,sa);
    SET NEW.profileID = nextProfileID();
END
//
 
 
 
I'm used to using straight mySQL and PHP. I'm using inherited code for this project and in another area this code has worked with the start transaction committ and rollback and trigger. So I'm thinking I must be making a silly error breaking it. I find it very strange it breaks with an error of 0 when I echo it out.

Thanks for any help.
Post Reply