2008年8月1日星期五

Re: [fw-db] Cardinality Violation - executing queries

tony stamp wrote:
>
> I am getting this error:
> Integrity constraint violation: 1062 Duplicate entry '20410' for key 1
>

The query() method is the equivalent of doing a prepare() followed by an
execute().

So if you use query() you don't need to execute() as second time. Doing
this redundantly is no problem for the delete statement, since the second
execute will simply find no matching rows (they've already been deleted).

It is a problem, however, for insert, because you're trying to insert a
second row with the same primary key value, and this is causing the
duplicate entry error.

For what it's worth, the "cardinality violation" error, as you found out, is
due to comparing a single value to a multi-value tuple. MySQL allows
comparisons like "(a, b, c) = (1, 2, 3)" so you can compare multiple things
at once. But of course you must have the same number of items (i.e. the
same cardinality) in each list. You were getting multiple columns returned
by the subquery because the default columns fetched by the Zend_Db_Select
interface are '*'.

Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/Cardinality-Violation---executing-queries-tp18772524p18776592.html
Sent from the Zend DB mailing list archive at Nabble.com.

Re: [fw-db] Oracle cursors returned from stored procedures

This is fantastic! Thank you.


funkyfly wrote:
>
> Hey,
> first i wanna say that it was a fast solution but working for me very
> well,
> nonetheless it can be more flexible, because now this method binds always
> name *cursor* to the statement.
>
> Usage:
> $params['rowsPerPage'] = 1;
> $params['order'] = 'date_added';
> $params['order_dir'] = 'ASC';
> $params['adId'] = (int)$adId;
> $sql = 'BEGIN dik.get_adverts(:cursor, :rowsPerPage, :order,
> :order_dir, :adId); END;';
>
> $rows = $this->_db->fetchCursor($sql, $params);
>
> Code:
>
> class My_Db_Adapter_Oracle extends Zend_Db_Adapter_Oracle
> {
> public function fetchCursor($sql, $bind = array())
> {
> $data = array();
> $conn = $this->getConnection();
>
> $curs = oci_new_cursor($conn);
> $stmt = oci_parse($conn, $sql);
>
> oci_bind_by_name($stmt, "cursor", $curs, -1, OCI_B_CURSOR);
> foreach ($bind as $key=>&$val) {
> oci_bind_by_name($stmt, $key, $val, -1, OCI_ASSOC);
> }
>
> oci_execute($stmt);
> if ($e = oci_error($stmt)) {
> throw new Zend_Db_Adapter_Oracle_Exception($e, -1234);
> }
> oci_execute($curs);
>
> if (oci_fetch_all($curs, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW))
> {
> // var_dump($data);
> }
>
> oci_free_statement($stmt);
> oci_free_statement($curs);
>
> return $data;
> }
> }
>

--
View this message in context: http://www.nabble.com/Oracle-cursors-returned-from-stored-procedures-tp18634078p18775090.html
Sent from the Zend DB mailing list archive at Nabble.com.

Re: [fw-core] AjaxContext and ContextSwitch question

Oddly enough I ran into this problem yesterday as well that I am trying to track down.  I have used AjaxContext/ContextSwitching without a problem in the past so I am not sure what is happening.

The view that is rendered is: update-module-preferences.phtml when it should be update-module-preferences.ajax.phtml.  This was verified through Firebug.  In addition I also tested in the action if the request was an ajax request ($this->_request->isXmlHttpRequest() ? "Yes" : "No";) and the response is yes.

I am using jQuery as my javascript library.

Controller:

[code]

    public function init() {
   
    $this->_logger         = Zend_Registry::get('logger');
    $this->_globalConfig   = Zend_Registry::get('globalConfig');
    $this->_homepageConfig = Zend_Registry::get('hpConfig');
       
        $this->_helper->layout->setLayout('layout-mystjude');
   
        $this->_ajaxContext = $this->_helper->getHelper('AjaxContext');
   
    $this->_ajaxContext->addActionContext('update-module-preferences', 'html');
    $this->_ajaxContext->initContext();
   
    $this->_modulePreferences = new SjcrhModulePreferences();
    $this->_joomlaContent     = new JoomlaContent();
  }


    public function updateModulePreferencesAction() {
   
        if ($this->_request->isPost()) {
           
            $auth          = Zend_Auth::getInstance()->getIdentity();
            $this->_userId = $auth['username'];
   
            $module = $this->view->escape($this->_getParam('mod'));
            $status = $this->view->escape($this->_getParam('status'));
           
            $where  = $this->_modulePreferences->getAdapter()
                                           ->quoteInto('username = ? ', $this->_userId);
   
        $select = $this->_modulePreferences->select()->where($where);
        $row    = $this->_getModulePositions($this->_userId);
       
        $positions = split(",", $row);
       
        foreach ($positions as $k => $position) {
           
            $regex         = "/(".$module.")=(show|hide)/i";
            $replacement   = '${1}='.$status;
            $positions[$k] = preg_replace($regex, $replacement, $positions[$k]);
        }
       
        $data = array('positions' => implode($positions, ","));

        $this->_modulePreferences->update($data, $where);
       
        $userLog = SJCRH_Util::logUserAction($this->_userId, "{$module} Updated");
       
        $this->view->ajax = $this->_request->isXmlHttpRequest() ? "Yes" : "No";

        }
    }

[/code]

Ajax Call

[code]
    $.ajax({
        url: '/home/mystjude/index/update-module-preferences/mod/' + divid + '/status/' + divStatus,
        type: 'POST',
        dataType: 'text',
        error: function(){
          $("#message").html('Error saving preferences');
        },
        success: function(data){
            $("#message").css("background-color", "yellow").html("Preference Saved").show().fadeOut();
        }
    });
[/code]

View Script [update-module-preferences.ajax.phtml is an empty file since all that is done is posting]




On Thu, Jul 31, 2008 at 7:57 AM, Matthew Weier O'Phinney <matthew@zend.com> wrote:
-- Ramon de la Fuente <ramon@delafuente.nl> wrote
(on Thursday, 31 July 2008, 10:27 AM +0200):
> I have started using the AjaxContext helper, but I'm running into the
> problem that although the right viewscript is being called
> (action.ajax.phtml), the Layout is not being disabled by default. I read
> this should happen when using ContextSwitch, and in the maual it states
> that AjaxContext is a special case of ContextSwitch; Am I reading it
> wrong?

I've been using AjaxContext and ContextSwitch without issue. Is it
possible that you're forwarding to another action? That could
potentially re-enable the layout.

Otherwise, can you provide a reproduce case for me to examine?

--
Matthew Weier O'Phinney
Software Architect       | matthew@zend.com
Zend Framework           | http://framework.zend.com/




--
Cory Wiles
kwylez@gmail.com
http://www.corywiles.com
http://www.400mtogo.com

Re: [fw-core] iPhone support

Wow! A really nice library indeed!
I didn't know it!
Thank you very much!

On Fri, Aug 1, 2008 at 10:14 AM, Ernesto <ernesto@emb.com.mx> wrote:
Hello,

I recently implemented an iphone version for our mexican tv guide app
(iphone.enlatele.com.mx) and used, as Matthew said, ContextSwitch
action helper. I'd also like to recommend you IUI. Maybe you already
know it, but just in case, it is the a very easy way to get iphone's
native look and feel  http://code.google.com/p/iui/


Ernesto



On Thu, Jul 31, 2008 at 11:10 PM, Ramses Paiva <ramses@onecode.com.br> wrote:
> Great!
> Thank you guys!
>
> On Thu, Jul 31, 2008 at 6:25 PM, Matthew Weier O'Phinney <matthew@zend.com>
> wrote:
>>
>> -- Ramses Paiva <ramses@onecode.com.br> wrote
>> (on Thursday, 31 July 2008, 02:31 PM +0530):
>> > I'm currently working on a web application that is going to be accessed
>> > by
>> > iPhone devices and I'm wondering if the framework has some kind of
>> > support for
>> > this devices.
>>
>> You may want to investigate the ContextSwitch action helper; you could
>> easily create a callback that examines the current HTTP headers to
>> switch contexts.
>>
>> --
>> Matthew Weier O'Phinney
>> Software Architect       | matthew@zend.com
>> Zend Framework           | http://framework.zend.com/
>>
>
>
>
> --
> Ramses Paiva
> +91 97 4237 6432
>



--
Ramses Paiva
+91 97 4237 6432

Re: [fw-db] Cardinality Violation - executing queries

ok, i was missing the $result = $db::query in the second one- explains
everything.

But now i am getting an error that is not down to any oversight (i hope!)

I am getting this error:

Integrity constraint violation: 1062 Duplicate entry '20410' for key 1

...even though it seems to insert the data ok?


--
View this message in context: http://www.nabble.com/Cardinality-Violation---executing-queries-tp18772524p18773271.html
Sent from the Zend DB mailing list archive at Nabble.com.

Re: [fw-db] Cardinality Violation - executing queries

ahh i had more than one column in the 'in' clause! That's fixed now and
generates working sql, but i am having trouble executing them - just this
section:

$db->beginTransaction();
try {
$result = $db->query($deleteQuery);
$result->execute();
$db->query($insertQuery);
$result->execute();
$db->commit();
}catch (Exception $e){
echo $e->getMessage();
$db->rollBack();
}
do i need to explicitly execute the query? It seems to delete the rows (the
first query) but doesn't perform the second one, as the table is missing the
rows - they are not being replaced by the second query. I am having trouble
understanding the return value from $result::execute. How can i see if it
was successful or not?
--
View this message in context: http://www.nabble.com/Cardinality-Violation---executing-queries-tp18772524p18773013.html
Sent from the Zend DB mailing list archive at Nabble.com.

[fw-db] Cardinality Violation - executing queries

Hello

I am trying to restore a subset of data from a backup table to a current
table. I have the following code:

$db = $this->_registry->get('db');

$select = $db->select();
$select->from(array('backupUsers'));
if($name= $request->getItem('name')){
$select->where('name = ' . $db->quote($name));
}

if($surname= $request->getItem('surname')){
$select->where('surname= ' . $db->quote($surname));
}


$deleteQuery = "DELETE FROM users WHERE user_id IN (" . $select . ")";
$insertQuery = "INSERT INTO users" . $select;

$db->beginTransaction();
try {
$result = $db->query($deleteQuery);
$result->execute();
$db->query($insertQuery);
$result->execute();
$db->commit();
}catch (Exception $e){
echo $e->getMessage();
$db->rollBack();
}

this code looks in the current table for any pk's that exist in the backup
table, and deletes those rows from the current table. It then inserts the
data from the backup table into the current table. The deletion of the row
from the current table first is to avoid pk conflicts.

This produces valid sql which works when run through phpMyAdmin, but from
php i get the following error:
Cardinality violation: 1241 Operand should contain 1 column(s)
What does this mean? Is there anything wrong with the above?
--
View this message in context: http://www.nabble.com/Cardinality-Violation---executing-queries-tp18772524p18772524.html
Sent from the Zend DB mailing list archive at Nabble.com.