RE: [PHP-DB] Unable to Login to Oracle 9i-R2 Database Using PHP 5.2.5
Adam, Have you tried oci_pconnect() instead of ocilogon()? Also, I have encountered some issues on Win32 with the instant client and PHP5. Try installing the full client and testing again. Neil Morgan -Original Message- From: Fortuno, Adam [mailto:[EMAIL PROTECTED] Sent: 24 November 2008 21:40 To: php-db@lists.php.net Subject: [PHP-DB] Unable to Login to Oracle 9i-R2 Database Using PHP 5.2.5 All: I'm running PHP in ISAPI mode with IIS accessing (or attempting to access) an Oracle 9i database on a RedHat box. Here are the versions of everything: Web Server: IIS v5.1 PHP: 5.2.5 Database: ORACLE v9i-R2 I am attempting to load a page with the following code: //Database credentials $username = MyUser; $passwd = MyPassword; $db=DBName; //Return the database connection OCILogon($username, $passwd, $db); I receive an error stating, Fatal error: Call to undefined function OCILogon() in C:\Documents and Settings\afortuno\My Documents\Dev\DBAIntranet\ghr_resources\transaction_report\adam.php on line 7 I'm able to use SQL Plus to login to the database I'm interested in. This gives me the impression that my credentials are not the problem; however, the plumbing between PHP and Oracle is the culprit. I initially had problems loading the OCI extension DLL. I resolved the issue by installing the Oracle 10g instant client. Here are the steps I followed as part of that process: (1) Download Oracle Instant Client Package Basic (v10.2.0.4) for Win32 http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/ winsoft.html (2) Unzip the contents downloaded zip-file. (3) Move the folder to the C:\Oracle directory. (4) Add the new folder (C:\oracle\ora102ic) to the PATH. (5) Restart IIS (e.g., C:\ iisreset). The error message couldn't be more vague, and I'm not experienced enough with Oracle, IIS, or PHP to know where to turn for more insight. Any help would be appreciated. Cheers, Adam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multiple Update SQL Statements Execution
Hi, Thanks for the replies, guys. I could finally come up with the actual code to do multiple updates. Just like some of you said, I had to name my index variables for it to distinguish between different elements. for($i = 1;$i = $total; $i++){ $a = $_POST['a'.$i]; $b = $_POST['b'.$i]; $c = $_POST['c'.$i]; $d = $_POST['d'.$i]; $e = $_POST['e'.$i]; $sql = UPDATE table SET market='$a',IM_accept='$b', IM_defer='$c', CR_accept='$d', FC_accept='$e' WHERE scenario_id='$scenario_id'; echo update_$i$sql/update_$i; $result2=mssql_query($sql) or die (Can't execute $sql); } However, this might be out of the question I have been asking here. Has anyone tried to do this type of stuff using Flex and PHP? Alice Date: Wed, 19 Nov 2008 12:23:33 -0800 From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: Re: [PHP-DB] Multiple Update SQL Statements Execution On Wed, Nov 19, 2008 at 5:55 AM, Alice Wei [EMAIL PROTECTED] wrote: I am inquiring on this list to see if it is possible to create a script that takes multiple update statements without my having to write one SQL statement for each of the updates. I'm not sure I understand your question. It is certainly possible to write one query that updates multiple rows at once. In other cases, you can use prepared statements and bound variables. If all you need to do is repeat a query of the same structure with different values, a prepared statement would be faster and mean cleaner code than sending repeated queries. Without more specific info from you, I don't think I can give a better answer than this. I've never worked with Microsoft SQL Server, so I doubt there's anything I can tell you about that in particular. I have a scenario of which I create a table of some sort with some existing information using Flex, and what I am told by my client is that no matter how many records there are on the screen, the users should be able to update any up to all the entries by simply pushing a button. I use Microsoft SQL, which I think that it does allow multiple update query execution. The problem is that I might have to come up with some method to accept all the POST variables the user provides into the script. Let's see. If your POST includes the IDs of the rows you want to change and the value you want to update, it could go something like this. Note that I haven't tested it, so it might contain an error. I'm just trying to provide an illustration of the approach. ?php /* SKIPPED: connect to your database as appropriate. Below I show using the PDO extension to escape the incoming data using the quote() method. If you are using the mssql extension instead, there is no escape function (!) so you'll have to decide how best to escape the data. That's reason enough for me to prefer PDO. If you don't know what I'm talking about here, you should study SQL injection until you're sure you fully understand. Otherwise you will produce very vulnerable code. */ $sql = UPDATE sometable SET somecolumn = ' . $pdo-quote($_POST['field']) . ' WHERE id IN ( . implode(',' $_POST['id']) . ); /* Send this query to your database as appropriate. It will set 'somecolumn' to the value of $_POST['field'] where the ID is in the list. In this case the form should submit the $_POST['id'] value as an array, which can be done by using setting the HTML name attribute to id[] (e.g. name=id[]). */ ? Does this help? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Check the weather nationwide with MSN Search: Try it now! http://search.msn.com/results.aspx?q=weatherFORM=WLMTAG
[PHP-DB] MySQLi not closing connections
Hoping someone may have some insight. I'm a PHP developer for my current employer. Recently, a coworker and myself started revamping our PHP-based intranet to add more OO functionality and replace some of the repetitive procedural code that was in place. In this revamp, we also converted the majority of our scripts to use MySQLi instead of MySQL. We're using the MySQLi class, which I've extended to add some logging functionality, and also incorporated some query builder methods (similar to what you'll see in CodeIgniter's Active Record class). To better troubleshoot semi-random bugs in our intranet, I've implemented an activity log in the database, to record all data in/out, current page, queries used, etc, which logs at the end of each page load. The problem is, this activity log, when enabled, results in all MySQL connections being used, and we run out of open connections. I'm not sure where the error is. Our config.php, when included, initializes the MySQL or MySQLi connection as decided by the script that included the config. Within the same file, a shutdown function is registered to automatically close the MySQL or MySQLi connection at the end of script execution (based on which connection was initialized to begin with). We never have connection issues during normal usage, but once the activity log is enabled (which will execute during shutdown), that is when we see the connections fill up fast. The weird thing is, to my knowledge, the same DB connection is being used, never duplicated. I've seen a PHP 5.3 bug that results in multiple connections being allowed when using the OO mysqli methods, but the mysqli class is only instantiated *once* per page load, same for the mysql procedural instance. The only possibility I can think of, is that normal mysql is being instantiated outside of the config.php include, but then we'd have connections disappearing continuously, not just when the activity log is enabled. I realize this is a long, and probably none-to-helpful email. If anyone can assist, I'd appreciate it. Any info you need, let me know. -- Jon L. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQLi not closing connections
Jonathan Langevin wrote: Hoping someone may have some insight. I'm a PHP developer for my current employer. Recently, a coworker and myself started revamping our PHP-based intranet to add more OO functionality and replace some of the repetitive procedural code that was in place. In this revamp, we also converted the majority of our scripts to use MySQLi instead of MySQL. We're using the MySQLi class, which I've extended to add some logging functionality, and also incorporated some query builder methods (similar to what you'll see in CodeIgniter's Active Record class). To better troubleshoot semi-random bugs in our intranet, I've implemented an activity log in the database, to record all data in/out, current page, queries used, etc, which logs at the end of each page load. The problem is, this activity log, when enabled, results in all MySQL connections being used, and we run out of open connections. I'm not sure where the error is. Our config.php, when included, initializes the MySQL or MySQLi connection as decided by the script that included the config. Within the same file, a shutdown function is registered to automatically close the MySQL or MySQLi connection at the end of script execution (based on which connection was initialized to begin with). We never have connection issues during normal usage, but once the activity log is enabled (which will execute during shutdown), that is when we see the connections fill up fast. The weird thing is, to my knowledge, the same DB connection is being used, never duplicated. I've seen a PHP 5.3 bug that results in multiple connections being allowed when using the OO mysqli methods, but the mysqli class is only instantiated *once* per page load, same for the mysql procedural instance. The only possibility I can think of, is that normal mysql is being instantiated outside of the config.php include, but then we'd have connections disappearing continuously, not just when the activity log is enabled. I realize this is a long, and probably none-to-helpful email. If anyone can assist, I'd appreciate it. Any info you need, let me know. -- Jon L. I'm not sure why the connections are staying open, but I would suggest using mysqli_real_connect with the flag to timout connections. http://www.php.net/manual/en/mysqli.real-connect.php If this is way off base, let me know. -- Jack -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: [PHP-DB] MySQLi not closing connections
Darn it. Didn't change the e-mail recipient to be the list. -- Forwarded message -- From: Fergus Gibson [EMAIL PROTECTED] Date: Tue, Nov 25, 2008 at 12:02 PM Subject: Re: [PHP-DB] MySQLi not closing connections To: Jonathan Langevin [EMAIL PROTECTED] On Tue, Nov 25, 2008 at 6:31 AM, Jonathan Langevin [EMAIL PROTECTED] wrote: The problem is, this activity log, when enabled, results in all MySQL connections being used, and we run out of open connections. I'm not sure where the error is. Our config.php, when included, initializes the MySQL or MySQLi connection as decided by the script that included the config. [...] Hi, Jon. It's difficult to offer specific replies without specific code, but I did raise my eyebrow at the notion of mixing the mysql and mysqli extensions in your application. I'm guessing you've inherited some pretty messy code that is time-consuming to refactor. Is any of the code working against the mysql extension calling mysql_pconnect() to create a persistent database connection? Connections so opened CANNOT be closed using mysql_close(). I have never written an application using persistent connections, but I did get hired at a company where the other programmers describe the same problem you're having (a proliferation of connections that overwhelmed the server). They blamed mysql_pconnect() and the lead programmer said that after he banned the use of that function, the problem went away completely. A comment to the PHP documentation suggests this is the result of a bad interaction between Apache and PHP. Within the same file, a shutdown function is registered to automatically close the MySQL or MySQLi connection at the end of script execution (based on which connection was initialized to begin with). This should not be necessary. The script will close all open resources when it terminates anyway. The reason for having and using close functions is to free resources while the script is still running. As mentioned above, persistent connections created by mysql_pconnect() cannot be closed by mysql_close() under any circumstances, nor will they automatically close on completion of the script. I believe that's your problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQLi not closing connections
On Tue, Nov 25, 2008 at 10:27 AM, Jack Mays [EMAIL PROTECTED] wrote: I'm not sure why the connections are staying open, but I would suggest using mysqli_real_connect with the flag to timout connections. [...] If this is way off base, let me know. Jack, I think Jon shouldn't implement this suggestion. Adjusting the timeout isn't a good way to address spawning a plethora of connections. Jon should find and fix the bug that is causing so many connections to be opened or to persist in the first place rather than simply asking the database to close connections more quickly (your suggestion). Opening or maintaining all those connections is a big waste of resources in the first place. Logically, it's also bad form to make the connections close faster than they should because that means more resources will be used setting up and tearing down connections. I'm all for timeout tuning, but it's a separate issue here. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi not closing connections
I have never written an application using persistent connections, but I did get hired at a company where the other programmers describe the same problem you're having (a proliferation of connections that overwhelmed the server). They blamed mysql_pconnect() and the lead programmer said that after he banned the use of that function, the problem went away completely. A comment to the PHP documentation suggests this is the result of a bad interaction between Apache and PHP. Not really true. pconnect leaves the connection open ('persistent'). From the manual: when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If you're on a shared host with lots of different mysql username/passwords floating around, pconnect will be a very bad idea - because you'll end up with lots of connections sitting around doing nothing (and never being re-used). If you're on dedicated hardware, pconnect can cut down the connection time - but when using them, you need to tune apache as well. There's lots of notes here: http://www.php.net/manual/en/features.persistent-connections.php -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi connections
Reading the thread on mysqli connection issues, I am curious if anyone knows of a downside to creating a connection from a configuration page and using it as a global in all functions? I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single $mysqli = mysqli_connect in a global main file and the just uses global $mysqli in all of it's functions (several hundred) that interact with the database. Since I have not seen this structure used elsewhere, I assume there is a good reason not to use it, but I haven't found one (except for the security issue in the use of globals). Could anyone point me towards any documentation on why such a structure is bad? Thanks, Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi connections
J. Hill wrote: Reading the thread on mysqli connection issues, I am curious if anyone knows of a downside to creating a connection from a configuration page and using it as a global in all functions? Good way to do it. You create the connection at the start and use the same thing throughout the whole script. I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single $mysqli = mysqli_connect in a global main file and the just uses global $mysqli in all of it's functions (several hundred) that interact with the database. Since I have not seen this structure used elsewhere, I assume there is a good reason not to use it, but I haven't found one (except for the security issue in the use of globals). Just old style, nothing wrong with using it that way. The person who wrote it probably just didn't know about singletons. I can't see a security issue with it either. $mysqli is set in the first file included (an 'init' type script). As long as register_globals is off, it can't be overwritten by a $_GET or $_POST .. of course you can destroy it yourself, but that's it. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi connections
Thank you for your response. I am glad to hear that the structure is not a problem, but I suspect he new about singletons (I forgot that term) as he was an old C/C++ progammer (now retired), although I gather he was not a PHP guy. In all other respects, his code and documentation seem very good in comparison with other code from php programmers I've had to modify. It's a little difficult for me at times because he modified the PHP source code, adding his own custom functions. I'm just glad it's not something I have too worry about. I wish I had the time to become a PHP expert, a C++ expert, a Java expert, and time to save the world. Thanks again, Jeff Chris wrote: J. Hill wrote: Reading the thread on mysqli connection issues, I am curious if anyone knows of a downside to creating a connection from a configuration page and using it as a global in all functions? Good way to do it. You create the connection at the start and use the same thing throughout the whole script. I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single $mysqli = mysqli_connect in a global main file and the just uses global $mysqli in all of it's functions (several hundred) that interact with the database. Since I have not seen this structure used elsewhere, I assume there is a good reason not to use it, but I haven't found one (except for the security issue in the use of globals). Just old style, nothing wrong with using it that way. The person who wrote it probably just didn't know about singletons. I can't see a security issue with it either. $mysqli is set in the first file included (an 'init' type script). As long as register_globals is off, it can't be overwritten by a $_GET or $_POST .. of course you can destroy it yourself, but that's it. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php