Java > Open Source Codes > com > caucho > quercus > lib > db > Mysqli


1 /*
2  * Copyright (c) 1998-2006 Caucho Technology -- all rights reserved
3  *
4  * This file is part of Resin(R) Open Source
5  *
6  * Each copy or derived work must preserve the copyright notice and this
7  * notice unmodified.
8  *
9  * Resin Open Source is free software; you can redistribute it and/or modify
10  * it under the terms of the GNU General Public License as published by
11  * the Free Software Foundation; either version 2 of the License, or
12  * (at your option) any later version.
13  *
14  * Resin Open Source is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty
17  * of NON-INFRINGEMENT. See the GNU General Public License for more
18  * details.
19  *
20  * You should have received a copy of the GNU General Public License
21  * along with Resin Open Source; if not, write to the
22  *
23  * Free Software Foundation, Inc.
24  * 59 Temple Place, Suite 330
25  * Boston, MA 02111-1307 USA
26  *
27  * @author Charles Reich
28  */
29
30 package com.caucho.quercus.lib.db;
31
32 import com.caucho.quercus.annotation.Optional;
33 import com.caucho.quercus.annotation.ReturnNullAsFalse;
34 import com.caucho.quercus.env.BooleanValue;
35 import com.caucho.quercus.env.Env;
36 import com.caucho.quercus.env.LongValue;
37 import com.caucho.quercus.env.StringValue;
38 import com.caucho.quercus.env.StringValueImpl;
39 import com.caucho.quercus.env.Value;
40 import com.caucho.util.L10N;
41
42 import java.sql.Connection ;
43 import java.sql.DataTruncation ;
44 import java.sql.ResultSet ;
45 import java.sql.SQLException ;
46 import java.sql.Statement ;
47 import java.util.ArrayList ;
48 import java.util.logging.Level ;
49 import java.util.logging.Logger ;
50
51 /**
52  * mysqli object oriented API facade
53  */
54 public class Mysqli extends JdbcConnectionResource {
55   private static final Logger log = Logger.getLogger(Mysqli.class.getName());
56   private static final L10N L = new L10N(Mysqli.class);
57
58   /**
59    * mysqli_multi_query populates _resultValues
60    * NB: any updates (ie: INSERT, UPDATE, DELETE) will
61    * have the update counts ignored.
62    *
63    * Has been stored tells moreResults whether the
64    * _nextResultValue has been stored already.
65    * If so, more results will return true only if
66    * there is another result.
67    *
68    * _hasBeenStored is set to true by default.
69    * if _hasBeenUsed == false, then
70    * _resultValues.get(_nextResultValue)
71    * is ready to be used by the next call to
72    * mysqli_store_result or mysqli_use_result.
73    */
74   private ArrayList <JdbcResultResource> _resultValues
75     = new ArrayList <JdbcResultResource>();
76   private int _nextResultValue = 0;
77   private boolean _hasBeenUsed = true;
78
79   public Mysqli(Env env,
80                 @Optional("localhost") String host,
81                 @Optional String user,
82                 @Optional String password,
83                 @Optional String db,
84                 @Optional("3306") int port,
85                 @Optional String socket,
86                 @Optional int flags,
87                 @Optional String driver,
88                 @Optional String url)
89   {
90     super(env);
91
92     connectInternal(env, host, user, password, db, port, socket, flags, driver, url);
93   }
94
95   protected Mysqli(Env env)
96   {
97     super(env);
98   }
99
100   public String getResourceType()
101   {
102     return "mysql link";
103   }
104
105   /**
106    * Connects to the underlying database.
107    */
108   protected boolean connectInternal(Env env,
109                                     @Optional("localhost") String host,
110                                     @Optional String userName,
111                                     @Optional String password,
112                                     @Optional String dbname,
113                                     @Optional("3306") int port,
114                                     @Optional String socket,
115                                     @Optional int flags,
116                                     @Optional String driver,
117                                     @Optional String url)
118   {
119     if (isConnected()) {
120       env.warning(L.l("Connection is already opened to '{0}'", this));
121       return false;
122     }
123
124     try {
125       if (host == null || host.equals(""))
126         host = "localhost";
127
128       if (driver == null || driver.equals("")) {
129         driver = "com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource";
130       }
131
132       if (url == null || url.equals("")) {
133         url = "jdbc:mysql://" + host + ":" + port + "/" + dbname;
134       }
135
136       Connection jConn = env.getConnection(driver, url, userName, password);
137
138       setConnection(host, userName, password, dbname, port, jConn, driver, url);
139
140       return true;
141
142     } catch (SQLException e) {
143       env.warning(L.l("A link to the server could not be established.\n url={0}\n driver={1}\n {2}", url, driver, e.toString()));
144
145       env.setSpecialValue("mysqli.connectErrno",new LongValue(e.getErrorCode()));
146       env.setSpecialValue("mysqli.connectError", new StringValueImpl(e.getMessage()));
147
148       return false;
149     } catch (Exception e) {
150       env.warning(L.l("A link to the server could not be established.\n url={0}\n driver={1}\n {2}", url, driver, e.toString()));
151       env.setSpecialValue("mysqli.connectError", new StringValueImpl(e.toString()));
152
153       return false;
154     }
155   }
156
157   /**
158    * returns the number of affected rows.
159    */
160   public int affected_rows()
161   {
162     return validateConnection().getAffectedRows();
163   }
164
165   /**
166    * sets the autocommit mode
167    */
168   public boolean autocommit(boolean isAutoCommit)
169   {
170     return validateConnection().setAutoCommit(isAutoCommit);
171   }
172
173   /**
174    * Changes the user and database
175    *
176    * @param user the new user
177    * @param password the new password
178    * @param db the new database
179    */
180   public boolean change_user(String user, String password, String db)
181   {
182     close(getEnv());
183
184     if ((user == null) || user.equals("")) {
185       user = getUserName();
186     }
187
188     if ((password == null) || password.equals("")) {
189       password = getPassword();
190     }
191
192     if ((db == null) || db.equals("")) {
193       db = getDbName();
194     }
195
196     return connectInternal(getEnv(), getHost(), user, password, db, getPort(),
197                            "", 0, getDriver(), getUrl());
198   }
199
200   /**
201    * Returns the client encoding.
202    *
203    * XXX: stubbed out. has to be revised once we
204    * figure out what to do with character encoding
205    */
206   public String character_set_name()
207   {
208     return getCharacterSetName();
209   }
210
211   /**
212    * Alias for character_set_name
213    */
214   public String client_encoding()
215   {
216     return character_set_name();
217   }
218
219   /**
220    * Returns the error code for the most recent function call
221    */
222   public int errno()
223   {
224     if (isConnected())
225       return getErrorCode();
226     else
227       return 0;
228   }
229
230   /**
231    * Returns the error string for the most recent function call
232    */
233   public String error()
234   {
235     String errorString = super.error();
236
237     if (errorString == null) {
238       return "";
239     }
240
241     return errorString;
242   }
243
244   /**
245    * Escapes the string
246    */
247   public Value escape_string(StringValue str)
248   {
249     return real_escape_string(str);
250   }
251
252   /**
253    * Returns the host information.
254    *
255    * @deprecated
256    */
257   public String get_client_info()
258   {
259     return validateConnection().getClientInfo();
260   }
261
262   /**
263    * Returns the database name.
264    */
265   public String get_dbname()
266   {
267     return getDbName();
268   }
269
270   /**
271    * Returns the host information.
272    */
273   public String get_host_info()
274   {
275     return getHost() + " via TCP socket";
276   }
277
278   /**
279    * Returns the host name.
280    */
281   public String get_host_name()
282   {
283     return getHost();
284   }
285
286   /**
287    * Returns the port number.
288    */
289   public int get_port_number()
290   {
291     return getPort();
292   }
293
294   /**
295    * Returns the protocol information.
296    */
297   public int get_proto_info()
298   {
299     return 10;
300   }
301
302   /**
303    * Returns the server information.
304    */
305   public String get_server_info()
306   {
307     try {
308       return validateConnection().getServerInfo();
309     } catch (SQLException e) {
310       return null;
311     }
312   }
313
314   /**
315    * Returns the server information.
316    */
317   public int get_server_version()
318   {
319     try {
320       String info = validateConnection().getServerInfo();
321
322       return infoToVersion(info);
323     } catch (SQLException e) {
324       return 0;
325     }
326   }
327
328   /**
329    * Returns the number of columns in the last query.
330    */
331   public int field_count()
332   {
333     return validateConnection().getFieldCount();
334   }
335
336   /**
337    * returns ID generated for an AUTO_INCREMENT column by the previous
338    * INSERT query on success, 0 if the previous query does not generate
339    * an AUTO_INCREMENT value, or FALSE if no MySQL connection was established
340    *
341    */
342   public Value insert_id()
343   {
344     try {
345       JdbcConnectionResource connV = validateConnection();
346       Connection conn = connV.getConnection();
347
348       Statement stmt = null;
349
350       try {
351         stmt = conn.createStatement();
352
353         ResultSet rs = stmt.executeQuery("SELECT @@identity");
354
355         if (rs.next())
356           return new LongValue(rs.getLong(1));
357         else
358           return BooleanValue.FALSE;
359       } finally {
360         if (stmt != null)
361           stmt.close();
362       }
363     } catch (SQLException e) {
364       log.log(Level.FINE, e.toString(), e);
365       return BooleanValue.FALSE;
366     }
367   }
368
369   /**
370    * Stub: kills the given mysql thread.
371    */
372   public boolean kill(int processId)
373   {
374     return false;
375   }
376
377   @ReturnNullAsFalse
378   public JdbcResultResource list_dbs()
379   {
380     return validateConnection().getCatalogs();
381   }
382
383   /**
384    * Check for more results in a multi-query
385    */
386   public boolean more_results()
387   {
388     return ((Mysqli) validateConnection()).moreResults();
389   }
390
391   /**
392    * executes one or multiple queries which are
393    * concatenated by a semicolon.
394    */
395   public boolean multi_query(String query)
396   {
397     return ((Mysqli) validateConnection()).multiQuery(query);
398   }
399
400   /**
401    * prepares next result set from a previous call to
402    * mysqli_multi_query
403    */
404   public boolean next_result()
405   {
406     return ((Mysqli) validateConnection()).nextResult();
407   }
408
409   /**
410    * Sets a mysqli options
411    */
412   public boolean options(int option, Value value)
413   {
414     return false;
415   }
416
417   /**
418    * Pings the database
419    */
420   public boolean ping()
421   {
422     return super.ping();
423   }
424
425   /**
426    * Executes a query.
427    *
428    * @param env the PHP executing environment
429    * @param sql the escaped query string (can contain escape sequences like `\n' and `\Z')
430    * @param resultMode ignored
431    *
432    * @return a {@link JdbcResultResource}, or null for failure
433    */
434   public Value query(Env env,
435                      String sql,
436                      @Optional("MYSQLI_STORE_RESULT") int resultMode)
437   {
438     MysqliResult result = (MysqliResult) realQuery(sql);
439
440     if (result == null) {
441       if (getErrorCode() == 0) {
442         // No error, this was an UPDATE, DELETE, etc.
443 return BooleanValue.TRUE;
444       }
445       return BooleanValue.FALSE;
446     }
447
448     return env.wrapJava(result);
449   }
450
451   /**
452    * returns a prepared statement
453    */
454   public MysqliStatement prepare(Env env, String query)
455   {
456     MysqliStatement stmt = new MysqliStatement((Mysqli) validateConnection());
457
458     stmt.prepare(query);
459
460     return stmt;
461   }
462
463   /**
464    * Connects to the underlying database.
465    */
466   public boolean real_connect(Env env,
467                               @Optional("localhost") String host,
468                               @Optional String userName,
469                               @Optional String password,
470                               @Optional String dbname,
471                               @Optional("3306") int port,
472                               @Optional String socket,
473                               @Optional int flags)
474   {
475     return connectInternal(env, host, userName, password, dbname, port, socket,
476                            flags, "", "");
477   }
478
479   /**
480    * Escapes the string
481    */
482   public Value real_escape_string(StringValue str)
483   {
484     return realEscapeString(str);
485   }
486
487   /**
488    * Rolls the current transaction back.
489    */
490   public boolean rollback()
491   {
492     return super.rollback();
493   }
494
495   /**
496    * Selects the underlying database/catalog to use.
497    *
498    * @param dbname the name of the database to select.
499    */
500   public boolean select_db(String dbname)
501   {
502     try {
503       if (isConnected()) {
504         validateConnection().setCatalog(dbname);
505
506         return true;
507       }
508       else
509         return false;
510     } catch (SQLException e) {
511       log.log(Level.FINE, e.toString(), e);
512       getEnv().warning(e.getMessage());
513       return false;
514     }
515   }
516
517   /**
518    * Sets the character set
519    */
520   public boolean set_charset(String charset)
521   {
522     return false;
523   }
524
525   /**
526    * Sets a mysqli option
527    */
528   public boolean set_opt(int option, Value value)
529   {
530     return options(option, value);
531   }
532
533   /**
534    * Returns the SQLSTATE error
535    */
536   public String sqlstate()
537   {
538     return "HY" + validateConnection().getErrorCode();
539   }
540
541   /**
542    * returns a string with the status of the connection
543    * or FALSE if error
544    */
545   public Value stat(Env env)
546   {
547     try {
548       JdbcConnectionResource connV = validateConnection();
549
550       Connection conn = connV.getConnection();
551       Statement stmt = null;
552
553       StringBuilder str = new StringBuilder ();
554
555       try {
556         stmt = conn.createStatement();
557         stmt.execute("SHOW STATUS");
558
559         ResultSet rs = stmt.getResultSet();
560
561         while (rs.next()) {
562           if (str.length() > 0)
563             str.append(' ');
564           str.append(rs.getString(1));
565           str.append(": ");
566           str.append(rs.getString(2));
567         }
568
569         return new StringValueImpl(str.toString());
570       } finally {
571         if (stmt != null)
572           stmt.close();
573       }
574     } catch (SQLException e) {
575       log.log(Level.FINE, e.toString(), e);
576       return BooleanValue.FALSE;
577     }
578   }
579
580   /**
581    * returns a string with the status of the connection
582    *
583   public Value stat()
584   {
585     clearErrors();
586
587     StringBuilder str = new StringBuilder();
588
589     try {
590       Statement stmt = _conn.createStatement();
591       stmt.execute("SHOW STATUS");
592
593       ResultSet rs = stmt.getResultSet();
594
595       while (rs.next()) {
596         if (str.length() > 0)
597           str.append(' ');
598         str.append(rs.getString(1));
599         str.append(": ");
600         str.append(rs.getString(2));
601       }
602
603       return new StringValueImpl(str.toString());
604     } catch (SQLException e) {
605       saveErrors(e);
606       log.log(Level.WARNING, e.toString(), e);
607       return BooleanValue.FALSE;
608     }
609   }
610   */
611
612   /**
613    * returns a statement for use with
614    * mysqli_stmt_prepare
615    */
616   public MysqliStatement stmt_init(Env env)
617   {
618     return new MysqliStatement((Mysqli) validateConnection());
619   }
620
621   /**
622    * Transfers the result set from the last query on the
623    * database connection represented by conn.
624    *
625    * Used in conjunction with mysqli_multi_query
626    */
627   @ReturnNullAsFalse
628   public JdbcResultResource store_result(Env env)
629   {
630     return ((Mysqli) validateConnection()).storeResult();
631   }
632
633   /**
634    * Returns a bogus thread id
635    */
636   public int thread_id()
637   {
638     return 1;
639   }
640
641   /**
642    * Returns true for thread_safe
643    */
644   public boolean thread_safe()
645   {
646     return true;
647   }
648
649   /**
650    * Transfers the result set from the last query on the
651    * database connection represented by conn.
652    *
653    * Used in conjunction with mysqli_multi_query
654    */
655   @ReturnNullAsFalse
656   public JdbcResultResource use_result(Env env)
657   {
658     return ((Mysqli) validateConnection()).storeResult();
659   }
660
661   /**
662    * returns the number of warnings from the last query
663    * in the connection object.
664    *
665    * @return number of warnings
666    */
667   public int warning_count()
668   {
669     return ((Mysqli) validateConnection()).getWarningCount();
670   }
671
672   /**
673    * Creates a database-specific result.
674    */
675   protected JdbcResultResource createResult(Statement stmt,
676                                             ResultSet rs)
677   {
678     return new MysqliResult(stmt, rs, this);
679   }
680
681   /**
682    * This functions queries the connection with "SHOW WARNING"
683    *
684    * @return # of warnings
685    */
686   private int getWarningCount()
687   {
688     if (getWarnings() != null) {
689       JdbcResultResource warningResult;
690       warningResult = metaQuery("SHOW WARNINGS",getCatalog().toString());
691       int warningCount = 0;
692
693       if (warningResult != null) {
694         warningCount =
695           JdbcResultResource.getNumRows(warningResult.getResultSet());
696       }
697
698       if (warningCount >= 0)
699         return warningCount;
700       else
701         return 0;
702     } else
703       return 0;
704   }
705
706   /**
707    * Used by the
708    * various mysqli functions to query the database
709    * for metadata about the resultset which is
710    * not in ResultSetMetaData.
711    *
712    * This function DOES NOT clear existing resultsets.
713    */
714   protected MysqliResult metaQuery(String sql,
715                                    String catalog)
716   {
717     clearErrors();
718
719     Value currentCatalog = getCatalog();
720
721     try {
722       getConnection().setCatalog(catalog);
723
724       // need to create statement after setting catalog or
725 // else statement will have wrong catalog
726 Statement stmt = getConnection().createStatement();
727       stmt.setEscapeProcessing(false);
728
729       if (stmt.execute(sql)) {
730         MysqliResult result = (MysqliResult) createResult(stmt, stmt.getResultSet());
731         getConnection().setCatalog(currentCatalog.toString());
732         return result;
733       } else {
734         getConnection().setCatalog(currentCatalog.toString());
735         return null;
736       }
737     } catch (SQLException e) {
738       saveErrors(e);
739       log.log(Level.WARNING, e.toString(), e);
740       return null;
741     }
742   }
743
744   /**
745    * indicates if one or more result sets are
746    * available from a multi query
747    *
748    * _hasBeenStored tells moreResults whether the
749    * _nextResultValue has been stored already.
750    * If so, more results will return true only if
751    * there is another result.
752    */
753   private boolean moreResults()
754   {
755     return !_hasBeenUsed || _nextResultValue < _resultValues.size() - 1;
756   }
757
758   /**
759    * Used for multiple queries. the
760    * JdbcConnectionResource now stores the
761    * result sets so that mysqli_store_result
762    * and mysqli_use_result can return result values.
763    *
764    * XXX: this may not function correctly in the
765    * context of a transaction. Unclear wether
766    * mysqli_multi_query was designed with transactions
767    * in mind.
768    *
769    * XXX: multiQuery sets fieldCount to true or false
770    * depending on the last query entered. Not sure what
771    * actual PHP intention is.
772    */
773   private boolean multiQuery(String sql)
774   {
775     clearErrors();
776
777     // Empty _resultValues on new call to query
778 // But DO NOT close the individual result sets.
779 // They may still be in use.
780 _resultValues.clear();
781
782     ArrayList <String > splitQuery = splitMultiQuery(sql);
783
784     Statement stmt = null;
785
786     try {
787       setResultResource(null);
788
789       for (String s : splitQuery) {
790         stmt = getConnection().createStatement();
791         stmt.setEscapeProcessing(false);
792         if (stmt.execute(s)) {
793           setAffectedRows(0);
794           setResultResource(createResult(stmt, stmt.getResultSet()));
795           _resultValues.add(getResultResource());
796           setWarnings(stmt.getWarnings());
797         } else {
798           setAffectedRows(stmt.getUpdateCount());
799           setWarnings(stmt.getWarnings());
800         }
801       }
802     } catch (DataTruncation truncationError) {
803       try {
804         setAffectedRows(stmt.getUpdateCount());
805         setWarnings(stmt.getWarnings());
806       } catch (SQLException e) {
807         saveErrors(e);
808         log.log(Level.WARNING, e.toString(), e);
809         return false;
810       }
811     } catch (SQLException e) {
812       saveErrors(e);
813       log.log(Level.WARNING, e.toString(), e);
814       return false;
815     }
816
817     if (_resultValues.size() > 0) {
818       _nextResultValue = 0;
819       _hasBeenUsed = false;
820     }
821
822     return true;
823   }
824
825   /**
826    * prepares the next resultset from
827    * a multi_query
828    */
829   private boolean nextResult()
830   {
831     if (_nextResultValue + 1 < _resultValues.size()) {
832       _hasBeenUsed = false;
833       _nextResultValue++;
834       return true;
835     } else
836       return false;
837   }
838
839   /**
840    * splits a string of multiple queries separated
841    * by ";" into an arraylist of strings
842    */
843   private ArrayList <String > splitMultiQuery(String sql)
844   {
845     ArrayList <String > result = new ArrayList <String >();
846     String query = "";
847     int length = sql.length();
848     boolean inQuotes = false;
849     char c;
850
851     for (int i = 0; i < length; i++) {
852       c = sql.charAt(i);
853
854       if (c == '\\') {
855         query += c;
856         if (i < length - 1) {
857           query += sql.charAt(i+1);
858           i++;
859         }
860         continue;
861       }
862
863       if (inQuotes) {
864         query += c;
865         if (c == '\'') {
866           inQuotes = false;
867         }
868         continue;
869       }
870
871       if (c == '\'') {
872         query += c;
873         inQuotes = true;
874         continue;
875       }
876
877       if (c == ';') {
878         result.add(query.trim());
879         query = "";
880       } else
881         query += c;
882     }
883
884     if (query != null)
885       result.add(query.trim());
886
887     return result;
888   }
889
890   /**
891    * returns the next jdbcResultValue
892    */
893   private JdbcResultResource storeResult()
894   {
895     if (!_hasBeenUsed) {
896       _hasBeenUsed = true;
897
898       return _resultValues.get(_nextResultValue);
899     } else
900       return null;
901   }
902
903   public String toString()
904   {
905     if (isConnected())
906       return "Mysqli[" + get_host_name() + "]";
907     else
908       return "Mysqli[]";
909   }
910 }
911

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags