1 /*************************************************************************
2  *
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * Copyright 2000, 2010 Oracle and/or its affiliates.
6  *
7  * OpenOffice.org - a multi-platform office productivity suite
8  *
9  * This file is part of OpenOffice.org.
10  *
11  * OpenOffice.org is free software: you can redistribute it and/or modify
12  * it under the terms of the GNU Lesser General Public License version 3
13  * only, as published by the Free Software Foundation.
14  *
15  * OpenOffice.org is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU Lesser General Public License version 3 for more details
19  * (a copy is included in the LICENSE file that accompanied this code).
20  *
21  * You should have received a copy of the GNU Lesser General Public License
22  * version 3 along with OpenOffice.org.  If not, see
23  * <http://www.openoffice.org/license.html>
24  * for a copy of the LGPLv3 License.
25  *
26  ************************************************************************/
27 
28 package convwatch;
29 
30 import java.sql.Connection;
31 import java.sql.DriverManager;
32 import java.sql.Statement;
33 import java.sql.ResultSet;
34 import java.sql.SQLException;
35 
36 import java.lang.Thread;
37 import java.util.StringTokenizer;
38 
39 class ShareConnection
40 {
41     private Connection m_aConnection = null;
42     public ShareConnection()
43         {}
44 
45     public Connection getConnection()
46         {
47             if (m_aConnection == null)
48             {
49                 try
50                 {
51                     m_aConnection = DBHelper.getMySQLConnection();
52                 }
53                 catch(java.sql.SQLException e)
54                 {
55                     GlobalLogWriter.get().println("DB: ERROR: can't connect to DB.");
56                     m_aConnection = null;
57                 }
58             }
59             return m_aConnection;
60         }
61 }
62 
63     class MySQLThread extends Thread
64     {
65         Connection m_aCon = null;
66         String m_sSQL;
67         public MySQLThread(Connection _aCon, String _sSQL)
68             {
69                 m_aCon = _aCon;
70                 m_sSQL = _sSQL;
71             }
72 
73         public void run()
74             {
75                 Statement oStmt = null;
76                 if (m_aCon == null)
77                 {
78                     GlobalLogWriter.get().println("DB: ERROR: in ExecSQL, connection not established.");
79                     return;
80                 }
81 
82                 // Connection oCon = null;
83                 try
84                 {
85                     // oCon = getMySQLConnection();
86                     oStmt = m_aCon.createStatement();
87 
88                     GlobalLogWriter.get().println("DB: " + m_sSQL);
89                     /* ResultSet oResult = */
90                     oStmt.executeUpdate(m_sSQL);
91                 }
92                 catch(Exception e)
93                 {
94                     GlobalLogWriter.get().println("DB: Couldn't execute sql string '" + m_sSQL + "'");
95                     GlobalLogWriter.get().println("DB: Reason: " + e.getMessage());
96                 }
97             }
98     }
99 
100 public class DBHelper
101 {
102     /**
103      * This method inserts given values into<br>
104      * the table 'states'
105      * @param values a set of comma separated values to be inserted
106      */
107 
108     public void SQLinsertValues(Connection _aCon, String _sTableName, String value_names, String values)
109         {
110             if (_aCon == null)
111             {
112                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
113                 return;
114             }
115 
116             // String aInsertStr = "";
117             //
118             // aInsertStr = "INSERT INTO " + _sTableName + " (" + value_names + " ) VALUES (" + values + ")";
119             // ExecSQL(_aCon, aInsertStr);
120             StringBuffer aInsertStr = new StringBuffer();
121 
122             aInsertStr.append( "INSERT INTO " ) . append( _sTableName );
123             aInsertStr.append( " (").append( value_names ).append ( ")" );
124             aInsertStr.append(" VALUES (" ).append( values ).append( ")" );
125             ExecSQL(_aCon, aInsertStr.toString() );
126         }
127 
128     public void SQLupdateValue(Connection _aCon, String _sTableName, String _sSet, String _sWhere)
129         {
130             if (_aCon == null)
131             {
132                 GlobalLogWriter.get().println("DB: ERROR: in SQLinsertValues, connection not established.");
133                 return;
134             }
135 
136             // String aUpdateStr = "";
137             //
138             // aUpdateStr = "UPDATE " + _sTableName + " SET " + _sSet + " WHERE " + _sWhere;
139             // ExecSQL( _aCon, aUpdateStr );
140             StringBuffer aUpdateStr = new StringBuffer();
141 
142             aUpdateStr.append( "UPDATE " ).append( _sTableName )
143                 .append( " SET " ).append( _sSet )
144                 .append( " WHERE " ).append( _sWhere );
145             ExecSQL( _aCon, aUpdateStr.toString() );
146         }
147 
148     private static String m_sDBServerName;
149     private static String m_sDBName;
150     private static String m_sDBUser;
151     private static String m_sDBPasswd;
152 
153     protected synchronized void fillDBConnection(String _sInfo)
154         {
155             StringTokenizer aTokenizer = new StringTokenizer(_sInfo,",",false);
156             while (aTokenizer.hasMoreTokens())
157             {
158                 String sPart = aTokenizer.nextToken();
159                 if (sPart.startsWith("db:"))
160                 {
161                     m_sDBName = sPart.substring(3);
162                     // GlobalLogWriter.get().println("DB: source version: " + m_sSourceVersion);
163                 }
164                 else if (sPart.startsWith("user:"))
165                 {
166                     m_sDBUser = sPart.substring(5);
167                 }
168                 else if (sPart.startsWith("passwd:"))
169                 {
170                     m_sDBPasswd = sPart.substring(7);
171                 }
172                 else if (sPart.startsWith("server:"))
173                 {
174                     m_sDBServerName = sPart.substring(7);
175                 }
176             }
177         }
178 
179     /**
180      * This method establishes a Connection<br>
181      * with the database 'module_unit' on jakobus
182      */
183 
184     public static Connection getMySQLConnection() throws SQLException
185         {
186             try
187             {
188                 Class.forName("org.gjt.mm.mysql.Driver");
189                 String sConnection = "jdbc:mysql://" + m_sDBServerName + ":3306/" + m_sDBName;
190                 // Connection mysql = DriverManager.getConnection(
191                 //    "jdbc:mysql://jakobus:3306/jobs_convwatch","admin","admin");
192                 Connection mysql = DriverManager.getConnection(sConnection, m_sDBUser, m_sDBPasswd);
193                 return mysql;
194             }
195             catch (ClassNotFoundException e)
196             {
197                 GlobalLogWriter.get().println("DB: Class not found exception caught: " + e.getMessage());
198                 GlobalLogWriter.get().println("DB: Maybe mysql.jar is not added to the classpath.");
199             }
200             return null;
201         }
202 
203 
204     /**
205      * This method removes all entries of the given<br>
206      * module/platform combination
207      * @param mdl the name of the module, e.g. sal
208      * @param os the name of the platform, e.g. unxsols
209      */
210     // LLA: public static void SQLdeleteValues(Connection _aCon, String _sEnvironment, String _sUnitName, String _sMethodName, String _sCWS, String _sDate)
211     // LLA:     {
212     // LLA:         String sSQL =
213     // LLA:             "DELETE FROM states WHERE " +
214     // LLA:             "     unit=" + DatabaseEntry.Quote(_sUnitName) +
215     // LLA:             " AND pf="   + DatabaseEntry.Quote (_sEnvironment) +
216     // LLA:             " AND meth=" + DatabaseEntry.Quote (_sMethodName) +
217     // LLA:             " AND cws="  + DatabaseEntry.Quote(_sCWS) +
218     // LLA:             " AND dt="   + DatabaseEntry.Quote(_sDate);
219     // LLA:
220     // LLA:         // ExecSQL(_aCon, sSQL);
221     // LLA:     }
222 
223     protected synchronized void ExecSQL(Connection _aCon, String _sSQL)
224             {
225                 MySQLThread aSQLThread = new MySQLThread(_aCon, _sSQL);
226                 aSQLThread.start();
227             }
228 
229 
230 
231     // public static int QueryIntFromSQL(String _sSQL, String _sColumnName, String _sValue)
232     //     {
233     //         boolean bNeedSecondTry = false;
234     //         int nValue = 0;
235     //         do
236     //         {
237     //             try
238     //             {
239     //                 nValue = QueryIntFromSQL(_sSQL, _sColumnName, _sValue);
240     //             }
241     //             catch (ValueNotFoundException e)
242     //             {
243     //                 bNeedSecondTry = true;
244     //                 String sSQL = "INSERT INTO " + _sTable + "(" + _sColumnName + ") VALUES (" + _sValue + ")";
245     //                 ExecSQL(sSQL);
246     //             }
247     //         } while (bNeedSecondTry);
248     //         return nValue;
249     //     }
250 
251     public int QueryIntFromSQL(Connection _aCon, String _sSQL, String _sColumnName)
252         throws ValueNotFoundException
253         {
254             Statement oStmt = null;
255             Connection oCon = null;
256             int nValue = 0;
257             try
258             {
259                 // oCon = getMySQLConnection();
260                 oStmt = _aCon.createStatement();
261 
262                 ResultSet oResult = oStmt.executeQuery(_sSQL);
263                 oResult.next();
264 
265 				try
266                 {
267                     if (_sColumnName.length() == 0)
268                     {
269                         // take the first row value (started with 1)
270                         nValue = oResult.getInt(1);
271                     }
272                     else
273                     {
274                         nValue = oResult.getInt(_sColumnName);
275                     }
276     	            // System.out.println("value: " + String.valueOf(nValue));
277 				}
278 				catch (SQLException e)
279 				{
280 	                String sError = e.getMessage();
281     	            GlobalLogWriter.get().println("DB: Original SQL error: " + sError);
282                     throw new ValueNotFoundException("Cant execute SQL: " + _sSQL);
283 				}
284             }
285             catch(SQLException e)
286             {
287                 String sError = e.getMessage();
288                 GlobalLogWriter.get().println("DB: Couldn't execute sql string " + _sSQL + "\n" + sError);
289             }
290             return nValue;
291         }
292 
293 	public String Quote(String _sToQuote)
294 		{
295             String ts = "'";
296             String ds = "\"";
297             int nQuote = _sToQuote.indexOf(ts);
298             if (nQuote >= 0)
299             {
300                 return ds + _sToQuote + ds;
301             }
302 			return ts + _sToQuote + ts;
303 		}
304 
305 /* default date format in the MySQL DB yyyy-MM-dd */
306     public static String today()
307         {
308             return DateHelper.getDateString("yyyy-MM-dd");
309         }
310 
311     public static final String sComma = ",";
312     public static final String sEqual = "=";
313     public static final String sAND = " AND ";
314 
315 }
316 
317