Re: [Sqlrelay-discussion] about insert_id

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

I believe you need to use 2 cursors in your code.

                $this->_masterDb = & Db::getDb("sqlrelay",9001);
                $this->_cur = sqlrcur_alloc( $this->_masterDb );
                sqlrcur_sendQuery( $this->_cur, "set names 'utf8'" );
                sqlrcur_sendQuery( $this->_cur, $query );

                $result =
$this->convertFromEmptySqlRelayResult($this->_cur);

		$this->_cur2 = sqlrcur_alloc( $this->_masterDb );
                sqlrcur_sendQuery($this->_cur2, "SELECT LAST_INSERT_ID()
as
Insert_ID");
                $row = sqlrcur_getRowAssoc($this->_cur2, 0);
                $this->log->debug("Row is ".LogUtil::format($row));
                $result->setInsertID($row["Insert_ID"]);
		sqlrcur_free($this->_cur);
		sqlrcur_free($this->_cur2);
                sqlrcon_endSession($this->_masterDb);

Each cursor can only keep track of a single result set.  If you run 1
query (in this case $query) and then use the same cursor to run another
query (in this case "select LAST_INSERT_ID() as Insert_ID") then the
result set of $query will be lost.  In this short bit of code, it
doesn't matter, but if it had called $result->Affected_Rows() after
running "select LAST_INSERT_ID()..." then it $result->Affected_Rows()
would return the number of affected rows from "select
LAST_INSERT_ID()..." because the cursor that was attached to $result was
reused for that query.

I added sqlrcur_free calls above where it's safe to delete the cursors.

Also, sqlrcur_getRowAssoc() requires 2 parameters: the cursor and the
row number.  In this case, row 0.  I added the second parameter in the
code above too.

Give it a try and let me know how it goes.

David Muse
dav...@fi...

On Fri, 2005-07-22 at 09:25 +0800, linuxman wrote:
> Work it out!
>  
> It seemed that, this line:
>  
> $row = sqlrcur_getRowAssoc($this->_cur);
>  
> has something wrong, I replace this line with:
>  
> $insertId = sqlcrur_getField($this->_cur, 0, "Insert_ID");
>  
> then everything go well...
>  
> But another problem, I don't know when to free the resources that
> sqlrcon_alloc and sqlrcur_alloc applied?
>  
> The trick is that, I can not free the cursor returned by
> sqlcur_sendQuery just after sendQuery finished, because I must
> transfer the cursor to resultset as the parameter. 
>  
> Thanks! 
> 
> 
> 
> 
> ______________________________________________________________________
> 发件人: sql...@li...
> [mailto:sql...@li...] 代表 Josh
> Johnson
> 发送时间: 2005年7月21日 23:08
> 收件人: sql...@li...
> 主题: Re: [Sqlrelay-discussion] 答复: [Sqlrelay-discussion] about
> insert_id
> 
> 
> 
> I had a feeling it was more complicated than I thought :) I haven't
> used sqlrelay enough (I've been using it with PEAR) to have run into
> this problem myself, but it's something I'll have to deal with too... 
> 
> -- JJ
> 
> linuxman wrote: 
> > thanks for JJ's quick response !
> > 
> > The code below is from the model class of my application:
> > 
> > ....
> > if the $query is insert something:
> > 
> >                 $this->_masterDb = & Db::getDb("sqlrelay",9001);
> >                 $this->_cur = sqlrcur_alloc( $this->_masterDb );
> >                 sqlrcur_sendQuery( $this->_cur, "set names 'utf8'" );
> >                 sqlrcur_sendQuery( $this->_cur, $query );
> > 
> >                 $result =
> > $this->convertFromEmptySqlRelayResult($this->_cur);
> > 
> >                 sqlrcur_sendQuery($this->_cur, "SELECT LAST_INSERT_ID() as
> > Insert_ID");
> >                 $row = sqlrcur_getRowAssoc($this->_cur);
> >                         $this->log->debug("Row is ".LogUtil::format($row));
> >                 $result->setInsertID($row["Insert_ID"]);
> >                 sqlrcon_endSession($this->_masterDb);
> >  
> > and convertFromEmptySqlRelayResult is here:
> > 
> >     function convertFromEmptySqlRelayResult( $cur ){
> >         $recordset = new EmptyMysqlResultSet_SqlRelay( $cur );
> >         return $recordset;
> >     }
> > 
> > And, EmptyMysqlResultSet_SqlRelay is defined as below:
> > 
> > class EmptyMysqlResultSet_SqlRelay{
> >     var $fields = false;
> >     var $EOF = true;
> >     function MoveNext() {return;}
> >     function RecordCount() {return 0;}
> >     function FieldCount() {return 0;}
> >     function EOF(){return TRUE;}
> >     function Close(){return true;}
> > 
> >     function emptyMysqlResultSet_SqlRelay($cur){
> >         $this->cursor = $cur;
> >     }
> > 
> >     function Affected_Rows(){
> >         return sqlrcur_affectedRows($this->cursor);
> >     }
> > 
> >     function setInsertID($id){
> >         $this->insert_ID = $id;
> >     }
> > 
> >     function getInsertID(){
> >         return $this->insert_ID;
> >     }
> > }
> > 
> > You see, sqlrelay is running port 9001, the query is like "INSERT ....".
> > 
> > But the problem is,  SELECT LAST_INSERT_ID() always return 0, I know this by
> > watch the log output of  "$this->log->debug("Row is
> > ".LogUtil::format($row))".
> > 
> > Any problem of my codes ? or any other way to get insert id? 
> > 
> > Thanks!
> > 
> > -----邮件原件-----
> > 发件人: sql...@li...
> > [mailto:sql...@li...] 代表 Josh Johnson
> > 发送时间: 2005年7月21日 22:14
> > 收件人: sql...@li...
> > 主题: Re: [Sqlrelay-discussion] about insert_id
> > 
> > try: SELECT LAST_INSERT_ID();
> > 
> > -- JJ
> > 
> > linuxman wrote:
> > 
> >   
> > > hi all,
> > > 
> > > I use mysql+php+adodb in my web application, and want to use sqlrelay 
> > > as database polling. But, I don't know how to get insert id just after 
> > > inserting a record with sqlrelay? you know , there is Insert_ID in 
> > > adodb to get the insert id, but I have not find the same function in 
> > > the php api of sqlrelay.
> > > 
> > > I used the 0.36 version of sqlrelay.
> > > 
> > > Any hints? Thanks in advance!
> > > 
> > > Linuxman
> > > 
> > > 
> > > 
> > > 
> > > 
> > > -------------------------------------------------------
> > > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies 
> > >     
> > >from IBM. Find simple to follow Roadmaps, straightforward articles, 
> >   
> > > informative Webcasts and more! Get everything you need to get up to 
> > > speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
> > > _______________________________________________
> > > Sqlrelay-discussion mailing list
> > > Sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > >  
> > > 
> > >     
> > 
> > 
> > 
> > 
> > -------------------------------------------------------
> > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies from
> > IBM. Find simple to follow Roadmaps, straightforward articles, informative
> > Webcasts and more! Get everything you need to get up to speed, fast.
> > http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > 
> > 
> > 
> > 
> > -------------------------------------------------------
> > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> > from IBM. Find simple to follow Roadmaps, straightforward articles,
> > informative Webcasts and more! Get everything you need to get up to
> > speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id492&op=click
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >   
> 