Re: [Sqlrelay-discussion] Question on Suspending and Resuming SqlRelay Sessions in PHP

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

On Fri, 2005-06-10 at 19:45 -0400, Himsl, Jason wrote:
> 
> Hello,
> 
> I have run into an interesting series of problems implementing
> sqlrelay’s suspend/resume functions.
> 
> My development environment consists of the following: 
> 1) A Debian server running Apache with PHP 4.3.4 
> 2) The same Debian server running sqlrelay (4 pooled connections to
> the DB) 
> 3) A Solaris server running Oracle 9.2
> 
> 
> Scenario:
> 
> I’m trying to create a dynamic page where users can run an Oracle
> procedure and view an ordered subset of the records returned on a per
> page basis. Users would be shown (x) number of records and be able to
> click a link which would return the next (x) set of records from the
> original RecordSet. 
> 
> SqlRelay's suspend/resume functionality supposedly allows for the same
> RecordSet session to be suspended and resumed across pages without
> having to run the DB procedure multiple times. The sqlrelay
> documentation gives a small example on how to suspend and resume a
> simple insert session. Basically you store the connection vars (RSID,
> Port, Socket), call the suspend functions and clear your resources. On
> the next page you call the resume functions passing the appropriate
> RecordSetID, Port and Socket values to retrieve the suspended
> Session's RecordSet. The example was quite vague on how to actually
> pass the appropriate RecordSetID, Port and Socket values though. The
> example also referenced incorrect function names and parameters to
> retrieve the port and socket from the current connection. 
> 
> I thumbed through the API and found the correct function names for
> retrieving the Port and Socket information. However, I have found the
> return values for both functions to be horribly inconsistent. In most
> instances calling the 'sqlrcon_getConnectionSocket' function will
> segfault Apache, display garbage characters or even display text
> values from the RecordSet. Also calling 'sqlrcon_getConnectionPort'
> almost never returns a valid port number and a lot of the time it only
> returns 0. Keep in mind these are values pulled from a working
> sqlrelay connection which successfully queries the database and
> returns a valid RecordSet. I am bewildered as to why the
> 'sqlrcon_getConnectionSocket' function is returning anything other
> then null or empty string. Since sqlrelay runs on a separate server
> than the database, the sqlrelay connections should only rely on the
> hostname and port number. It should not be using Unix Sockets at all.
> I'm assuming that the connection session we are suspending is the
> connection between sqlrelay and the Database. Please correct me if I
> am wrong in this assumption.
> 
> I have a slightly different scenario then what was given in the
> sqlrelay example. I’m calling an Oracle procedure which returns a
> refcursor that I bind a sqlrelay cursor to too. After some
> experimenting I have suspended and resumed a RecordSet within the same
> page call. However, once I attempted to split the Suspend and Resume
> functions across pages I get nothing but 'Unknown errors' from the
> debugger and Segfaults from Apache.  I believe I have narrowed the
> problem down to the sqlrelay connection object. This all comes back to
> the 'sqlrcon_getConnectionPort' and 'sqlrcon_getConnectionSocket'
> functions.
> 
> Passing the RecordSetID and Connection Port to another page in PHP is
> trivial since they are simple value types. The Connection Socket is
> another issue though. PHP considers Unix Sockets to be resources.
> Resources can not be serialized in PHP and therefore can not be stored
> in a session or passed as a value to the GET or POST vars. 
> 
> 
> Here are my questions:
> 
> Why do I see or what would cause so many inconsistencies in the values
> returned by the 'sqlrcon_getConnectionPort' and
> 'sqlrcon_getConnectionSocket' functions?
> 
> How do I ensure I get either a valid Port or Socket?

I suspect that there's a bug.  I have a hunch what it might be, so I'll
take a look into it and let you know what I find.

In your scenario, you should be able to call sqlrcur_getResultSetId(),
sqlrcur_suspendResultSet(), sqlrcon_suspendSession(), then
sqlrcon_getConnectionPort() or sqlrcon_getConnectionSocket() and they
should return valid values.  Then later, on a different page, you should
be able to call sqlrcon_resumeSession() and pass it the port/socket and
then call sqlrcur_resumeResultSet() and pass it the id.

You also need to call sqlrcur_setResultSetBufferSize() and use a buffer
size equal to the number of rows that you're displaying on each page.
By default, the SQL Relay client fetches all rows of the result set and
it's result set buffer gets deleted when the client-side cursor is
deleted, which will happen when the page is done executing.  The new
client-side cursor that will get created on the next page to resume the
result set won't be able to re-fetch the rows that were already fetched
by the cursor on the previous page.

> 
> How do I effectively pass the Socket information from one PHP page to
> another?

sqlrcon_getConnectionPort() should return a number and
sqlrcon_getConnectionSocket() should return a string.  You can pass them
as POST or GET variables to the next page or store them in a cookie, or
if you have some kind of session record, you could put them there.  The
Port and Socket aren't actual sockets, the Port is the port number that
the sqlr-connection daemon is listening on for a client to resume the
session and the Socket is the name of the unix socket that the sqlr-
connnection daemon is listening on for a client to resume the session.

Dave
dav...@fi...

> 
> 
> 
> 
> J.D.H.
> 
> 
> 
>