Re: [jplatform] Support IGNORE clause for INSERT

[Michael Babker]

INSERT IGNORE INTO is MySQL specific syntax.  I know it isn't supported by PostgreSQL, not sure about others.  Could it be added, sure, but it would probably need to be in JDatabaseQueryMysqli if it isn't supported in other drivers.

-Michael

Please pardon any errors, this message was sent from my iPhone.

On Jul 21, 2012, at 1:37 PM, Aerendir <adamo....@gmail.com> wrote:

Hi at all,

this is my first post on this group, so excuse me for any error of any kind :)

I'm developing an app using Joomla! Platform 12.1 and i need to use IGNORE clause for a set of multiple insert.

Actually, the framework doesn't support the use of this clause, but, reading the code, it seems quite simple to add it.

If i'm not wrong it could be sufficient to edit the method insert of the JDatabaseQuery class (joomla/database/query.php).

Now the function is this:

public function insert($table, $incrementField=false)
{
$this->type = 'insert';
$this->insert = new JDatabaseQueryElement('INSERT INTO', $table);
$this->autoIncrementField = $incrementField;

return $this;
}

I think (but i could be wrong! I don't yet fully know the logic of the entire database package) that the function could be rewrote in this way:

public function insert($table, $incrementField=false, $ignore=false)
{
$this->type = 'insert';

if($ignore)
{
$this->insert = new JDatabaseQueryElement('INSERT IGNORE INTO', $table);
}
else
{
$this->insert = new JDatabaseQueryElement('INSERT INTO', $table);
}

$this->autoIncrementField = $incrementField;

return $this;
}

What do you think about?

[Sam Moffatt]

Yes, INSERT IGNORE is a MySQL specific extension and should be avoided
if portability is a concern.

Cheers,

Sam Moffatt
http://pasamio.id.au

[Janich]

From a quick search, it doesnt look like MSSQL supports this either, so it would seem a very MySQL specific case.

But there are other ways to do it. 

Try check this thread out for inspiration on alternatives: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update (both MySQL and non-MySQL specific solutions there)

/ Janich

[Aerendir]

At this moment i'm working around the problem using a simple str_replace, something like:

$this->_dbo->setQuery(str_replace('INSERT INTO', 'INSERT IGNORE INTO', $query));

As Michael suggested, i tried to add an insert method to JDatabaseQueryMysql:

public function insertIgnore($table) 

{

$this->type = 'insert';

$this->insert = new JDatabaseQueryElement('INSERT IGNORE INTO', $table);

return $this;

}

It seems work well: the query is correctly built but i think, if interested in, that someone else should try it.