[sr-dev] mysql_db and UPDATE

[Fred Posner]

Interesting to say the least.

> Thoughts?

My belief, which does not seem to be popular, is that the best practice
utilizes stored procedures.

Many reasons why I like this including the following scenarios:

1) Need for a new column or structure in mysql will not result in the
need of modifying Kamailio code.

Take for example Kamailio is updating a field with calls in progress per
user in a company setting. Due to new employee controls, a table
structure needs to change and fields are to be remapped and linked among
several tables.

As far as Kamailio is concerned, this is irrelevant, since you can
update the procedure instead of the sql statement.

2) Update rows

You can do the update statement within the procedure and return
something like "fail" or "good" based on the successful run. Your
kamailio result will simply need to check the result.

3) Speed

You can optimize the procedures as needed to take advantage of indexes,
etc. without constantly changing the sql statement within kamailio.

4) One call, many actions

You can use one stored procedure to run many sql statements.

The above reasons are the most common scenarios where stored procedures
have continually helped me.

Fred Posner
The Palner Group, Inc.
http://www.palner.com (web)
+1-503-914-0999 (direct)
+1-954-472-2896 (fax)

On 08/08/2014 08:00 AM, Jason Penton wrote:
> Hi,
> 
> I have noticed that in some instances if you update a row in mysql via the
> mysql_db module and the actual row data does not change - affected_rows
> will return 0. This is the default behaviour for the mysql API as per -
> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
> 
> There is a flag (CLIENT_FOUND_ROWS) that can be used in the
> mysql_real_connect function that will cause affected_rows to return the
> number of rows that were "matched" - ie in the WHERE clause, as opposed to
> whether or not any data was changed.
> 
> If we don't it could be a problem for modules like usrloc where an update
> is done and if no row are "affected" and new row is added which would cause
> a duplicate.
> 
> I understand that updating a row with zero changes is not ideal and prob.
> not a typical use case but this IMO is irrelevant. I understand affected
> rows to mean the number of rows that were matched and not the number of
> rows that had data changed... - seems like a case of MySQL being too clever
> here ;)
> 
> Thoughts?
> 
> Cheers
> Jason
> 
> 
> 
> _______________________________________________
> sr-dev mailing list
> sr-dev at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
>