Cdbcommand Failed To Execute The Sql Statement

Yii 1.1 General Discussion
1

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:

[indent]541 throw new CDbException(Yii::t(‘yii’,‘CDbCommand failed to execute the SQL statement: {error}’,

542 array(’{error}’=>$message)),(int)$e->getCode(),$errorInfo);[/indent]

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.

2

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

3

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.

4

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

5

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.

6

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."

[color="#222222"][font="Arial, sans-serif"][size="4"]So you should use: [/size][/font][/color]

[color="#222222"][font="Arial, sans-serif"][size="4"]




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

$command->execute();

[/size][/font][/color]

[color="#222222"][font="Arial, sans-serif"] [/font][/color]

[color="#222222"][font="Arial, sans-serif"] [/font][/color]

[color="#222222"][font="Arial, sans-serif"] [/font][/color]

7

@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!