custom SQL insert and get_new_id()

Scheduler Javascript
#1

Hi there,

    I'm using the render_sql function to apply a filter to the displayed events and, as such, I have to redefine Insert, Update and Delete logic. I was able to make Update and Delete work correctly, but Insert is another business altogether. The problem is that the ID of the database is never reassigned to the $action object since I have no idea how to do this. Here's what I have so far:

function myInsert($action)
	{
		global $scheduler;
		$requete = "INSERT INTO tblpmtodo (NoClient, NoDivision, Titre, Etiquette, Description, StartDateTime, " . 
					"EndDateTime, Priorite, ANoPersonne, StatutId, PrcComplete, CalendrierStrategique) VALUES (" . 
					$action->get_value('NoClient') . ", " . $action->get_value('NoDivision') . ", \"" . 
					mysql_real_escape_string($action->get_value('Titre')) . "\", \"" . 
					mysql_real_escape_string($action->get_value('Etiquette')) . "\", \"" . 
					mysql_real_escape_string($action->get_value('Description')) . "\", \"" . 
					$action->get_value('StartDateTime') . "\", \"" . 
					$action->get_value('EndDateTime') . "\", " . 
					$action->get_value('Priorite') . ", " . 
					$action->get_value('ANoPersonne') . ", " . 
					$action->get_value('StatutId') . ", " . 
					$action->get_value('PrcComplete') . ", " . 
					$action->get_value('CalendrierStrategique') . ")";
		$scheduler->sql->attach("Insert",$requete);
	}
$scheduler->event->attach("beforeInsert", "myInsert");

The query I’ve built actually works and the line is indeed added to the tblpmtodo table. The problem comes with events that happen later and that use the get_new_id() function. I got this in the log:

change value of: Etiquette as: TODO - tfilteau - 1291926837887

Which is how I know the id is not the right one. So, how exactly do I reassign the database ID to the $action object so that the get_new_id() function will work as expected?

Thanks in advance.

Osu

#2

You can add somethign like

function my_code($action){
global $sched;
$action->success($sched->sql->get_new_id());
}
$sched->event->attach(“afterInsert”, “my_code”);

#3

Well, I finally managed to get back to my scheduler project and try out the solution you sent me… and sadly, it doesn’t work. I tried and tried again, but as soon as I use:

$sched->sql->get_new_id()

I get an error. I also checked on the log and I noticed this:

Edit operation finished
0 => action:inserted; sid:1292445894190; tid:1292445894190;

Which leads me to believe that, since I’m using custom SQL to insert data, the scheduler doesn’t know what to reassign to the tid (which I’m guessing means Table ID).

Anyway, I was still able to get it to work by doing:

function myInsert($action)
	{
		global $scheduler;
		$requete = "INSERT INTO tblpmtodo (NoClient, NoDivision, Titre, Etiquette, Description, StartDateTime, " . 
					"EndDateTime, Priorite, ANoPersonne, StatutId, PrcComplete, CalendrierStrategique) VALUES (" . 
					$action->get_value('NoClient') . ", " . $action->get_value('NoDivision') . ", \"" . 
					mysql_real_escape_string($action->get_value('Titre')) . "\", \"" . 
					mysql_real_escape_string($action->get_value('Etiquette')) . "\", \"" . 
					mysql_real_escape_string($action->get_value('Description')) . "\", \"" . 
					$action->get_value('StartDateTime') . "\", \"" . 
					$action->get_value('EndDateTime') . "\", " . 
					$action->get_value('Priorite') . ", " . 
					$action->get_value('ANoPersonne') . ", " . 
					$action->get_value('StatutId') . ", " . 
					$action->get_value('PrcComplete') . ", " . 
					$action->get_value('CalendrierStrategique') . ")";
		
		//$scheduler->sql->query($requete);
		mysql_query($requete);
		$action->success(mysql_insert_id());
		
		//$scheduler->sql->attach("Insert",$requete);
		//$action->success($scheduler->sql->get_new_id());
		//$action->success();
	}
$scheduler->event->attach("beforeInsert", "myInsert");

This successfully returns the Database ID to the $action object, but if I could, I’d much prefer to use the scheduler’s built-in mechanism. Anyway, if you could tell me why the $scheduler->sql->get_new_id() doesn’t work, I’d be really grateful.

Osu