sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] why do not startup?

[Sha Li J. <lig...@sh...>]

berry han 写道:
> hello:
>   I have problem:   why do not startup?
>
> note
> [root@localhost bin]# ./sqlr-start -id mysqltest
>
> Starting listener:
>   sqlr-listener -id mysqltest -config /usr/local/sqlrelay/etc/sqlrelay.conf
> sh: sqlr-listener: command not found
>
> sqlr-listener failed to start.
>   
it seems you haven't the sqlr-listener file which is an important daemon 
of the sqlreplay , what distribution do you use? if your linux box has 
the apt-get system, you have better apt-get the sqlrelay package other 
than compiling it by yourself.

Re: [Sqlrelay-discussion] why do not startup?

[Sha Li J. <lig...@sh...>]

berry han 写道:
> hello:
>   I have problem:   why do not startup?
>
> note
> [root@localhost bin]# ./sqlr-start -id mysqltest
>
> Starting listener:
>   sqlr-listener -id mysqltest -config /usr/local/sqlrelay/etc/sqlrelay.conf
> sh: sqlr-listener: command not found
>
> sqlr-listener failed to start.
>   
it seems you haven't the sqlr-listener file which is an important daemon 
of the sqlreplay , what distriubition do you use? if your linux box has 
the apt-get system, you have better apt-get the sqlrelay package other 
than compiling it by yourself.

Re: [Sqlrelay-discussion] why do not startup?

[Alexander K. <ak-...@cy...>]

Hi Berry,

On Wed, Oct 24, 2007 at 10:13:35AM +0800, berry han wrote:
> hello:
>   I have problem:   why do not startup?
> 
> note
> [root@localhost bin]# ./sqlr-start -id mysqltest
> 
> Starting listener:
>   sqlr-listener -id mysqltest -config /usr/local/sqlrelay/etc/sqlrelay.conf
> sh: sqlr-listener: command not found
> 
> sqlr-listener failed to start.
Looks like sqlr-listener is not in your $PATH to me. Have you tried
adding /usr/local/sqlrelay/bin to it?

Best regards,
    Alex
-- 
Dipl.-Math. Alexander Klink | IT-Security Engineer
        ak-...@cy... | working @ urn:oid:1.3.6.1.4.1.11417

[Sqlrelay-discussion] why do not startup?

[berry h. <ber...@ho...>]

hello:
  I have problem:   why do not startup?

note
[root@localhost bin]# ./sqlr-start -id mysqltest

Starting listener:
  sqlr-listener -id mysqltest -config /usr/local/sqlrelay/etc/sqlrelay.conf
sh: sqlr-listener: command not found

sqlr-listener failed to start.


Thanks to MP3.com for sponsoring:
        Clustered/Replicated database support.
        Perl API.
Thanks to FeedLounge for sponsoring:
        Query routing and filtering.
[root@localhost bin]#

install
  Redhat AS4,rudiments-0.31.tar.gz,sqlrelay-0.39.4.tar.gz,

../configure --prefix=/usr/local/sqlrelay 
--with-rudiments-prefix=/usr/local/rudiments 
--with-mysql-prefix=/usr/local/mysql --with-php-prefix=/usr/local/php/ 
--with-oci8=instantclient-prefix=/usr/lib/oracle/10.2.0.1/client/lib

add sql_relay.so

sqlrelay.conf:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="mysqltest" port="9000" socket="/tmp/mysqltest.socket" 
dbase="mysql" connections="3" maxconnections="5" maxqueuelength="0" 
growby="1" ttl="60" endofsession="commit" sessiontimeout="600" 
runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" 
handoff="pass">
                <users>
                        <user user="mysqltest" password="mysqltest"/>
                </users>
                <connections>
                        <connection connectionid="mysqltest" 
string="user=root;password=mobonet;db=mobonet" metric="1"/>
                </connections>
        </instance>

</instances>


thinks,
                   berry

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[Sqlrelay-discussion] Failed to get a cursor id!

[jzhang <jz...@po...>]

Hi!
I use mysql replication and sqlrelay for load banlancing. My symfony program got some error:
ONE:[DBException]Failed to get a cursor id. A network error may have ocurred. Success stack trace
TWO:[DBException] stack trace
the hint followed them were the same. Just as follows:
[DBException]
stack trace
at ()
in SF_ROOT_DIR/lib/db.class.php line 370 ...

367    if(!sqlrcur_executeQuery($sqlrcursor->cursor))
368    {
369      $this->errmessage = sqlrcur_errorMessage($sqlrcursor->cursor);
370      throw new DBException( $this->errmessage );
371    }
372    //sqlrcon_endSession($this->con);
373    return $sqlrcursor;
at DB->execute(object('DB_sqlrelay_cursor'))
in SF_ROOT_DIR/apps/frontend/modules/manage/actions/listMySuppliersAction.class.php line 16 ...

        //p_numPerPage int 
        $db->setString($smst, '3', sfConfig::get('app_listMySuppliers') ); 
         
    $result = $db->execute($smst); 

        $suppliers=array(); 
      while($row=$db->fetchAssoc($result)) { 
at listMySuppliersAction->execute()
in SF_SYMFONY_LIB_DIR/filter/sfExecutionFilter.class.php line 115 ...


          // execute the action
          $actionInstance->preExecute();
          $viewName = $actionInstance->execute();
          if ($viewName == '')
          {
            $viewName = sfView::SUCCESS;
at sfExecutionFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/filter/sfFlashFilter.class.php line 50 ...

    }

    // execute next filter
    $filterChain->execute();

    // remove flash that are tagged to be removed
    $names = $userAttributeHolder->getNames('symfony/flash/remove');
at sfFlashFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/filter/sfCommonFilter.class.php line 29 ...

  public function execute($filterChain)
  {
    // execute next filter
    $filterChain->execute();

    // execute this filter only once
    $response = $this->getContext()->getResponse();
at sfCommonFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_ROOT_DIR/apps/frontend/lib/filter/navigatorFilter.class.php line 84 ...

        //var_dump($this->getContext());
        $this->getContext()->getRequest()->setParameter('navigator',$navigator);
        $this->getContext()->getRequest()->setParameter('menu',$menu);
    $filterChain->execute();
  }
} 
at navigatorFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_ROOT_DIR/apps/frontend/lib/filter/myBasicSecurityFilter.class.php line 106 ...

      if ($credential === null || $user->hasCredential($credential))
      {
        // the user has access, continue
        $filterChain->execute();
      }
      else
      {
at myBasicSecurityFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/filter/sfWebDebugFilter.class.php line 35 ...

    }

    // execute next filter
    $filterChain->execute();

    $context    = $this->getContext();
    $response   = $context->getResponse();
at sfWebDebugFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/filter/sfRenderingFilter.class.php line 33 ...

  public function execute($filterChain)
  {
    // execute next filter
    $filterChain->execute();

    if (sfConfig::get('sf_logging_enabled'))
    {
at sfRenderingFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 43 ...

      }

      // execute the next filter
      $this->chain[$this->index]->execute($this);
    }
  }

at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/controller/sfController.class.php line 276 ...

        }

        // process the filter chain
        $filterChain->execute();
      }
      else
      {
at sfController->forward('manage', 'listMySuppliers')
in SF_SYMFONY_LIB_DIR/controller/sfFrontWebController.class.php line 48 ...

      $actionName = $request->getParameter('action');

      // make the first request
      $this->forward($moduleName, $actionName);
    }
    catch (sfException $e)
    {
at sfFrontWebController->dispatch()
in SF_ROOT_DIR/web/frontend/frontend_dev.php line 11 ...


require_once(SF_ROOT_DIR.DIRECTORY_SEPARATOR.'apps'.DIRECTORY_SEPARATOR.SF_APP.DIRECTORY_SEPARATOR.'config'.DIRECTORY_SEPARATOR.'config.php');

sfContext::getInstance()->getController()->dispatch();

"SF_ROOT_DIR/lib/db.class.php" is a packaging program in order to supply a interface to sqlrelay for other programs . The error one comes out more often than error tow. I think the error one is brought by error two. If i use the router instance tranmit to the connection instance , the errors come often; if i get rid of router instance ,the errors gone. 
This afternoon i debugged, results are as follows:

debugging from router instance:
query:
call sp_getMySuppliers(?,?,?,@totalCount,@totalPages,@currentPage)
getting query succeeded
getting input binds...
:1
STRING
75
:2
INTEGER
1
:3
INTEGER
5
done getting input binds
getting output binds...
done getting output binds
getting send column info...
send column info
done getting send column info...
processing query...
preparing/executing...
commit or rollback check...
commit or rollback needed
done with commit or rollback check
processing query succeeded
done processing query

     debugging from connect instance:

query:
call sp_getMySuppliers(?,?,?,@totalCount,@totalPages,@currentPage)
getting query succeeded
getting input binds...
:1
STRING
75
:2
INTEGER
1
:3
INTEGER
5
done getting input binds
getting output binds...
done getting output binds
getting send column info...
send column info
done getting send column info...
processing query...
preparing/executing...
commit or rollback check...
commit or rollback needed
done with commit or rollback check
processing query succeeded
done processing query
returning result set header...
returning row counts...
sending row counts...
actual rows: 3
affected rows: -1
done sending row counts
done returning row counts
column info will be sent
returning column counts...
done returning column counts
sending column type format...
id's
done sending column type format
returning column info...
supplierID:2:4 (10,0) NOT NULL Primary key 
supplierInfoStateID:4:1 (1,0) NOT NULL 
dataInputTypeID:4:1 (2,0) NOT NULL 
branchEmployeeID:3:2 (5,0) 
memberID:55:8 (15,0) 
spiderSourceID:2:4 (10,0) 
processDate:6:8 (19,0) NOT NULL 
businessScope:1:763 (762,0) NOT NULL 
description:44:16777215 (196605,0) NOT NULL 
annualSales:1:61 (60,0) 
contactID:55:8 (15,0) NOT NULL 
dialCount:2:4 (10,0) NOT NULL 
url:1:451 (450,0) 
supplierType:34:3 (3,0) NOT NULL 
supplierScore:15:4 (3,1) NOT NULL 
pageStyle:4:1 (3,0) NOT NULL 
hasPojaaPage:4:1 (1,0) 
pojaaURL:1:451 (450,0) 
profileLastUpdate:6:8 (19,0) NOT NULL 
viewCounter:55:8 (20,0) NOT NULL 
bookmarkCounts:2:4 (10,0) NOT NULL 
submitIPAddress:1:121 (120,0) 
enterpriseTypeID:4:1 (2,0) NOT NULL 
companyName:1:301 (300,0) NOT NULL 
companyNameAlias1:1:91 (90,0) 
branchCompanyName:1:91 (90,0) 
SFranchiseID:2:4 (10,0) 
sub4industryCode:34:15 (15,0) 
dateEstablished:6:8 (19,0) NOT NULL 
registeredCapital:14:8 (22,31) 
CurrencyTypeID:4:1 (3,0) 
incorporationPlace:1:61 (60,0) 
focusMarkets:1:751 (750,0) 
CEO_President:1:61 (60,0) 
chairman:1:61 (60,0) 
annualExportUSD:1:61 (60,0) 
numRDStaff:1:61 (60,0) 
numStaff:1:61 (60,0) 
OEMService:4:1 (1,0) NOT NULL 
AuthorizedMemberID:55:8 (15,0) NOT NULL 
ForeignSupplierCode:34:42 (42,0) 
ChineseInvestor1:1:301 (300,0) 
ForeignInvestor1:1:301 (300,0) 
FI_NationID:4:1 (3,0) NOT NULL 
numForeignStaff:1:61 (60,0) 
majorCustomers:1:751 (750,0) 
targetCustomers:1:751 (750,0) 
sub3CategoryID:3:2 (5,0) NOT NULL 
sub4CategoryID:3:2 (5,0) NOT NULL 
done returning column info
returning output bind values
0
done returning output bind values
done returning result set header
handle query succeeded
returning result set data...
skipping 0 rows...
done skipping rows
fetching 10 rows...
"384","3","1","0","75","0","2007-10-17 21:21:19","shop1111","shop1111","0","578","0","","E","0.0","0","0",NULL"2007-10-18 16:14:11","0","0","192.168.1.17","0","shop1111","","","0",NULL"0000-00-00 00:00:00","0","0","","","","","","","","0","0",NULL"","","1","","","","10020","50086",
"383","3","1","0","75","0","2007-10-17 21:10:27","shop2222","shop2222","0","573","0","","E","0.0","0","0",NULL"2007-10-18 16:14:29","1","0","192.168.1.17","0","shop2222","","","0",NULL"0000-00-00 00:00:00","0","0","","","","","","","","0","0",NULL"","","1","","","","10010","50032",
"381","3","1","0","75","0","2007-10-17 20:29:20","shop3333","shop3333","0","533","0","","E","0.0","0","0",NULL"2007-10-18 16:14:48","0","0","192.168.1.17","0","shop3333","","","0",NULL"0000-00-00 00:00:00","0","0","","","","","","","","0","0",NULL"","","1","","","","10025","50104",
done returning result set data
getting command...
done getting command
identify
getting command...
getting command failed: client sent bad command or timed out
end session
ending session...
aborting all busy cursors...




jzhang
2007-10-19

[Sqlrelay-discussion] DBD Borkenpipe

[nemux <ne...@cp...>]

Hello all,

i'm trying to do some tests with sqlrelay i'm doing that on :

FreeBSD 4.2 
mysql client 3
Mysql server 5.1
sqlrelay-0.35_1 (installed from ports)

sqlrelay starts without problems but when i use this simple perl
script :

#!/usr/bin/perl
use DBI;

$dbh=DBI->connect("dbi:SQLRelay:host=127.0.0.1;port=3307;debug=1","user","password") or die DBI->errstr;

$dbh->disconnect;

#perl test.pl
Connecting to listener...
Inet socket: 127.0.0.1:3307
Authenticating with listener : user:password
No authentication error.
Must Not Reconnect.
Authenticating with connection : user:password
Broken pipe
#

I have the same error bypassing DBD using SQLRelay.pm  :

$con=SQLRelay::Connection->new("127.0.0.1","3307","","user","password",0,1);
$cur=SQLRelay::Cursor->new($con);

$con->ping();

#perl testSQLRelay.pl
Broken pipe
#

i use DBI::mysql on the same server without issues 

Someone know how can i solve this issue ?

Thanks.

Re: [Sqlrelay-discussion] Bug report, open client connections not freed, with code to reproduce problem

[David M. <dav...@fi...>]

Thanks,

I'll check it out and let you know.

Dave
dav...@fi...

On Mon, 2007-10-15 at 12:32 +0200, Ingmar Brouns wrote:
> Hi David,
> 
> I am using:
> 
> sqlrelay 0.39.4
> Oracle 10g
> php 4.3.11
> 
> I've discovered a bug that will cause sqlrelay to think that client
> connections are still open while they are not. This is quite
> troublesome, as once all client connections are taken, sqlrelay will
> have to be restarted. I've been able to isolate the bug in a piece of
> code. In the code, I've denoted two sqlrcur_free statements as #A and
> #B respectively. Trouble will occur in the situation in which #A has
> not been commented out while #B has been commented out. When both have
> been commented out, there is no problem. To run the example, first a
> simple table has to be created: 
> 
> CREATE TABLE t1 
> (
> c1 varchar(20),
> c2 varchar(20),
> c3 varchar(20),
> c4 varchar(20),
> c5 varchar(20),
> c6 varchar(20),
> c7 varchar(20),
> c8 varchar(20),
> c9 varchar(20),
> c10 varchar(20),
> c11 varchar(20),
> c12 varchar(20),
> c13 varchar(20),
> c14 varchar(20),
> c15 varchar(20),
> c16 varchar(20),
> c17 varchar(20)
> );
> 
> It is actually also possible to reveil the bug using only DUAL, but I
> needed a lot of duals to do so. Hence, I think this example is better.
> Below is the example code: 
> 
> //Funtion to connect to a db_schema, initializes the variable $conn
> //connect_to_user_db($DB_SCHEMA);
> 
> $query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype; BEGIN
> OPEN ref_cur FOR 
>         SELECT   
>             t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3, t3.c1, t3.c2,
> t3.c3                              
>         FROM    t1 
>         ,       t1 t2 
>         ,       t1 t3 
>         WHERE 1 <> 1;
>        :query_handle := ref_cur ; END; 
>        ";
>        
>     $cur = sqlrcur_alloc($conn);
>     sqlrcur_prepareQuery($cur, $query);
>     sqlrcur_defineOutputBindCursor($cur, "query_handle");
>     sqlrcur_executeQuery($cur);
>     $query_cursor = sqlrcur_getOutputBindCursor($cur,
> "query_handle"); 
>     sqlrcur_fetchFromBindCursor($query_cursor); 
>     sqlrcur_free($query_cursor);                           //#A
>     sqlrcur_free($cur);
> 
> 
>     $cur = sqlrcur_alloc($conn);
>     $query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype;
> BEGIN OPEN ref_cur FOR SELECT * FROM DUAL; :query_handle := ref_cur ;
> END;";     
>     sqlrcur_prepareQuery($cur, $query);
>     sqlrcur_defineOutputBindCursor($cur, "query_handle");
>     sqlrcur_executeQuery($cur);
>     $query_cursor = sqlrcur_getOutputBindCursor($cur,
> "query_handle"); 
>     sqlrcur_fetchFromBindCursor($query_cursor);
>     //sqlrcur_free($query_cursor);                          //#B
>     sqlrcur_free($cur);
>     sqlrcon_free($conn);
> 
> Just hit the refresh button a couple of times, and with sqlr-status
> you will see the open client connections increase, these are never
> released again. Now the strange thing is that if you look in the
> directory  /usr/local/firstworks/var/sqlrelay/tmp/pids/, then there is
> a file for each connection related to the schema. However, if you grep
> on the process list for these pids, then you will find that these
> processes do not exist. This is why I think that sqlrelay thinks that
> the connection is still there while it is not. When you stop sqlrelay
> using sqlr-stop, then these pid files are not deleted and sqlr-stop
> will complain that these processes do not exist. 
> 
> Please let me know whether the code has helped reproducing the
> problem. If you have any questions, let me know.
> 
> Regards,
> 
> Ingmar
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] Bug report, open client connections not freed, with code to reproduce problem

[Ingmar B. <sw...@gm...>]

Hi David,

I am using:

sqlrelay 0.39.4
Oracle 10g
php 4.3.11

I've discovered a bug that will cause sqlrelay to think that client
connections are still open while they are not. This is quite troublesome, as
once all client connections are taken, sqlrelay will have to be restarted.
I've been able to isolate the bug in a piece of code. In the code, I've
denoted two sqlrcur_free statements as #A and #B respectively. Trouble will
occur in the situation in which #A has not been commented out while #B has
been commented out. When both have been commented out, there is no problem.
To run the example, first a simple table has to be created:

CREATE TABLE t1
(
c1 varchar(20),
c2 varchar(20),
c3 varchar(20),
c4 varchar(20),
c5 varchar(20),
c6 varchar(20),
c7 varchar(20),
c8 varchar(20),
c9 varchar(20),
c10 varchar(20),
c11 varchar(20),
c12 varchar(20),
c13 varchar(20),
c14 varchar(20),
c15 varchar(20),
c16 varchar(20),
c17 varchar(20)
);

It is actually also possible to reveil the bug using only DUAL, but I needed
a lot of duals to do so. Hence, I think this example is better. Below is the
example code:

//Funtion to connect to a db_schema, initializes the variable $conn
//connect_to_user_db($DB_SCHEMA);

$query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype; BEGIN OPEN
ref_cur FOR
        SELECT
            t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3, t3.c1, t3.c2,
t3.c3

        FROM    t1
        ,       t1 t2
        ,       t1 t3
        WHERE 1 <> 1;
       :query_handle := ref_cur ; END;
       ";

    $cur = sqlrcur_alloc($conn);
    sqlrcur_prepareQuery($cur, $query);
    sqlrcur_defineOutputBindCursor($cur, "query_handle");
    sqlrcur_executeQuery($cur);
    $query_cursor = sqlrcur_getOutputBindCursor($cur, "query_handle");
    sqlrcur_fetchFromBindCursor($query_cursor);
    sqlrcur_free($query_cursor);                           //#A
    sqlrcur_free($cur);


    $cur = sqlrcur_alloc($conn);
    $query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype; BEGIN
OPEN ref_cur FOR SELECT * FROM DUAL; :query_handle := ref_cur ; END;";
    sqlrcur_prepareQuery($cur, $query);
    sqlrcur_defineOutputBindCursor($cur, "query_handle");
    sqlrcur_executeQuery($cur);
    $query_cursor = sqlrcur_getOutputBindCursor($cur, "query_handle");
    sqlrcur_fetchFromBindCursor($query_cursor);
    //sqlrcur_free($query_cursor);                          //#B
    sqlrcur_free($cur);
    sqlrcon_free($conn);

Just hit the refresh button a couple of times, and with sqlr-status you will
see the open client connections increase, these are never released again.
Now the strange thing is that if you look in the directory
/usr/local/firstworks/var/sqlrelay/tmp/pids/, then there is a file for each
connection related to the schema. However, if you grep on the process list
for these pids, then you will find that these processes do not exist. This
is why I think that sqlrelay thinks that the connection is still there while
it is not. When you stop sqlrelay using sqlr-stop, then these pid files are
not deleted and sqlr-stop will complain that these processes do not exist.

Please let me know whether the code has helped reproducing the problem. If
you have any questions, let me know.

Regards,

Ingmar

Re: [Sqlrelay-discussion] ORA-01480 with long CLOBs?

[Alexander K. <ak-...@cy...>]

Hi David,

thanks for the quick reply.

On Fri, Oct 12, 2007 at 11:13:27AM -0400, David Muse wrote:
> > I assume the DBD::Oracle module enlarges this value and DBD::SQLRelay
> > doesn't - could this be the reason for my error?
> 
> I've never heard of that before, or of a way to enlarge it.  Could you
> send me the url where they talk about it.
http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a67846/datatype.htm

> A quick fix is to edit src/connection/sqlrcursor/binds.C and around line
> 51, change:
> 
> 		inbindvars[i].valuesize,
> 
> to:
> 
> 		inbindvars[i].valuesize+1,
> 
> Then rebuild/reinstall.
> 
> Give that a try and let me know if it fixes the problem or not.
Unfortunately, that did not help :-( Any other ideas?

Best regards,
  Alex
-- 
Dipl.-Math. Alexander Klink | IT-Security Engineer
        ak-...@cy... | working @ urn:oid:1.3.6.1.4.1.11417

Re: [Sqlrelay-discussion] ORA-01480 with long CLOBs?

[David M. <dav...@fi...>]

On Fri, 2007-10-12 at 12:05 +0200, Alexander Klink wrote:
> Hi,
> 
> I am one of the core developers of the OpenXPKI project - an open source
> certificate authority written in Perl and I was looking into sqlrelay as
> a way to reduce the number of database connections we currently open
> (but do not actively use at the same time).
> 
> We are using Oracle at a customer here and as a quick test I've
> replaced the connection string to use SQLRelay instead of Oracle.
> 
> Unfortunately, now I run into the following problem:
> ORA-01480: trailing null missing from STR bind value;
> while doing the following query:
> insert into L2CA1.config (config_identifier, data) VALUE (?, ?)
> The config table consists of a data CLOB, and the bind value is
> a few kilobytes big. Googling for the Oracle error shows that it
> apparently looks for the \0 delimiter in the first few hundred(?)
> characters only.
> 
> I assume the DBD::Oracle module enlarges this value and DBD::SQLRelay
> doesn't - could this be the reason for my error?

I've never heard of that before, or of a way to enlarge it.  Could you
send me the url where they talk about it.

I think I see a bug in the SQL Relay code causing it to leave off the
trailing \0 though.

A quick fix is to edit src/connection/sqlrcursor/binds.C and around line
51, change:

		inbindvars[i].valuesize,

to:

		inbindvars[i].valuesize+1,

Then rebuild/reinstall.

Give that a try and let me know if it fixes the problem or not.

Dave Muse
dav...@fi...

> 
> Any help highly appreciated ...
> Best regards,
>   Alex

[Sqlrelay-discussion] ORA-01480 with long CLOBs?

[Alexander K. <ak-...@cy...>]

Hi,

I am one of the core developers of the OpenXPKI project - an open source
certificate authority written in Perl and I was looking into sqlrelay as
a way to reduce the number of database connections we currently open
(but do not actively use at the same time).

We are using Oracle at a customer here and as a quick test I've
replaced the connection string to use SQLRelay instead of Oracle.

Unfortunately, now I run into the following problem:
ORA-01480: trailing null missing from STR bind value;
while doing the following query:
insert into L2CA1.config (config_identifier, data) VALUE (?, ?)
The config table consists of a data CLOB, and the bind value is
a few kilobytes big. Googling for the Oracle error shows that it
apparently looks for the \0 delimiter in the first few hundred(?)
characters only.

I assume the DBD::Oracle module enlarges this value and DBD::SQLRelay
doesn't - could this be the reason for my error?

Any help highly appreciated ...
Best regards,
  Alex
-- 
Dipl.-Math. Alexander Klink | IT-Security Engineer
        ak-...@cy... | working @ urn:oid:1.3.6.1.4.1.11417

Re: [Sqlrelay-discussion] the inputBindString posts the last value into the destination table repeatly

[Sha Li J. <lig...@sh...>]

I assigned the values to a array before executing the executeQuery 
function, it works ,thank you very much.

Alfred J Fazio 写道:
> Hello,
>
>     This is happening because the inputBindString function associates 
> a pointer to a string with a variable, but does not use that pointer 
> until the executeQuery function is called.  In your code, you 
> associate all of your query variables to a single pointer (body).  
> When the executeQuery function is called, the pointer body points to 
> the last string you assigned it ("zxvf").  Try using different 
> pointers for each query variable.
>
> Good luck
>

Re: [Sqlrelay-discussion] the inputBindString posts the last value into the destination table repeatly

[Alfred J F. <alf...@gm...>]

Hello,

    This is happening because the inputBindString function associates a
pointer to a string with a variable, but does not use that pointer until the
executeQuery function is called.  In your code, you associate all of your
query variables to a single pointer (body).  When the executeQuery function
is called, the pointer body points to the last string you assigned it
("zxvf").  Try using different pointers for each query variable.

Good luck

On 10/2/07, Sha Li Jun <lig...@sh...> wrote:
>
> hi all:
>
>    I wroten a simple C program to test the inputBindString function of
> the sqlrelay , the destination table was created in a mysql database .
> its table  structure is  term_no varchar(3), order_no varchar(4),
> book_name varchar(120), publisher varchar(10), and the C program
> contains follow source code to insert values into the table,
>
> #include <sqlrelay/sqlrclientwrapper.h>
> #include <string.h>
>
> int main(){
>
>         char *body;
>
>         sqlrcon mysql_con = sqlrcon_alloc("192.168.1.101",5900,"",
> "mydbuser","userpwd",0,1);
>         sqlrcur mysql_cur = sqlrcur_alloc(mysql_con);
>
>         sqlrcur_prepareQuery( mysql_cur, "insert into kb values (
> :term_no, :order_no, :book_name, :publisher )" );
>
>         strcpy( body, "079" );
>         sqlrcur_inputBindString( mysql_cur, "term_no", body );
>         strcpy( body, "0002" );
>         sqlrcur_inputBindString( mysql_cur, "order_no", body );
>         strcpy( body, "abcd" );
>         sqlrcur_inputBindString( mysql_cur, "book_name", body );
>         strcpy( body, "zxvf" );
>         sqlrcur_inputBindString( mysql_cur, "publisher", body );
>         sqlrcur_executeQuery( mysql_cur );
>
>         sqlrcon_commit( mysql_con );
>         sqlrcur_free( mysql_cur );
>         sqlrcon_free( mysql_con );
> }
>
>
> but the result of inserting is incorrect , the "zxvf" was filled in all
> of columns, in other words, the last value of the inputBindString was
> inserted by the executeQuery repeatly.
>
> term_no order_no book_name publisher
> ====================================
> zxv     zxvf     zxvf      zxvf
>
> I could not find any mistakes in my source code, the result is so strange
> that I have no idea, anybody could help me ? thank you.
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>



-- 
Alfred J Fazio,
alf...@gm...

[Sqlrelay-discussion] the inputBindString posts the last value into the destination table repeatly

[Sha Li J. <lig...@sh...>]

hi all:

   I wroten a simple C program to test the inputBindString function of 
the sqlrelay , the destination table was created in a mysql database . 
its table  structure is  term_no varchar(3), order_no varchar(4), 
book_name varchar(120), publisher varchar(10), and the C program 
contains follow source code to insert values into the table,

#include <sqlrelay/sqlrclientwrapper.h>
#include <string.h>

int main(){

        char *body;

        sqlrcon mysql_con = sqlrcon_alloc("192.168.1.101",5900,"", 
"mydbuser","userpwd",0,1);
        sqlrcur mysql_cur = sqlrcur_alloc(mysql_con);

        sqlrcur_prepareQuery( mysql_cur, "insert into kb values ( 
:term_no, :order_no, :book_name, :publisher )" );

        strcpy( body, "079" );
        sqlrcur_inputBindString( mysql_cur, "term_no", body );
        strcpy( body, "0002" );
        sqlrcur_inputBindString( mysql_cur, "order_no", body );
        strcpy( body, "abcd" );
        sqlrcur_inputBindString( mysql_cur, "book_name", body );
        strcpy( body, "zxvf" );
        sqlrcur_inputBindString( mysql_cur, "publisher", body );
        sqlrcur_executeQuery( mysql_cur );

        sqlrcon_commit( mysql_con );
        sqlrcur_free( mysql_cur );
        sqlrcon_free( mysql_con );
}


but the result of inserting is incorrect , the "zxvf" was filled in all 
of columns, in other words, the last value of the inputBindString was 
inserted by the executeQuery repeatly.

term_no order_no book_name publisher
====================================
zxv     zxvf     zxvf      zxvf    

I could not find any mistakes in my source code, the result is so strange
that I have no idea, anybody could help me ? thank you.

[Sqlrelay-discussion] Question about cursor in sqlrelay (resolve)

[Huve L. <l....@fo...>]

Hello,

We find the probleme, in the procedure we use, we have made a "infinite"
loop and the procedure never finish.
But when we do that the listener dont accept new connexion and wait, so
it can't address new connexion to open pool.

Thanks a lot.
Loic 

[Sqlrelay-discussion] [Solved] Re: SQLrelay very slow on getting data

[Jens B. <je...@ch...>]

Hi all,

after we decreased the size of the ResultBuffer it kicks ass... ;)  

[root@outpost1 jens]# php comparePdoToSqlRelay2.php
SQL Relay: 7734 rows in 9.5583131313324 seconds (809.13858896794
rows/second)<br />
[root@outpost1
jens]#                                                                              

It's around three times faster as PDO...

PDO: 7733 rows in 31.92887711525 seconds (242.1945492191 rows/second)


ResultBuffer size is set to 500 ;)

Hf, 

Jens


Am Mittwoch, den 26.09.2007, 17:02 +0200 schrieb Jens Brey:
> Hi Dave,
> 
> now i have tested the setting of sqlrcur_setResultSetBufferSize(), but
> it not helps.
> 
> Result: 
> 
> PDO: 7733 rows in 31.92887711525 seconds (242.1945492191 rows/second)
> SQL Relay: 7733 rows in 212.47803497314 seconds (36.394350131191
> rows/second)
> 
> But the interesting part is, that if i limit the resultset to a value
> lower as around 1600, SQLrelay is faster.
> 
> PDO: 1600 rows in 5.829540014267 seconds (274.46419375872 rows/second)
> SQL Relay: 1600 rows in 5.2128088474274 seconds (306.93625007747
> rows/second)
> 
> I also checked out the tunning and the programming page, but i found no
> tip, that helps...
> 
> 
> Regards, 
> 
> Jens
> 
> <--- Script --->
> 
> /**
>  * SQL Relay
>  */
> $sqlRelayConnection =
> sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "debug",
> "debug", 0, 1);
> if (sqlrcon_ping($sqlRelayConnection)) {
>         $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);
> 
>         $sqlRelayLocations = new CList();
> 
>         $sqlRelayTime = microtime(true);
>         sqlrcur_setResultSetBufferSize($sqlRelayCursor, 10000);
>         sqlrcur_prepareQuery($sqlRelayCursor, $query);
> 
>         if (sqlrcur_executeQuery($sqlRelayCursor)) {
> 
>                 for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
> $row++) {
>                         $sqlRelayLocations->addElement(new
> CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
>                 }
> 
> 
>         }
>         $sqlRelayTime = microtime(true) - $sqlRelayTime;
>         echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
> ".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
> $sqlRelayTime)." rows/second)<br />\n";
> 
>         sqlrcur_free($sqlRelayCursor);
> }
> sqlrcon_free($sqlRelayConnection);
> 
> 
> <----- /Script ----->
> 
> 
> 
> 
> 
> Am Dienstag, den 25.09.2007, 22:39 -0400 schrieb David Muse:
> > That's pretty odd.  I wouldn't expect it to be slower.  Did you try
> > using sqlrcur_setResultSetBufferSize()?
> > 
> > See
> > http://sqlrelay.sourceforge.net/sqlrelay/programming/php.html#largeresultsets
> > for more info.
> > 
> > Offhand, that's the only thing I can think of that would cause slowness
> > like this.
> > 
> > Also, check out the tuning tips at:
> > http://sqlrelay.sourceforge.net/sqlrelay/tuning.html
> > Maybe one of them will help.
> > 
> > Dave
> > dav...@fi...
> > 
> > On Tue, 2007-09-25 at 18:04 +0200, Jens Brey wrote:
> > > Hi all,
> > > 
> > > i have installed sqlrelay on or development envireonment and made some
> > > performance tests. I knew, that sqlrelay is slower as PDO, because of
> > > the internal double copy of data. But so many times slower? 
> > > 
> > > Configuration: sqlrelay on pentium4 2,8GHz connected to a Oracle 10g RAC
> > > cluster. Simple select statement on a table with around 700MB data. 
> > > 
> > > Here is the result:
> > > PDO: 7733 rows in 27 seconds (286.40740740741 rows/second)
> > > SQL Relay: 7733 rows in 207 seconds (37.357487922705 rows/second)
> > > 
> > > The amount of data transferred is around 60 MB. 
> > > 
> > > I tried to play with fetchatonce and maxselectlistsize, but if i set
> > > fetchatonce to 100 and maxselectlistsize to 512, i couldn't establish a
> > > connection to the cluster.
> > > 
> > > 
> > > Connection Data:
> > > 
> > > <!-- Regular SQL Relay Instance -->
> > > <instance id="OMNIDUMP" port="9000" socket="/tmp/sqlr_OMNIDUMP.socket"
> > > dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
> > > growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
> > > runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
> > > handoff="pass" deniedips="" allowedips="" debug="none"
> > > maxquerysize="65536" maxstringbindvaluelength="4000"
> > > maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> > > listenertimeout="0" reloginatstart="false">
> > >                 <users>
> > >                         <user user="xxx" password="xxx"/>
> > >                 </users>
> > >                 <connections>
> > >                         <connection connectionid="OMNIDUMP1"
> > > string="user=xxx;password=xxx;oracle_sid=OMNIDUMP;oracle_home=INSTANT;nls_lang=GERMAN_GERMANY.WE8ISO8859P1;" metric="1" behindloadbalancer="no"/>
> > >                 </connections>
> > > </instance>
> > > 
> > > 
> > > SQL Relay Script:
> > > /**
> > >  * SQL Relay
> > >  */
> > > 
> > > $sqlRelayConnection =
> > > sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "xxx",
> > > "xxx", 0, 1);
> > > if (sqlrcon_ping($sqlRelayConnection)) {
> > >         $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);
> > > 
> > >         $sqlRelayLocations = new CList();
> > > 
> > >         $sqlRelayTime = time();
> > >         sqlrcur_prepareQuery($sqlRelayCursor, $query);
> > >         if (sqlrcur_executeQuery($sqlRelayCursor)) {
> > >                 for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
> > > $row++) {
> > >                         $sqlRelayLocations->addElement(new
> > > CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
> > >                 }
> > >         }
> > >         $sqlRelayTime = time() - $sqlRelayTime;
> > >         echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
> > > ".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
> > > $sqlRelayTime)." rows/second)<br />\n";
> > > 
> > >         sqlrcur_free($sqlRelayCursor);
> > > }
> > > sqlrcon_free($sqlRelayConnection);
> > > 
> > > 
> > > Anyone a idea?
> > > 
> > > 
> > > Regards,
> > > 
> > > Jens Brey
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > -------------------------------------------------------------------------
> > > This SF.net email is sponsored by: Microsoft
> > > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > > _______________________________________________
> > > Sqlrelay-discussion mailing list
> > > Sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > > 
> > > 
> > > _________________________________________________________________
> > > Need personalized email and website? Look no further. It's easy
> > > with Doteasy $0 Web Hosting! Learn more at www.doteasy.com
> > 
> > 
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] Question about cursor in sqlrelay

[Huve L. <l....@fo...>]

Hello David ,

I have another question about the cursors.
Is it a max value about the number of cursor used by connexion (I cant
use more 35).=20
When we load some web page with oracle procedure we get Listeners fork
and time out,  however I have a lot of available connexions but there
are not used and the listener fork and I must restart my pool.

Is it possible we use to many sqlrelay cursor (In oracle  no probleme).

exemple of what append :
3 Forks but when I do a sqlr-status I see X connexion not use. Each time
I want use the connexion a new fork appear and nothing occur. so I must
reload my configuration.

    0:04 sqlr-listener -id CRON_COMMANDE
-config /usr/local/sqlrelay/etc/sqlrelay.conf
    0:00  \_ sqlr-listener -id CRON_COMMANDE
-config /usr/local/sqlrelay/etc/sqlrelay.conf
    0:00  \_ sqlr-listener -id CRON_COMMANDE
-config /usr/local/sqlrelay/etc/sqlrelay.conf
    0:00  \_ sqlr-listener -id CRON_COMMANDE
-config /usr/local/sqlrelay/etc/sqlrelay.conf=20
    0:00 sqlr-connection-oracle8 -id CRON_COMMANDE -connectionid
CRON_COMMANDE -config /usr/local
217:47 sqlr-connection-oracle8 -id CRON_COMMANDE -connectionid
CRON_COMMANDE -config /usr/local
   0:00 sqlr-connection-oracle8 -id CRON_COMMANDE -connectionid
CRON_COMMANDE -config /usr/local
   0:00 sqlr-connection-oracle8 -id CRON_COMMANDE -connectionid
CRON_COMMANDE -config /usr/local


Open   Server Connections:  9
Opened Server Connections:  2986152
Open   Client Connections:  3
Opened Client Connections:  579
Open   Server Cursors:      150
Opened Server Cursors:      729
Times  New Cursor Used:     0
Times  Cursor Reused:       1135

Total  Queries:             2987347
Total  Errors:              2986155

Forked Listeners:           3

---------------------------------------------------------------------------=
--------

This part is about my last mail :=20

This is the display when I launch the procedure with php and my
connexion die (my last mail)
at one time I get a "(null)=3DErreur de segmentation" maybe it's at this
moment the connexion is kill.


randd:/var/www/PixPlace# php -e relay4loic.php
<hr><hr>SQL RELAY<hr><hr>Connecting to listener...
Inet socket: 192.168.1.69:9200
Authenticating with listener : devpixplace:pixplacedev
Waiting for auth success/failure...
No authentication error.
Must Not Reconnect.
Authenticating with connection : devpixplace:pixplacedev
Waiting for auth success/failure...
No authentication error.
Sending Query:
begin
PCK_COUNTRIES.P_GET_COUNTRY_LANGUAGE_TEST(:LANGUAGE,:ERROR,:CURSOR);
end;
Length: 79
Requesting a new cursor.
Sending 1 Input Bind Variables:
LANGUAGE(8:STRING)=3Dfr(2)
Sending Output Bind Variables:
ERROR
CURSOR
Send Column Info: yes
Skipping and Fetching
Skipping 0 rows
Fetching 0 rows
Checking For An Error... error!!!
Getting Error From Server
ORA-00942: Table ou vue inexistante
ORA-06512: =C3=A0 "DEVPIXPLACE.PCK_COUNTRIES", ligne 101
ORA-06512: =C3=A0 ligne 1
Getting Cursor ID...
Cursor ID: 0
Send Column Info: yes
Skipping and Fetching
Skipping 0 rows
Fetching 0 rows
Checking For An Error... none.
Getting Cursor ID...
Cursor ID: 0
Previous result set was not suspended.
Parsing Column Info
Actual row count: unknown
Affected row count: 0
Column count: 0
Receiving Output Bind Values:
        getting type...
        done getting type: 5
        INTEGER output bind
                done fetching
(null)=3D(null)
        getting type...
        done getting type: 4
        CURSOR output bind
                done fetching
(null)=3DErreur de segmentation


Thanks a lot.
Loic

[Sqlrelay-discussion] Instantclient 11g patch

[Jens B. <je...@ch...>]

Hi all,

i wrote a short patch, to use the Instantclient 11g for Oracle with
sqlrelay.

Apply the patch with:
patch -p1 < ../configure.patch

and set the new option:

--oracle-instantclient-version=11

Hf, 

Jens

Re: [Sqlrelay-discussion] SQLrelay very slow on getting data

[Jens B. <je...@ch...>]

Hi Dave,

now i have tested the setting of sqlrcur_setResultSetBufferSize(), but
it not helps.

Result: 

PDO: 7733 rows in 31.92887711525 seconds (242.1945492191 rows/second)
SQL Relay: 7733 rows in 212.47803497314 seconds (36.394350131191
rows/second)

But the interesting part is, that if i limit the resultset to a value
lower as around 1600, SQLrelay is faster.

PDO: 1600 rows in 5.829540014267 seconds (274.46419375872 rows/second)
SQL Relay: 1600 rows in 5.2128088474274 seconds (306.93625007747
rows/second)

I also checked out the tunning and the programming page, but i found no
tip, that helps...


Regards, 

Jens

<--- Script --->

/**
 * SQL Relay
 */
$sqlRelayConnection =
sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "map24",
"mappas", 0, 1);
if (sqlrcon_ping($sqlRelayConnection)) {
        $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);

        $sqlRelayLocations = new CList();

        $sqlRelayTime = microtime(true);
        sqlrcur_setResultSetBufferSize($sqlRelayCursor, 10000);
        sqlrcur_prepareQuery($sqlRelayCursor, $query);

        if (sqlrcur_executeQuery($sqlRelayCursor)) {

                for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
$row++) {
                        $sqlRelayLocations->addElement(new
CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
                }


        }
        $sqlRelayTime = microtime(true) - $sqlRelayTime;
        echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
$sqlRelayTime)." rows/second)<br />\n";

        sqlrcur_free($sqlRelayCursor);
}
sqlrcon_free($sqlRelayConnection);


<----- /Script ----->





Am Dienstag, den 25.09.2007, 22:39 -0400 schrieb David Muse:
> That's pretty odd.  I wouldn't expect it to be slower.  Did you try
> using sqlrcur_setResultSetBufferSize()?
> 
> See
> http://sqlrelay.sourceforge.net/sqlrelay/programming/php.html#largeresultsets
> for more info.
> 
> Offhand, that's the only thing I can think of that would cause slowness
> like this.
> 
> Also, check out the tuning tips at:
> http://sqlrelay.sourceforge.net/sqlrelay/tuning.html
> Maybe one of them will help.
> 
> Dave
> dav...@fi...
> 
> On Tue, 2007-09-25 at 18:04 +0200, Jens Brey wrote:
> > Hi all,
> > 
> > i have installed sqlrelay on or development envireonment and made some
> > performance tests. I knew, that sqlrelay is slower as PDO, because of
> > the internal double copy of data. But so many times slower? 
> > 
> > Configuration: sqlrelay on pentium4 2,8GHz connected to a Oracle 10g RAC
> > cluster. Simple select statement on a table with around 700MB data. 
> > 
> > Here is the result:
> > PDO: 7733 rows in 27 seconds (286.40740740741 rows/second)
> > SQL Relay: 7733 rows in 207 seconds (37.357487922705 rows/second)
> > 
> > The amount of data transferred is around 60 MB. 
> > 
> > I tried to play with fetchatonce and maxselectlistsize, but if i set
> > fetchatonce to 100 and maxselectlistsize to 512, i couldn't establish a
> > connection to the cluster.
> > 
> > 
> > Connection Data:
> > 
> > <!-- Regular SQL Relay Instance -->
> > <instance id="OMNIDUMP" port="9000" socket="/tmp/sqlr_OMNIDUMP.socket"
> > dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
> > growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
> > runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
> > handoff="pass" deniedips="" allowedips="" debug="none"
> > maxquerysize="65536" maxstringbindvaluelength="4000"
> > maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> > listenertimeout="0" reloginatstart="false">
> >                 <users>
> >                         <user user="xxx" password="xxx"/>
> >                 </users>
> >                 <connections>
> >                         <connection connectionid="OMNIDUMP1"
> > string="user=xxx;password=xxx;oracle_sid=OMNIDUMP;oracle_home=INSTANT;nls_lang=GERMAN_GERMANY.WE8ISO8859P1;" metric="1" behindloadbalancer="no"/>
> >                 </connections>
> > </instance>
> > 
> > 
> > SQL Relay Script:
> > /**
> >  * SQL Relay
> >  */
> > 
> > $sqlRelayConnection =
> > sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "xxx",
> > "xxx", 0, 1);
> > if (sqlrcon_ping($sqlRelayConnection)) {
> >         $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);
> > 
> >         $sqlRelayLocations = new CList();
> > 
> >         $sqlRelayTime = time();
> >         sqlrcur_prepareQuery($sqlRelayCursor, $query);
> >         if (sqlrcur_executeQuery($sqlRelayCursor)) {
> >                 for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
> > $row++) {
> >                         $sqlRelayLocations->addElement(new
> > CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
> >                 }
> >         }
> >         $sqlRelayTime = time() - $sqlRelayTime;
> >         echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
> > ".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
> > $sqlRelayTime)." rows/second)<br />\n";
> > 
> >         sqlrcur_free($sqlRelayCursor);
> > }
> > sqlrcon_free($sqlRelayConnection);
> > 
> > 
> > Anyone a idea?
> > 
> > 
> > Regards,
> > 
> > Jens Brey
> > 
> > 
> > 
> > 
> > 
> > 
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > 
> > 
> > _________________________________________________________________
> > Need personalized email and website? Look no further. It's easy
> > with Doteasy $0 Web Hosting! Learn more at www.doteasy.com
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

Re: [Sqlrelay-discussion] SQLrelay very slow on getting data

[David M. <dav...@fi...>]

That's pretty odd.  I wouldn't expect it to be slower.  Did you try
using sqlrcur_setResultSetBufferSize()?

See
http://sqlrelay.sourceforge.net/sqlrelay/programming/php.html#largeresultsets
for more info.

Offhand, that's the only thing I can think of that would cause slowness
like this.

Also, check out the tuning tips at:
http://sqlrelay.sourceforge.net/sqlrelay/tuning.html
Maybe one of them will help.

Dave
dav...@fi...

On Tue, 2007-09-25 at 18:04 +0200, Jens Brey wrote:
> Hi all,
> 
> i have installed sqlrelay on or development envireonment and made some
> performance tests. I knew, that sqlrelay is slower as PDO, because of
> the internal double copy of data. But so many times slower? 
> 
> Configuration: sqlrelay on pentium4 2,8GHz connected to a Oracle 10g RAC
> cluster. Simple select statement on a table with around 700MB data. 
> 
> Here is the result:
> PDO: 7733 rows in 27 seconds (286.40740740741 rows/second)
> SQL Relay: 7733 rows in 207 seconds (37.357487922705 rows/second)
> 
> The amount of data transferred is around 60 MB. 
> 
> I tried to play with fetchatonce and maxselectlistsize, but if i set
> fetchatonce to 100 and maxselectlistsize to 512, i couldn't establish a
> connection to the cluster.
> 
> 
> Connection Data:
> 
> <!-- Regular SQL Relay Instance -->
> <instance id="OMNIDUMP" port="9000" socket="/tmp/sqlr_OMNIDUMP.socket"
> dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
> growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
> runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
> handoff="pass" deniedips="" allowedips="" debug="none"
> maxquerysize="65536" maxstringbindvaluelength="4000"
> maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> listenertimeout="0" reloginatstart="false">
>                 <users>
>                         <user user="xxx" password="xxx"/>
>                 </users>
>                 <connections>
>                         <connection connectionid="OMNIDUMP1"
> string="user=xxx;password=xxx;oracle_sid=OMNIDUMP;oracle_home=INSTANT;nls_lang=GERMAN_GERMANY.WE8ISO8859P1;" metric="1" behindloadbalancer="no"/>
>                 </connections>
> </instance>
> 
> 
> SQL Relay Script:
> /**
>  * SQL Relay
>  */
> 
> $sqlRelayConnection =
> sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "xxx",
> "xxx", 0, 1);
> if (sqlrcon_ping($sqlRelayConnection)) {
>         $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);
> 
>         $sqlRelayLocations = new CList();
> 
>         $sqlRelayTime = time();
>         sqlrcur_prepareQuery($sqlRelayCursor, $query);
>         if (sqlrcur_executeQuery($sqlRelayCursor)) {
>                 for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
> $row++) {
>                         $sqlRelayLocations->addElement(new
> CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
>                 }
>         }
>         $sqlRelayTime = time() - $sqlRelayTime;
>         echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
> ".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
> $sqlRelayTime)." rows/second)<br />\n";
> 
>         sqlrcur_free($sqlRelayCursor);
> }
> sqlrcon_free($sqlRelayConnection);
> 
> 
> Anyone a idea?
> 
> 
> Regards,
> 
> Jens Brey
> 
> 
> 
> 
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> _________________________________________________________________
> Need personalized email and website? Look no further. It's easy
> with Doteasy $0 Web Hosting! Learn more at www.doteasy.com

Re: [Sqlrelay-discussion] bad pl/sql kill sqlrelay connexion

[David M. <dav...@fi...>]

Looks like a bug.  I'll see if I can reproduce this and get a fix out.

Dave
dav...@fi...

On Tue, 2007-09-18 at 17:03 +0200, Huve Loic wrote:
> Hello,
> 
> We have a strange problem since we use oracle pl/sql and sqlrelay. 
> That arrived when we debug proc or test new proc, sqlrelay connexions
> are kill and disappear so we must restart it.
> 
> Do you think there is something to do to avoid this problem
> 
> I give you the connexion log and the error I find.
> 
> Thank's a lot
> Loic
> 
> 
> 
> error : 
> - getting command failed: client sent bad command or timed out
> 
> connexion log :
> 
> begin
> PCK_LEAD_INFO.P_GET_PRICE_ONLINE(  :FUPID , :SITE_ID , :COUNTRY_CODE , :CURSEUR , :ERROR  ); end;
> getting query succeeded
> getting input binds...
> :FUPID
> STRING
> 00020905
> :SITE_ID
> INTEGER
> 1
> :COUNTRY_CODE
> STRING
> fr
> done getting input binds
> getting output binds...
> :CURSEUR
> CURSOR
> found a free cursor:
> 1
> :ERROR
> INTEGER
> done getting output binds
> getting send column info...
> don't send column info
> done getting send column info...
> processing query...
> preparing/executing...
> commit or rollback check...
> commit or rollback needed
> done with commit or rollback check
> processing query failed
> done processing query
> handling error...
> returning error...
> failed to handle query: error
> done returning error
> done handling error...
> getting command...
> done getting command
> getting a cursor...
> done getting a cursor
> fetch from bind cursor
> handling query...
> getting send column info...
> send column info
> done getting send column info...
> processing query...
> bind cursor...
> commit or rollback check...
> commit or rollback needed
> done with commit or rollback check
> processing query succeeded
> done processing query
> returning result set header...
> returning row counts...
> sending row counts...
> actual rows unknown
> affected rows: 0
> done sending row counts
> done returning row counts
> column info will be sent
> returning column counts...
> done returning column counts
> sending column type format...
> id's
> done sending column type format
> returning column info...
> done returning column info
> returning output bind values
> 2
> 2
> 0:CURSOR:
> 1
> 1:INTEGER:
> 0
> done returning output bind values
> done returning result set header
> handle query succeeded
> returning result set data...
> done returning result set data
> getting command...
> done getting command
> getting a cursor...
> done getting a cursor
> abort result set
> getting command...
> getting command failed: client sent bad command or timed out
> <========== and my connexion is kill.
> end session
> ending session...
> aborting all busy cursors...
> 1
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] In SQLRelay for FreeTDS, sqlrconnction->ping() makes following queries fail when restarting SQL Server

[sz l. <shu...@gm...>]

Hi,

We are using sqlrelay 0.37 to connect to SQL Server via FreeTDS in Linux
box. We want to check whether the SQL Server is down or not using
sqlrconnction->ping(). However, it seems that it does not work as expected:

(1)   Restart the sqlrelay, make sure it works.

(2)   Stop the SQL service.

(3)   In a program, call sqlrconnction->ping() and then exit.

(4)   Restart the SQL service.

(5)   %query =96id instanceName "select 1"

It gives out error message:

"Error: Attempt to initiate a new SQL Server operation with results
pending."



In the sqlrelay/src/connections/freetds/freetdsconnection.C, the

bool freetdscursor::executeQuery(const char* query, unit32_t length, bool
execute)

is used to send out the query using freetds: ct_send(cmd).

When there are some errors, it would call cleanUpData to clean the result
set.



However, the discardResults() depends on the data member "results" which
would not got initialized until the ct_send is successful. If we initialize=
:

results=3DCS_SUCCEED;

before calling ct_send(cmd), the

 %query =96id instanceName "select 1"

would work.



Does this  fix make sense?



Thanks.

[Sqlrelay-discussion] SQLrelay very slow on getting data

[Jens B. <je...@ch...>]

Hi all,

i have installed sqlrelay on or development envireonment and made some
performance tests. I knew, that sqlrelay is slower as PDO, because of
the internal double copy of data. But so many times slower? 

Configuration: sqlrelay on pentium4 2,8GHz connected to a Oracle 10g RAC
cluster. Simple select statement on a table with around 700MB data. 

Here is the result:
PDO: 7733 rows in 27 seconds (286.40740740741 rows/second)
SQL Relay: 7733 rows in 207 seconds (37.357487922705 rows/second)

The amount of data transferred is around 60 MB. 

I tried to play with fetchatonce and maxselectlistsize, but if i set
fetchatonce to 100 and maxselectlistsize to 512, i couldn't establish a
connection to the cluster.


Connection Data:

<!-- Regular SQL Relay Instance -->
<instance id="OMNIDUMP" port="9000" socket="/tmp/sqlr_OMNIDUMP.socket"
dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
handoff="pass" deniedips="" allowedips="" debug="none"
maxquerysize="65536" maxstringbindvaluelength="4000"
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
listenertimeout="0" reloginatstart="false">
                <users>
                        <user user="xxx" password="xxx"/>
                </users>
                <connections>
                        <connection connectionid="OMNIDUMP1"
string="user=xxx;password=xxx;oracle_sid=OMNIDUMP;oracle_home=INSTANT;nls_lang=GERMAN_GERMANY.WE8ISO8859P1;" metric="1" behindloadbalancer="no"/>
                </connections>
</instance>


SQL Relay Script:
/**
 * SQL Relay
 */

$sqlRelayConnection =
sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "xxx",
"xxx", 0, 1);
if (sqlrcon_ping($sqlRelayConnection)) {
        $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);

        $sqlRelayLocations = new CList();

        $sqlRelayTime = time();
        sqlrcur_prepareQuery($sqlRelayCursor, $query);
        if (sqlrcur_executeQuery($sqlRelayCursor)) {
                for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
$row++) {
                        $sqlRelayLocations->addElement(new
CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
                }
        }
        $sqlRelayTime = time() - $sqlRelayTime;
        echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
$sqlRelayTime)." rows/second)<br />\n";

        sqlrcur_free($sqlRelayCursor);
}
sqlrcon_free($sqlRelayConnection);


Anyone a idea?


Regards,

Jens Brey

Re: [Sqlrelay-discussion] Problem with binary data in Oracle VARCHAR2 field using PHP

[David M. <dav...@fi...>]

Are the passwords getting truncated?

If so, they probably have NULL's in them.  sqlrcur_getField() will
return the data, but if it has a NULL in it somewhere, then if you use
string operations on it (such as getting the length), they will all bail
when they hit the first NULL.

There is a sqlrcur_getFieldLength() method that you should use when
dealing with binary data that should return the correct length.

That could be related to the issue you're having.

Another possibility is that the field may be getting truncated on insert
for a similar reason.  The NULL's may need to be escaped in the query,
or you might want to use bind variables.

If neither of these solutions seem applicable to your problem, then I'll
need to know some more details to be able to help further.

David Muse
dav...@fi...

On Fri, 2007-09-21 at 16:49 +0000, Daniel Smith wrote:
> We've been going back and forth with our IT people about using SQL
> Relay for a while now and in trying to convince them we've ported our
> implementation of PHP's oci8 pecl extension to sqlrelay.  The port
> took 2 days for one guy (included setting up a sqlrelay box) and is
> going quite well, but we're having one problem.  We store certain
> data, such as passwords, encrypted in the database and we can't seem
> to get it to retrieve the data properly.  I'll admit that our
> encryption/decryption functionality is somewhat of a blackbox, but we
> switch back and forth between the libraries and the only difference
> now is sqlrelay vs. oci8 extension.  Has anyone had any experience
> with this?
> 
>  
> 
> Thanks,
> 
> Daniel Smith 
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] Problem with binary data in Oracle VARCHAR2 field using PHP

[Daniel S. <sql...@bo...>]

  

 We've been going back and forth with our IT people about using SQL
Relay for a while now and in trying to convince them we've ported our
implementation of PHP's oci8 pecl extension to sqlrelay.  The port
took 2 days for one guy (included setting up a sqlrelay box) and is
going quite well, but we're having one problem.  We store certain
data, such as passwords, encrypted in the database and we can't seem
to get it to retrieve the data properly.  I'll admit that our
encryption/decryption functionality is somewhat of a blackbox, but we
switch back and forth between the libraries and the only difference
now is sqlrelay vs. oci8 extension.  Has anyone had any experience
with this?  
 Thanks,  

 Daniel Smith