Menu
▾
▴
Re: [phpesp-dev] ODBC portDear Charlie,
|
From: Charlie S. <Sm...@ld...> - 2003-10-25 17:46:47
|
Thanks for your reply Stephan. Please keep postings copied to the group if=
=
possible.
I believe it in our best interest to work together as well.
Calling for help - W O U L D A N Y O N E B E INTERESTED =
IN ASSISTING WITH THE
PORT OF MYSQL TO PEAR DB? If so, please respond, and I'll try and divy out=
=
assignments.
Stephan, is the odbc stuff able to be abstracted to PEAR DB?
>>> "Stefan Champailler" <sch...@ea...> 10/23/03 03:38PM >>>
Dear Charlie,
Thanks a lot for your long comment. I thought people had lost their interes=
t=
=20
in working together, that's why I went my way... Another reason, a bit more=
=20
convincing I think is that I have modified the code quite a lot and I'm now=
=20
quite away from the original. However, Ill be happy to contribute in anyway=
=
I=20
can. As soon as I see the end of the project, Ill show some "diffs" of my=20
code against the base.
Anyway, here's what I'm doing so far. As I said previously, I'm converting =
the=20
code to something that makes the least possible use of row counts. Parts of=
=20
the code can be rewritten to avoid it (and we actually increase efficiency,=
=20
but I'll assume it is pointless here). I'm almost done. The only thing =
that's=20
left to do is to get rid for "insert_id" stuff and alike. I'll do that by=20
simulating Oracle sequences in the beginning and then, once I move to =
Oracle,=20
remove the simulation. Here are the functions I have created (might be =
bugged=20
as I still don't have made a full regression test). I use phpDocumentor for=
=20
documenting (a really cool tool).
On the counting issue, I also prefer to use "SELECT COUNT(*) ..." rather =
than=20
=66etching the whole information "just" for coutning it. I try to push as =
much=20
as the code into the DB, without using stored procedure though (because I=20
don't know them very much, because it's not portable).
On another front, I'm also looking at some of the database constructs. I =
think=20
that some of the auto_increment are not necessary as well as sompe of the=20
"id" fields. But I prefer to look into that before posting misleading=20
information.
So, Charlie, I think we can just say that we have taken two quite different=
=20
directions, but what I suggest is that we share our experience here so that=
=20
other can eventually read it. I'll continue to post my progress on the port=
=20
as well as some of my interrogations..
cheers,
stF
/** Make a query against the ODBC persistent connection.
* This is equivalent to calling odbc_prepare/execute.
* A lot of error reporting is done if necessary.
* @return mixed false in case of error. Results ressource if successfull.=
=
*/
=66unction odbc_query( $sql) {
global $ESPCONFIG;
if( !empty( $ESPCONFIG['odbc_cid'])) {
$psql =3D odbc_prepare( $ESPCONFIG['odbc_cid'], $sql);
error_log("PSQL is $psql : $sql");
if( $psql !=3D false) {
$res =3D odbc_execute( $psql);
if( $res) {
error_log("Executed $sql =3D $sql"); // debug
return $psql;
} else {
odbc_free_result( $psql);
error_log("odbc_query() : Unable to execute the following=20
*prepared* SQL :");
error_log("odbc_query() : $sql");
error_log("odbc_query() : ODBC reported : =
".odbc_errormsg());
return false;
}
} else {
error_log("odbc_query() : Unable to prepare the following SQL =
:");
error_log("odbc_query() : $sql");
error_log("odbc_query() : ODBC reported : ".odbc_errormsg());
return false;
}
} else {
error_log("odbc_query() : Ooops tried to query without a proper=20
connection to the DB.");
return false;
}
}
/** Same semantic as mysql_fetch_assoc()
* A row is fetched.
*
* @param resource Query Id.
* @return mixed False if no row are available. An associative
* array with the row fields in it. */
=66unction odbc_fetch_assoc( $qid) {
if( ($row =3D odbc_fetch_row( $qid))
&& odbc_num_fields( $qid) > 0)
return odbc_extract_assoc( $qid);
else
return false;
}
/** Extracts the data in a fetched row into an associative array.
* Each field of the row gets an entry in the associative array.
* No test has been done for field sharing the same name.
*
* @param resource Query Id.
* @return An associative array with all the fields of the row
* or an empty array if there is no field. */
=66unction odbc_extract_assoc( $qid) {
$a =3D array();
for( $i=3D1; $i <=3D odbc_num_fields( $qid); $i++)
$a[ odbc_field_name($qid, $i) ] =3D odbc_result( $qid, $i);
return $a;
}
/** Same semantic as mysql_fetch_array()
* A row is fetched.
*
* @param resource Query Id.
* @return mixed False if no row are available. An indexed
* array with the row fields in it. */
=66unction odbc_fetch_row_array( $qid) {
if( ($row =3D odbc_fetch_row( $qid) && odbc_num_fields( $qid) > 0))
return odbc_extract_array( $qid);
else
return false;
}
/** Extracts the data in a fetched row into an indexed array.
* Each field of the row gets an entry in the associative array,
* in the order it appears in the row. Array's indexes are zero based.
*
* @param resource Query Id.
* @return An indexed array with all the fields of the row
* or an empty array if there is no field. */
=66unction odbc_extract_array( $qid) {
$a =3D array();
for( $i=3D1; $i <=3D odbc_num_fields( $qid); $i++)
$a[ $i - 1 ] =3D odbc_result( $qid, $i);
return $a;
}
=66unction odbc_get_first_cell( $qid) {
if( odbc_fetch_row( $qid) && odbc_num_fields( $qid) > 0)
return odbc_result( $qid, 1);
else
return false;
}
=66unction odbc_get_first_cell_into( $qid, &$value) {
if( ($row =3D odbc_fetch_row( $qid))
&& odbc_num_fields( $qid) > 0) {
$value =3D odbc_result( $qid, 1);
return true;
} else
return false;
}
> Stephan,
> The mysql_num_rows fucntion as well as the OCIRowCount only return the
> number of rows fetched in the query so far. So, as you've found, all the
> rows of a particular query have to be fetched for the numrows function to
> return what most people would want to see - a number of the rows matching=
=
a
> specific query.
>
> I was wondering how much of this you want to work together on. If =
you'd
> asked earlier, I would have given you the code that follows. Hopefully, =
we
> can see the value of working together on this.
>
> I'd like a count and names of individuals that would like to
> participate. I believe there was a Jeremy Buckman that showed some
> interest in this. Also, I've asked to be put on the developer's list for
> this project, with no reply. Is there an objection to this proposal.
>
> Here is the code I wrote to return the number of rows matching a =
particular
> query: Hope this helps. I've also written oracle queries to match all of
> the mysql queries in phpesp. It's really just a matter of unit testing
> now. If we could have some volunteers to do this, that would be great. =
=20
> Also, volunteering means, locating the appropriate PEAR db call, and doing
> the code to move from the mysql/Oracle calls to the PEAR equivalent if it
> exists. I really dont want to offent anyone here, and I've not been able
> to pay much attention to this lately, as my other work responsibilities
> have caused me to put this on the back burner. However, I believe that
> with some effort on all of our parts and - working together - we could
> proceed to the PEAR port. However, I'm so busy, It really doesn't bother
> me to wait, right now. Just maybe could make life simpler if we share.
>
> Here is the oracle function for returning num rows:
> # Function: oracleNumRows
> # Author: Charlie Smith
> # Date: 5/30/03
> # Description:
> # Get number of rows matching query.
> #
> # Input Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $query Oracle query/statement
> # $connected resource identifier
> #
> # Return Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $mcount number of rows in $query
> #
> function oracleNumRows($query, $connected) {
>
> $cwsStmt =3D oracleExecuteQuery($query, $connected);
> while($result =3D @oracleFetchArray($cwsStmt)) {
> }
> $mCount =3D OCIRowCount($cwsStmt);
> return $mCount;
> }
>
> # Function: oracleFetchArray
> # Author: Charlie Smith
> # Date: 5/30/03
> # Description:
> # Fetch record from table.
> #
> # Input Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $stmt prepared oracle query
> #
> # Return Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $row array of values for record retrieved
> #
> # NOTE: The known flags to OCIFetchInto are:
> # OCI_ASSOC Return an associative array.
> # OCI_NUM Return an numbered array starting with zero. (DEFAULT)
> # OCI_RETURN_NULLS Return empty columns.
> # OCI_RETURN_LOBS Return the value of a LOB instead of the descriptor.
> #
> function oracleFetchArray($stmt) {
>
> @OCIFetchInto ($stmt, $row, OCI_ASSOC);
> # In case you want to return just one field value, uncommand
> # and add parameter for name of field.
> # return $row[$alias_name];
> return $row;
> }
>
> # Function: oracleExecuteQuery
> # Author: Charlie Smith
> # Date: 5/30/03
> # Description:
> # Executes query on connected resource.
> #
> # Calling routines: oracleNumRows
> #
> # Input Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $query Oracle query/statement
> # $connected resource identifier
> #
> # Return Values Description
> # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> # $stmt Used statement asscociated with parsed/prepared
> resource.
>
> #
> function oracleExecuteQuery($query, $connected) {
> global $ORACLE_USER, $ORACLE_PASS, $ORACLE_SID;
>
> if(!$connected) {
> $connected =3D @OCIPLogon($ORACLE_USER, $ORACLE_PASS, $ORACLE_SID);
>
> if($error =3D OCIError()) {
> die("<font color=3Dred>ERROR!! Couldn't connect to server!</font>");
> }
> }
>
> $stmt =3D @OCIParse($connected, $query);
> if($error =3D OCIError()) {
> die("<font color=3Dred>ERROR!! Statement syntax error!
> $error[message]</font>" );
> }
> @OCIExecute($stmt);
> if($error =3D OCIError($stmt)) {
> die("<font color=3Dred>ERROR!! Could not execute statement =
query:$query!
> $erro r[message] </font>");
> }
>
> return $stmt;
> }
>
> >>> "Stefan Champailler" <sch...@ea...> 10/22/03 01:55PM >>>
>
> More on the ODBC port...
>
> Since we can't rely on the odbc_num_rows function for counting the result
> of select statement, I had to get rid of them all. It took me 1.5 days so
> far. Now I have to see where I've broken stuff.
>
> I stumbled across a piece of code that is very strange as well. It's in
> order.inc, the part of the code that actually change the order of a
> question. I've a feeling that the SQL statement used is not right. But,
> somehow, everything seems to work (I didn't investigate completely). So
> either its wrong, either is quite clever (I need three SQL operation to =
get
> the same result). So if someone can explain me how this part of the code =
is
> working, it'll be helpful.
>
> stF
>
> > Yes, you read well, ODBC port :)
> >
> > I'm doing it like that because I have to reduce the risks... Since it's
> > gonna work on Oracle, I'll share my experience with you. Right now, I'm
> > converting all the mysal_ statements to odbc_ statements. It's not that
> > simple. Especially the mysql_num_rows which doesn' translate easily into
> > odbc_num_rows (this one is not guaranteed to work on every ODBC
> > driver...).
> >
> > More on that later.
> >
> > stF
> >
> >
> >
> > -------------------------------------------------------
> > This SF.net email is sponsored by OSDN developer relations
> > Here's your chance to show off your extensive product knowledge
> > We want to know what you know. Tell us and you have a chance to win $100
> > http://www.zoomerang.com/survey.zgi?HRPT1X3RYQNC5V4MLNSV3E54=20
> > _______________________________________________
> > phpESP-devel mailing list
> > php...@li...=20
> > https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20
>
> -------------------------------------------------------
> This SF.net email is sponsored by OSDN developer relations
> Here's your chance to show off your extensive product knowledge
> We want to know what you know. Tell us and you have a chance to win $100
> http://www.zoomerang.com/survey.zgi?HRPT1X3RYQNC5V4MLNSV3E54=20
> _______________________________________________
> phpESP-devel mailing list
> php...@li...=20
> https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20
>
>
> =
---------------------------------------------------------------------------
>--- This message may contain confidential information, and is intended only
> for the use of the individual(s) to whom it is addressed.
>
>
> =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>=3D=3D=3D
---------------------------------------------------------------------------=
---
This message may contain confidential information, and is intended only for=
=
the use of the individual(s) to whom it is addressed.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
|
