Yii Framework Forum: Cdbcommand Failed To Execute The Sql Statement - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cdbcommand Failed To Execute The Sql Statement Rate Topic: -----

#1 User is offline   okiz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 26-February 13

Posted 26 February 2013 - 12:00 PM

Hi to all!
It's first post for me,
sorry if something is wrong.

Background:
i'm trying to create a ticketing system,
the idea is to create a table per ticket( if this idea is feel free to suggest a different solution).
I have a "ticket main table" that holds the creation time and the id of the user who created the ticket.
The "ticket main table" is managed with a model "Ticket" and basic crud actions work fine.

Problem:
the idea was to create a table ticket_id.$model->id when actionCreate() inside the controller(TicketController) is executed.
$command = Yii::app()->db->createCommand("CREATE TABLE ticket_id_".$model->id." (id INT  AUTO_INCREMENT PRIMARY KEY)");
$command->queryAll()


CDbException:
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error. The SQL statement executed was: CREATE TABLE ticket_id_37 (id INT AUTO_INCREMENT PRIMARY KEY)


Even if it says that the command has failed, the table is created.

I've noticed that commenting out the lines:

541 throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
542 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);


in framework/db/CDbCommand.php
"solves" the issue...

I'm using a MySQL database
		'db'=>array(
			'connectionString' => 'mysql:host=localhost;dbname=ticketing',
			'emulatePrepare' => true,
			'username' => 'user',
			'password' => 'mypassword',
			'charset' => 'utf8',
			'tablePrefix'=>'tbl_ticketing_',
		),


Server:
MySQL Server version: 5.1.67-0ubuntu0.11.10.1 (Ubuntu)
PHP version: 5.3.6-13 ubuntu 3.9
Yii: yii-1.1.13

Thank you all for any suggestions/help.
0

#2 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,670
  • Joined: 04-March 10
  • Location:UK

Posted 26 February 2013 - 12:35 PM

Why are you creating a separate table for each ticket? Is there a reason for not just storing it in the "ticket main table"?
0

#3 User is offline   okiz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 26-February 13

Posted 26 February 2013 - 01:05 PM

View PostKeith, on 26 February 2013 - 12:35 PM, said:

Why are you creating a separate table for each ticket? Is there a reason for not just storing it in the "ticket main table"?


I thought it was the best solution.
When the number of users & tickets grows won't it be hard to manage.

Each user can see only it's tickets, won't it be "resource consuming" to query a huge table to retrive records?

That's why i was also asking for any suggestions about this solution.
0

#4 User is offline   Girish Awate 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 23-January 13
  • Location:Mumbai

Posted 26 February 2013 - 02:00 PM

View Postokiz, on 26 February 2013 - 01:05 PM, said:

I thought it was the best solution.
When the number of users & tickets grows won't it be hard to manage.

Each user can see only it's tickets, won't it be "resource consuming" to query a huge table to retrive records?

That's why i was also asking for any suggestions about this solution.


Hi
There are number of ways to do same things but since you are using a framework there are standard ways of doing things to get you results with less code and time, as the framework takes care about rest of the issues like performance etc
you just need to use the framework rightly for the same ..
regards wish you luck
0

#5 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,670
  • Joined: 04-March 10
  • Location:UK

Posted 26 February 2013 - 02:07 PM

If the table is properly indexed, lookup performance will be fine until the table gets enormous (millions of records). Bear in mind that databases are designed for this use case; naive guesses at how to improve performance are likely to prove misguided. Read into database normalisation and indexing to get a better idea of how to structure your data.
0

#6 User is offline   Juan Carrera 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 05-October 12
  • Location:Zaragoza (Spain)

Posted 26 February 2013 - 03:39 PM

In http://www.yiiframew...-sql-statements you can read:


" execute(): performs a non-query SQL statement, such as INSERT, UPDATE and DELETE. If successful, it returns the number of rows that are affected by the execution."



So you should use:

$command = Yii::app()->db->createCommand("CREATE TABLE ticket_id_".$model->id." (id INT  AUTO_INCREMENT PRIMARY KEY)"); 
$command->execute();








0

#7 User is offline   okiz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 26-February 13

Posted 26 February 2013 - 04:01 PM

@Girish Awate

I agree. It's my first experience with a framework(and till now it's great!), but since i was used to "hard code" what i wanted to obtain, i'm a bit lost...

@Keith

That was my first thought, but wasn't sure.

@Juan Carrera

Yes, i forgot about it, was kinda desperate...



I'll stick with one table solution.

Thank you all!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users