Java > Open Source Codes > org > apache > derby > catalog > SystemProcedures


1 /*
2
3    Derby - Class org.apache.derby.catalog.SystemProcedures
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derby.catalog;
23
24 import org.apache.derby.iapi.services.sanity.SanityManager;
25
26 import org.apache.derby.iapi.services.i18n.MessageService;
27 import org.apache.derby.iapi.error.PublicAPI;
28 import org.apache.derby.iapi.error.StandardException;
29 import org.apache.derby.iapi.reference.SQLState;
30 import java.sql.ResultSet ;
31 import java.sql.Connection ;
32 import java.sql.PreparedStatement ;
33 import java.sql.SQLException ;
34 import java.sql.DatabaseMetaData ;
35 import java.util.StringTokenizer ;
36 import java.util.NoSuchElementException ;
37
38 import org.apache.derby.jdbc.InternalDriver;
39 import org.apache.derby.iapi.db.Factory;
40 import org.apache.derby.iapi.db.PropertyInfo;
41 import org.apache.derby.impl.jdbc.Util;
42 import org.apache.derby.impl.load.Export;
43 import org.apache.derby.impl.load.Import;
44 import org.apache.derby.impl.jdbc.EmbedDatabaseMetaData;
45
46 import org.apache.derby.impl.sql.execute.JarDDL;
47 import org.apache.derby.iapi.util.IdUtil;
48 import org.apache.derby.iapi.error.PublicAPI;
49 import org.apache.derby.iapi.error.StandardException;
50 import org.apache.derby.iapi.sql.conn.ConnectionUtil;
51
52
53 /**
54     Some system built-in procedures, and help routines. Now used for network server.
55     These procedures are built-in to the SYSIBM schema which match the DB2 SYSIBM procedures.
56     Currently information on those can be found at url:
57     ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2l2e80.pdf
58     
59     <P>
60     Also used for builtin-routines, such as SYSFUN functions, when direct calls
61     into Java libraries cannot be made.
62 */
63 public class SystemProcedures {
64
65
66     private final static int SQL_BEST_ROWID = 1;
67     private final static int SQL_ROWVER = 2;
68     private final static String DRIVER_TYPE_OPTION = "DATATYPE";
69     private final static String ODBC_DRIVER_OPTION = "'ODBC'";
70
71     // This token delimiter value is used to separate the tokens for multiple
72 // error messages. This is used in DRDAConnThread
73 /**
74      * <code>SQLERRMC_MESSAGE_DELIMITER</code> When message argument tokes are sent,
75      * this value separates the tokens for mulitiple error messages
76      */
77     public static String SQLERRMC_MESSAGE_DELIMITER = new String (new char[] {(char)20,(char)20,(char)20});
78
79     /**
80       Method used by Cloudscape Network Server to get localized message (original call
81       from jcc.
82
83       @param sqlcode sqlcode, not used.
84       @param errmcLen sqlerrmc length
85       @param sqlerrmc sql error message tokens, variable part of error message (ie.,
86                         arguments) plus messageId, separated by separator.
87       @param sqlerrp not used
88       @param errd0 not used
89       @param errd1 not used
90       @param errd2 not used
91       @param errd3 not used
92       @param errd4 not used
93       @param errd5 not used
94       @param warn not used
95       @param sqlState 5-char sql state
96       @param file not used
97       @param localeStr client locale in string
98       @param msg OUTPUT parameter, localized error message
99       @param rc OUTPUT parameter, return code -- 0 for success
100      */
101     public static void SQLCAMESSAGE(int sqlcode, short errmcLen, String sqlerrmc,
102                                         String sqlerrp, int errd0, int errd1, int errd2,
103                                         int errd3, int errd4, int errd5, String warn,
104                                         String sqlState, String file, String localeStr,
105                                         String [] msg, int[] rc)
106     {
107         int numMessages = 1;
108         
109
110         // Figure out if there are multiple exceptions in sqlerrmc. If so get each one
111 // translated and append to make the final result.
112 for (int index=0; ; numMessages++)
113         {
114             if (sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER, index) == -1)
115                 break;
116             index = sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER, index) +
117                         SQLERRMC_MESSAGE_DELIMITER.length();
118         }
119
120         // Putting it here instead of prepareCall it directly is because inter-jar reference tool
121 // cannot detect/resolve this otherwise
122 if (numMessages == 1)
123             MessageService.getLocalizedMessage(sqlcode, errmcLen, sqlerrmc, sqlerrp, errd0, errd1,
124                                             errd2, errd3, errd4, errd5, warn, sqlState, file,
125                                             localeStr, msg, rc);
126         else
127         {
128             int startIdx=0, endIdx;
129             String sqlError;
130             String [] errMsg = new String [2];
131             for (int i=0; i<numMessages; i++)
132             {
133                 endIdx = sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER, startIdx);
134                 if (i == numMessages-1) // last error message
135 sqlError = sqlerrmc.substring(startIdx);
136                 else sqlError = sqlerrmc.substring(startIdx, endIdx);
137
138                 if (i > 0)
139                 {
140                     /* Strip out the SQLState */
141                     sqlState = sqlError.substring(0, 5);
142                     sqlError = sqlError.substring(6);
143                     msg[0] += " SQLSTATE: " + sqlState + ": ";
144                 }
145
146                 MessageService.getLocalizedMessage(sqlcode, (short)sqlError.length(), sqlError,
147                                             sqlerrp, errd0, errd1, errd2, errd3, errd4, errd5,
148                                             warn, sqlState, file, localeStr, errMsg, rc);
149
150                 if (rc[0] == 0) // success
151 {
152                     if (i == 0)
153                         msg[0] = errMsg[0];
154                     else msg[0] += errMsg[0]; // append the new message
155 }
156                 startIdx = endIdx + SQLERRMC_MESSAGE_DELIMITER.length();
157             }
158         }
159     }
160     
161     /**
162      * Get the default or nested connection corresponding to the URL
163      * jdbc:default:connection. We do not use DriverManager here
164      * as it is not supported in JSR 169. IN addition we need to perform
165      * more checks for null drivers or the driver returing null from connect
166      * as that logic is in DriverManager.
167      * @return The nested connection
168      * @throws SQLException Not running in a SQL statement
169      */
170     private static Connection getDefaultConn()throws SQLException
171     {
172         InternalDriver id = InternalDriver.activeDriver();
173         if (id != null) {
174             Connection conn = id.connect("jdbc:default:connection", null);
175             if (conn != null)
176                 return conn;
177         }
178         throw Util.noCurrentConnection();
179     }
180
181     /**
182      * Get the DatabaseMetaData for the current connection for use in
183      * mapping the jcc SYSIBM.* calls to the Cloudscape DatabaseMetaData methods
184      *
185      * @return The DatabaseMetaData object of the current connection
186      */
187     private static DatabaseMetaData getDMD() throws SQLException {
188         Connection conn = getDefaultConn();
189         return conn.getMetaData();
190     }
191
192     /**
193      * Map SQLProcedures to EmbedDatabaseMetaData.getProcedures
194      *
195      * @param catalogName SYSIBM.SQLProcedures CatalogName varchar(128),
196      * @param schemaName SYSIBM.SQLProcedures SchemaName varchar(128),
197      * @param procName SYSIBM.SQLProcedures ProcName varchar(128),
198      * @param options SYSIBM.SQLProcedures Options varchar(4000))
199      * @param rs output parameter, the resultset object containing
200      * the result of getProcedures
201      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
202      * version of this procedure.
203      */
204     public static void SQLPROCEDURES (String catalogName, String schemaName, String procName,
205                                         String options, ResultSet [] rs) throws SQLException
206     {
207         rs[0] = isForODBC(options)
208             ? ((EmbedDatabaseMetaData)getDMD()).getProceduresForODBC(
209                 catalogName, schemaName, procName)
210             : getDMD().getProcedures(catalogName, schemaName, procName);
211     }
212
213     /**
214      * Map SQLFunctions to EmbedDatabaseMetaData.getFunctions
215      *
216      * @param catalogName SYSIBM.SQLFunctions CatalogName varchar(128),
217      * @param schemaName SYSIBM.SQLFunctions SchemaName varchar(128),
218      * @param funcName SYSIBM.SQLFunctions ProcName varchar(128),
219      * @param options SYSIBM.SQLFunctions Options varchar(4000))
220      * (not used)
221      * @param rs output parameter, the resultset object containing
222      * the result of getFunctions
223      */
224     public static void SQLFUNCTIONS(String catalogName,
225                                     String schemaName,
226                                     String funcName,
227                                     String options,
228                                     ResultSet [] rs) throws SQLException
229     {
230         rs[0] = ((EmbedDatabaseMetaData)getDMD()).
231             getFunctions(catalogName, schemaName, funcName);
232     }
233
234     /**
235      * Map SQLTables to EmbedDatabaseMetaData.getSchemas, getCatalogs,
236      * getTableTypes and getTables, and return the result of the
237      * DatabaseMetaData calls.
238      *
239      * <p>JCC and DNC overload this method:
240      * <ul>
241      * <li>If options contains the string 'GETSCHEMAS=1',
242      * call getSchemas()</li>
243      * <li>If options contains the string 'GETSCHEMAS=2',
244      * call getSchemas(String, String)</li>
245      * <li>If options contains the string 'GETCATALOGS=1',
246      * call getCatalogs()</li>
247      * <li>If options contains the string 'GETTABLETYPES=1',
248      * call getTableTypes()</li>
249      * <li>otherwise, call getTables()</li>
250      * </ul>
251      *
252      * @param catalogName SYSIBM.SQLTables CatalogName varchar(128),
253      * @param schemaName SYSIBM.SQLTables SchemaName varchar(128),
254      * @param tableName SYSIBM.SQLTables TableName varchar(128),
255      * @param tableType SYSIBM.SQLTables TableType varchar(4000))
256      * @param options SYSIBM.SQLTables Options varchar(4000))
257      * @param rs output parameter, the resultset object
258      */
259     public static void SQLTABLES (String catalogName, String schemaName, String tableName,
260                                         String tableType, String options, ResultSet [] rs)
261         throws SQLException
262     {
263
264         String optionValue = getOption("GETCATALOGS", options);
265         if (optionValue != null && optionValue.trim().equals("1"))
266         {
267             rs[0] = getDMD().getCatalogs();
268             return;
269         }
270         optionValue = getOption("GETTABLETYPES", options);
271         if (optionValue != null && optionValue.trim().equals("1"))
272         {
273             rs[0] = getDMD().getTableTypes();
274             return;
275         }
276         optionValue = getOption("GETSCHEMAS", options);
277         if (optionValue != null) {
278             optionValue = optionValue.trim();
279             if (optionValue.equals("1")) {
280                 rs[0] = getDMD().getSchemas();
281                 return;
282             }
283             if (optionValue.equals("2")) {
284                 EmbedDatabaseMetaData edmd = (EmbedDatabaseMetaData) getDMD();
285                 rs[0] = edmd.getSchemas(catalogName, schemaName);
286                 return;
287             }
288         }
289                 
290
291         String [] typeArray = null;
292         if (tableType != null)
293         {
294             StringTokenizer st = new StringTokenizer (tableType,"',");
295             typeArray = new String [st.countTokens()];
296             int i = 0;
297
298             while (st.hasMoreTokens())
299             {
300                 typeArray[i] = st.nextToken();
301                 i++;
302             }
303         }
304         rs[0] = getDMD().getTables(catalogName, schemaName, tableName, typeArray);
305     }
306
307     /**
308      * Map SQLForeignKeys to EmbedDatabaseMetaData.getImportedKeys, getExportedKeys, and getCrossReference
309      *
310      * @param pkCatalogName SYSIBM.SQLForeignKeys PKCatalogName varchar(128),
311      * @param pkSchemaName SYSIBM.SQLForeignKeys PKSchemaName varchar(128),
312      * @param pkTableName SYSIBM.SQLForeignKeys PKTableName varchar(128),
313      * @param fkCatalogName SYSIBM.SQLForeignKeys FKCatalogName varchar(128),
314      * @param fkSchemaName SYSIBM.SQLForeignKeys FKSchemaName varchar(128),
315      * @param fkTableName SYSIBM.SQLForeignKeys FKTableName varchar(128),
316      * @param options SYSIBM.SQLForeignKeys Options varchar(4000))
317      * @param rs output parameter, the resultset object
318      * containing the result of the DatabaseMetaData calls
319      * JCC overloads this method:
320      * If options contains the string 'EXPORTEDKEY=1', call getImportedKeys
321      * If options contains the string 'IMPORTEDKEY=1', call getExportedKeys
322      * otherwise, call getCrossReference
323      */
324     public static void SQLFOREIGNKEYS (String pkCatalogName, String pkSchemaName, String pkTableName,
325                                         String fkCatalogName, String fkSchemaName, String fkTableName,
326                                         String options, ResultSet [] rs)
327         throws SQLException
328     {
329
330         String exportedKeyProp = getOption("EXPORTEDKEY", options);
331         String importedKeyProp = getOption("IMPORTEDKEY", options);
332
333         if (importedKeyProp != null && importedKeyProp.trim().equals("1"))
334             rs[0] = getDMD().getImportedKeys(fkCatalogName,
335                                         fkSchemaName,fkTableName);
336         else if (exportedKeyProp != null && exportedKeyProp.trim().equals("1"))
337             rs[0] = getDMD().getExportedKeys(pkCatalogName,
338                                         pkSchemaName,pkTableName);
339         else
340             rs[0] = getDMD().getCrossReference (pkCatalogName,
341                                            pkSchemaName,
342                                            pkTableName,
343                                            fkCatalogName,
344                                            fkSchemaName,
345                                            fkTableName);
346     }
347
348     /**
349      * Helper for SQLForeignKeys and SQLTables
350      *
351      * @return option String containing the value for a given option
352      * @param pattern String containing the option to search for
353      * @param options String containing the options to search through
354      */
355     private static String getOption(String pattern, String options)
356     {
357         if (options == null)
358             return null;
359         int start = options.lastIndexOf(pattern);
360         if (start < 0) // not there
361 return null;
362         int valueStart = options.indexOf('=', start);
363         if (valueStart < 0) // invalid options string
364 return null;
365         int valueEnd = options.indexOf(';', valueStart);
366         if (valueEnd < 0) // last option
367 return options.substring(valueStart + 1);
368         else
369             return options.substring(valueStart + 1, valueEnd);
370     }
371     
372     /**
373      * Map SQLProcedureCols to EmbedDatabaseMetaData.getProcedureColumns
374      *
375      * @param catalogName SYSIBM.SQLProcedureCols CatalogName varchar(128),
376      * @param schemaName SYSIBM.SQLProcedureCols SchemaName varchar(128),
377      * @param procName SYSIBM.SQLProcedureCols ProcName varchar(128),
378      * @param paramName SYSIBM.SQLProcedureCols ParamName varchar(128),
379      * @param options SYSIBM.SQLProcedureCols Options varchar(4000))
380      * @param rs output parameter, the resultset object containing
381      * the result of getProcedureColumns
382      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
383      * version of this procedure.
384      */
385     public static void SQLPROCEDURECOLS (String catalogName, String schemaName, String procName,
386                                         String paramName, String options, ResultSet [] rs)
387         throws SQLException
388     {
389         rs[0] = isForODBC(options)
390             ? ((EmbedDatabaseMetaData)getDMD()).getProcedureColumnsForODBC(
391                 catalogName, schemaName, procName, paramName)
392             : getDMD().getProcedureColumns(catalogName, schemaName, procName, paramName);
393     }
394     
395     /**
396      * Map SQLFunctionParameters to
397      * EmbedDatabaseMetaData.getFunctionColumns()
398      *
399      * @param catalogName SYSIBM.SQLFunctionParameters CatalogName
400      * varchar(128),
401      * @param schemaName SYSIBM.SQLFunctionParameters SchemaName
402      * varchar(128),
403      * @param funcName SYSIBM.SQLFunctionParameters FuncName
404      * varchar(128),
405      * @param paramName SYSIBM.SQLFunctionParameters ParamName
406      * varchar(128),
407      * @param options SYSIBM.SQLFunctionParameters Options
408      * varchar(4000))
409      * @param rs output parameter, the resultset object containing the
410      * result of getFunctionColumns().
411      */
412     public static void SQLFUNCTIONPARAMS(String catalogName,
413                                          String schemaName,
414                                          String funcName,
415                                          String paramName,
416                                          String options,
417                                          ResultSet [] rs) throws SQLException
418         {
419             rs[0] = ((EmbedDatabaseMetaData)getDMD()).
420                 getFunctionColumns(catalogName, schemaName, funcName,
421                                       paramName);
422         }
423     
424
425     /**
426      * Map SQLColumns to EmbedDatabaseMetaData.getColumns
427      *
428      * @param catalogName SYSIBM.SQLColumns CatalogName varchar(128),
429      * @param schemaName SYSIBM.SQLColumns SchemaName varchar(128),
430      * @param tableName SYSIBM.SQLColumns TableName varchar(128),
431      * @param columnName SYSIBM.SQLColumns ColumnName varchar(128),
432      * @param options SYSIBM.SQLColumns Options varchar(4000))
433      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
434      * version of this procedure.
435      * @param rs output parameter, the resultset object containing
436      * the result of getProcedures
437      */
438     public static void SQLCOLUMNS (String catalogName, String schemaName, String tableName,
439                                         String columnName, String options, ResultSet [] rs)
440         throws SQLException
441     {
442         rs[0] = isForODBC(options)
443             ? ((EmbedDatabaseMetaData)getDMD()).getColumnsForODBC(
444                 catalogName, schemaName, tableName, columnName)
445             : getDMD().getColumns(catalogName, schemaName, tableName, columnName);
446     }
447
448     /**
449      * Map SQLColPrivileges to EmbedDatabaseMetaData.getColumnPrivileges
450      *
451      * @param catalogName SYSIBM.SQLColPrivileges CatalogName varchar(128),
452      * @param schemaName SYSIBM.SQLColPrivileges SchemaName varchar(128),
453      * @param tableName SYSIBM.SQLColPrivileges ProcName varchar(128),
454      * @param columnName SYSIBM.SQLColPrivileges ColumnName varchar(128),
455      * @param options SYSIBM.SQLColPrivileges Options varchar(4000))
456      * @param rs output parameter, the resultset object containing
457      * the result of getColumnPrivileges
458      */
459     public static void SQLCOLPRIVILEGES (String catalogName, String schemaName, String tableName,
460                                         String columnName, String options, ResultSet [] rs)
461         throws SQLException
462     {
463         rs[0] = getDMD().getColumnPrivileges(catalogName, schemaName, tableName, columnName);
464     }
465
466     /**
467      * Map SQLTablePrivileges to EmbedDatabaseMetaData.getTablePrivileges
468      *
469      * @param catalogName SYSIBM.SQLTablePrivileges CatalogName varchar(128),
470      * @param schemaName SYSIBM.SQLTablePrivileges SchemaName varchar(128),
471      * @param tableName SYSIBM.SQLTablePrivileges ProcName varchar(128),
472      * @param options SYSIBM.SQLTablePrivileges Options varchar(4000))
473      * @param rs output parameter, the resultset object containing
474      * the result of getTablePrivileges
475      */
476     public static void SQLTABLEPRIVILEGES (String catalogName, String schemaName, String tableName,
477                                         String options, ResultSet [] rs)
478         throws SQLException
479     {
480         rs[0] = getDMD().getTablePrivileges(catalogName, schemaName, tableName);
481     }
482
483     /**
484      * Map SQLPrimaryKeys to EmbedDatabaseMetaData.getPrimaryKeys
485      *
486      * @param catalogName SYSIBM.SQLPrimaryKeys CatalogName varchar(128),
487      * @param schemaName SYSIBM.SQLPrimaryKeys SchemaName varchar(128),
488      * @param tableName SYSIBM.SQLPrimaryKeys TableName varchar(128),
489      * @param options SYSIBM.SQLPrimaryKeys Options varchar(4000))
490      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
491      * version of this procedure.
492      * @param rs output parameter, the resultset object containing
493      * the result of getPrimaryKeys
494      */
495     public static void SQLPRIMARYKEYS (String catalogName, String schemaName, String tableName, String options, ResultSet [] rs)
496         throws SQLException
497     {
498         rs[0] = getDMD().getPrimaryKeys(catalogName, schemaName, tableName);
499     }
500
501     /**
502      * Map SQLGetTypeInfo to EmbedDatabaseMetaData.getTypeInfo
503      *
504      * @param dataType SYSIBM.SQLGetTypeInfo DataType smallint,
505      * @param options SYSIBM.SQLGetTypeInfo Options varchar(4000))
506      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
507      * version of this procedure.
508      * @param rs output parameter, the resultset object containing the
509      * result of getTypeInfo
510      */
511     public static void SQLGETTYPEINFO (short dataType, String options, ResultSet [] rs)
512         throws SQLException
513     {
514         rs[0] = isForODBC(options)
515             ? ((EmbedDatabaseMetaData)getDMD()).getTypeInfoForODBC()
516             : getDMD().getTypeInfo();
517     }
518
519     /**
520      * Map SQLStatistics to EmbedDatabaseMetaData.getIndexInfo
521      *
522      * @param catalogName SYSIBM.SQLStatistics CatalogName varchar(128),
523      * @param schemaName SYSIBM.SQLStatistics SchemaName varchar(128),
524      * @param tableName SYSIBM.SQLStatistics TableName varchar(128),
525      * @param unique SYSIBM.SQLStatistics Unique smallint; 0=SQL_INDEX_UNIQUE(0); 1=SQL_INDEX_ALL(1),
526      * @param approximate SYSIBM.SQLStatistics Approximate smallint; 1=true; 0=false,
527      * @param options SYSIBM.SQLStatistics Options varchar(4000))
528      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
529      * version of this procedure.
530      * @param rs output parameter, the resultset object containing
531      * the result of getIndexInfo
532      */
533     public static void SQLSTATISTICS (String catalogName, String schemaName, String tableName,
534                                         short unique, short approximate, String options, ResultSet [] rs)
535         throws SQLException
536     {
537         boolean boolUnique = (unique == 0) ? true: false;
538         boolean boolApproximate = (approximate == 1) ? true: false;
539             
540         rs[0] = isForODBC(options)
541             ? ((EmbedDatabaseMetaData)getDMD()).getIndexInfoForODBC(
542                 catalogName, schemaName, tableName, boolUnique, boolApproximate)
543             : getDMD().getIndexInfo(catalogName, schemaName, tableName, boolUnique, boolApproximate);
544     }
545
546     /**
547      * Map SQLSpecialColumns to EmbedDatabaseMetaData.getBestRowIdentifier and getVersionColumns
548      *
549      * @param colType SYSIBM.SQLSpecialColumns ColType smallint,
550      * where 1 means getBestRowIdentifier and 2 getVersionColumns was called.
551      * @param catalogName SYSIBM.SQLSpecialColumns CatalogName varchar(128),
552      * @param schemaName SYSIBM.SQLSpecialColumns SchemaName varchar(128),
553      * @param tableName SYSIBM.SQLSpecialColumns TableName varchar(128),
554      * @param scope SYSIBM.SQLSpecialColumns Scope smallint,
555      * @param nullable SYSIBM.SQLSpecialColumns Nullable smallint; 0=false, 1=true,
556      * @param options SYSIBM.SQLSpecialColumns Options varchar(4000))
557      * If options contains the string 'DATATYPE='ODBC'', call the ODBC
558      * version of this procedure.
559      * @param rs output parameter, the resultset object containing
560      * the result of the DatabaseMetaData call
561      */
562     public static void SQLSPECIALCOLUMNS (short colType, String catalogName, String schemaName, String tableName,
563                                         short scope, short nullable, String options, ResultSet [] rs)
564         throws SQLException
565     {
566
567         boolean boolNullable = (nullable == 1) ? true: false;
568         if (colType == SQL_BEST_ROWID)
569         {
570             rs[0] = isForODBC(options)
571                 ? ((EmbedDatabaseMetaData)getDMD()).getBestRowIdentifierForODBC(
572                     catalogName, schemaName, tableName, scope, boolNullable)
573                 : getDMD().getBestRowIdentifier(catalogName, schemaName, tableName, scope, boolNullable);
574         }
575         else // colType must be SQL_ROWVER
576 {
577             rs[0] = isForODBC(options)
578                 ? ((EmbedDatabaseMetaData)getDMD()).getVersionColumnsForODBC(
579                     catalogName, schemaName, tableName)
580                 : getDMD().getVersionColumns(catalogName, schemaName, tableName);
581         }
582     }
583
584     /**
585      * Map SQLUDTS to EmbedDatabaseMetaData.getUDTs
586      *
587      * @param catalogName SYSIBM.SQLUDTS CatalogName varchar(128),
588      * @param schemaPattern SYSIBM.SQLUDTS Schema_Name_Pattern varchar(128),
589      * @param typeNamePattern SYSIBM.SQLUDTS Type_Name_Pattern varchar(128),
590      * @param udtTypes SYSIBM.SQLUDTS UDTTypes varchar(128),
591      * @param options SYSIBM.SQLUDTS Options varchar(4000))
592      * @param rs output parameter, the resultset object containing
593      * the result of getUDTs, which will be empty
594      */
595     public static void SQLUDTS (String catalogName, String schemaPattern, String typeNamePattern,
596                                         String udtTypes, String options, ResultSet [] rs)
597         throws SQLException
598     {
599
600         int[] types = null;
601         
602         if( udtTypes != null && udtTypes.length() > 0)
603         {
604             StringTokenizer tokenizer = new StringTokenizer ( udtTypes, " \t\n\t,");
605             int udtTypeCount = tokenizer.countTokens();
606             types = new int[ udtTypeCount];
607             String udtType = "";
608             try
609             {
610                 for( int i = 0; i < udtTypeCount; i++)
611                 {
612                     udtType = tokenizer.nextToken();
613                     types[i] = Integer.parseInt( udtType);
614                 }
615             }
616             catch( NumberFormatException nfe)
617             {
618                 throw new SQLException ( "Invalid type, " + udtType + ", passed to getUDTs.");
619             }
620             catch( NoSuchElementException nsee)
621             {
622                 throw new SQLException ( "Internal failure: NoSuchElementException in getUDTs.");
623             }
624         }
625         rs[0] = getDMD().getUDTs(catalogName, schemaPattern, typeNamePattern, types);
626     }
627
628     /*
629      * Map SYSIBM.METADATA to appropriate EmbedDatabaseMetaData methods
630      * for now, using the sps in org.apache.derby.iapi.db.jdbc.datadictionary.metadata_net.properties
631      *
632      */
633     public static void METADATA (ResultSet [] rs)
634         throws SQLException
635     {
636         rs[0] = ((EmbedDatabaseMetaData) getDMD()).getClientCachedMetaData();
637     }
638
639     /**
640      * Helper for ODBC metadata calls.
641      * @param options String containig the options to search through.
642      * @return True if options contain ODBC indicator; false otherwise.
643      */
644     private static boolean isForODBC(String options) {
645
646         String optionValue = getOption(DRIVER_TYPE_OPTION, options);
647         return ((optionValue != null) && optionValue.toUpperCase().equals(ODBC_DRIVER_OPTION));
648
649     }
650
651     /**
652      * Set/delete the value of a property of the database in current connection.
653      * <p>
654      * Will be called as SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY.
655      *
656      * @param key The property key.
657      * @param value The new value, if null the property is deleted.
658      *
659      * @exception StandardException Standard exception policy.
660      **/
661     public static void SYSCS_SET_DATABASE_PROPERTY(
662     String key,
663     String value)
664         throws SQLException
665     {
666         PropertyInfo.setDatabaseProperty(key, value);
667     }
668
669     /**
670      * Get the value of a property of the database in current connection.
671      * <p>
672      * Will be called as SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY.
673      *
674      * @param key The property key.
675      *
676      * @exception StandardException Standard exception policy.
677      **/
678     public static String SYSCS_GET_DATABASE_PROPERTY(
679     String key)
680         throws SQLException
681     {
682         return(PropertyInfo.getDatabaseProperty(key));
683     }
684
685     /**
686      * Compress the table.
687      * <p>
688      * Calls the "alter table compress {sequential}" sql. This syntax
689      * is not db2 compatible so it mapped by a system routine. This
690      * routine will be called when an application calls:
691      *
692      * SYSCS_UTIL.SYSCS_COMPRESS_TABLE
693      * <p>
694      *
695      * @param schema schema name of the table to compress. Must be
696      * non-null, no default is used.
697      * @param tablename table name of the table to compress. Must be
698      * non-null.
699      * @param sequential if non-zero then rebuild indexes sequentially,
700      * if 0 then rebuild all indexes in parallel.
701      *
702      * @exception StandardException Standard exception policy.
703      **/
704     public static void SYSCS_COMPRESS_TABLE(
705     String schema,
706     String tablename,
707     int sequential)
708         throws SQLException
709     {
710
711         String query =
712             "alter table " + "\"" + schema + "\"" + "." + "\"" + tablename + "\"" +
713             " compress" + (sequential != 0 ? " sequential" : "");
714
715         Connection conn = getDefaultConn();
716
717         conn.prepareStatement(query).executeUpdate();
718
719         conn.close();
720     }
721
722     /**
723      * Freeze the database.
724      * <p>
725      * Call internal routine to freeze the database so that a backup
726      * can be made.
727      *
728      * @exception StandardException Standard exception policy.
729      **/
730     public static void SYSCS_FREEZE_DATABASE()
731         throws SQLException
732     {
733         Factory.getDatabaseOfConnection().freeze();
734     }
735
736     /**
737      * Unfreeze the database.
738      * <p>
739      * Call internal routine to unfreeze the database, which was "freezed"
740      * by calling SYSCS_FREEZE_DATABASE().
741      * can be made.
742      *
743      * @exception StandardException Standard exception policy.
744      **/
745     public static void SYSCS_UNFREEZE_DATABASE()
746         throws SQLException
747     {
748         Factory.getDatabaseOfConnection().unfreeze();
749     }
750
751     public static void SYSCS_CHECKPOINT_DATABASE()
752         throws SQLException
753     {
754         Factory.getDatabaseOfConnection().checkpoint();
755     }
756
757     /**
758      * Backup the database to a backup directory.
759      *
760      * This procedure will throw error, if there are any unlogged
761      * operation executed in the same transaction backup is started.
762      * If there any unlogged operations in progess in other transaction, it
763      * will wait until those transactions are completed before starting the backup.
764      *
765      * Examples of unlogged operations include: create index and bulk insert.
766      * Note that once the backup begins these operations will not block,
767      * instead they are automatically converted into logged operations.
768      *
769      * @param backupDir the name of the directory where the backup should be
770      * stored. This directory will be created if it
771      * does not exist.
772      * @exception StandardException thrown on error
773      */
774     public static void SYSCS_BACKUP_DATABASE(String backupDir)
775         throws SQLException
776     {
777         Factory.getDatabaseOfConnection().backup(backupDir, true);
778     }
779
780     /**
781      * Backup the database to a backup directory.
782      *
783      * This procedure will throw error, if there are any uncommitted unlogged
784      * operation before stating the backup. It will not wait for the unlogged
785      * operations to complete.
786      *
787      * Examples of unlogged operations include: create index and bulk insert.
788      * Note that once the backup begins these operations will not block,
789      * instead they are automatically converted into logged operations.
790      *
791      * @param backupDir the name of the directory where the backup should be
792      * stored. This directory will be created if it
793      * does not exist.
794      * @exception StandardException thrown on error
795      */
796     public static void SYSCS_BACKUP_DATABASE_NOWAIT(String backupDir)
797         throws SQLException
798     {
799         Factory.getDatabaseOfConnection().backup(backupDir, false);
800     }
801
802
803     /**
804      * Backup the database to a backup directory and enable the log archive
805      * mode that will keep the archived log files required for roll-forward
806      * from this version of the backup.
807      *
808      * This procedure will throw error if there are any unlogged
809      * operation executed in the same transaction backup is started.
810      * If there any unlogged operations in progess in other transaction, it
811      * will wait until those transactions are completed before starting the backup.
812      *
813      * Examples of unlogged operations include: create index and bulk insert.
814      * Note that once the backup begins these operations will not block,
815      * instead they are automatically converted into logged operations.
816      *
817      * @param backupDir the name of the directory where the backup should be
818      * stored. This directory will be created if not it
819      * does not exist.
820      * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes online
821      * archived log files that exist before this backup, delete
822      * will occur only after the backup is complete.
823      * @exception StandardException thrown on error.
824      */
825     public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
826     String backupDir,
827     int deleteOnlineArchivedLogFiles)
828         throws SQLException
829     {
830
831         Factory.getDatabaseOfConnection().backupAndEnableLogArchiveMode(
832                 backupDir,
833                 (deleteOnlineArchivedLogFiles != 0),
834                 true);
835     }
836
837     /**
838      * Backup the database to a backup directory and enable the log archive
839      * mode that will keep the archived log files required for roll-forward
840      * from this version backup.
841      *
842      * This procedure will throw error, if there are any uncommitted unlogged
843      * operation before stating the backup. It will not wait for the unlogged
844      * operations to complete.
845      *
846
847      * Examples of unlogged operations include: create index and bulk insert.
848      * Note that once the backup begins these operations will not block,
849      * instead they are automatically converted into logged operations.
850      *
851      * @param backupDir the name of the directory where the backup should be
852      * stored. This directory will be created if not it
853      * does not exist.
854      *
855      * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes online
856      * archived log files that exist before this backup, delete
857      * will occur only after the backup is complete.
858      *
859      * @exception StandardException thrown on error.
860      */
861     public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT(
862     String backupDir,
863     int deleteOnlineArchivedLogFiles)
864         throws SQLException
865     {
866
867         Factory.getDatabaseOfConnection().backupAndEnableLogArchiveMode(
868                 backupDir,
869                 (deleteOnlineArchivedLogFiles != 0),
870                 false);
871     }
872
873
874     /**
875      * Disables the log archival process, i.e No old log files
876      * will be kept around for a roll-forward recovery.
877      *
878      * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes all the
879      * online archived log files that exist before this call immediately.
880      *
881      * @exception StandardException Thrown on error
882      */
883
884     public static void SYSCS_DISABLE_LOG_ARCHIVE_MODE(
885     int deleteOnlineArchivedLogFiles)
886         throws SQLException
887     {
888         Factory.getDatabaseOfConnection().disableLogArchiveMode(
889                 (deleteOnlineArchivedLogFiles != 0));
890     }
891
892
893     public static void SYSCS_SET_RUNTIMESTATISTICS(
894     int enable)
895         throws SQLException
896     {
897         ConnectionUtil.getCurrentLCC().setRunTimeStatisticsMode(enable != 0 ? true : false);
898     }
899
900     public static void SYSCS_SET_STATISTICS_TIMING(
901     int enable)
902         throws SQLException
903     {
904         ConnectionUtil.getCurrentLCC().setStatisticsTiming(enable != 0 ? true : false);
905     }
906
907     public static int SYSCS_CHECK_TABLE(
908     String schema,
909     String tablename)
910         throws SQLException
911     {
912         boolean ret_val =
913             org.apache.derby.iapi.db.ConsistencyChecker.checkTable(
914                 schema, tablename);
915
916         return(ret_val ? 1 : 0);
917     }
918
919     public static void SYSCS_INPLACE_COMPRESS_TABLE(
920     String schema,
921     String tablename,
922     int purgeRows,
923     int defragementRows,
924     int truncateEnd)
925         throws SQLException
926     {
927         org.apache.derby.iapi.db.OnlineCompress.compressTable(
928             schema,
929             tablename,
930             (purgeRows == 1),
931             (defragementRows == 1),
932             (truncateEnd == 1));
933
934         return;
935     }
936
937     public static String SYSCS_GET_RUNTIMESTATISTICS()
938         throws SQLException
939     {
940
941         Object rts = ConnectionUtil.getCurrentLCC().getRunTimeStatisticsObject();
942
943         if (rts == null)
944             return null;
945
946         return rts.toString();
947
948     }
949
950
951     /*
952     ** SQLJ Procedures.
953     */
954
955     /**
956         Install a jar file in the database.
957
958         SQLJ.INSTALL_JAR
959
960         @param url URL of the jar file to be installed in the database.
961         @param jar SQL name jar will be installed as.
962         @param deploy Ignored.
963
964         @exception SQLException Error installing jar file.
965     */
966     public static void INSTALL_JAR(String url, String jar, int deploy)
967         throws SQLException {
968
969         try {
970
971             String [] st = IdUtil.parseQualifiedName(jar.trim(), true);
972
973             String schemaName = null;
974             String sqlName = null;
975
976             switch (st.length) {
977             case 1:
978                 schemaName = null;
979                 sqlName = st[0];
980                 break;
981             case 2:
982                 schemaName = st[0];
983                 sqlName = st[1];
984             default:
985                 ; // RESOLVE
986 }
987
988             checkJarSQLName(sqlName);
989             JarDDL.add(schemaName, sqlName, url);
990         }
991         catch (StandardException se) {
992             throw PublicAPI.wrapStandardException(se);
993         }
994     }
995
996     /**
997         Replace a jar file in the database.
998
999         SQLJ.REPLACE_JAR
1000
1001        @param url URL of the jar file to be installed in the database.
1002        @param jar SQL name of jar to be replaced.
1003
1004        @exception SQLException Error replacing jar file.
1005    */
1006    public static void REPLACE_JAR(String url, String jar)
1007        throws SQLException {
1008
1009        try {
1010
1011            String [] st = IdUtil.parseQualifiedName(jar.trim(), true);
1012
1013            String schemaName = null;
1014            String sqlName = null;
1015
1016            switch (st.length) {
1017            case 1:
1018                schemaName = null;
1019                sqlName = st[0];
1020                break;
1021            case 2:
1022                schemaName = st[0];
1023                sqlName = st[1];
1024            default:
1025                ; // RESOLVE
1026 }
1027
1028            checkJarSQLName(sqlName);
1029            JarDDL.replace(schemaName, sqlName, url);
1030        }
1031        catch (StandardException se) {
1032            throw PublicAPI.wrapStandardException(se);
1033        }
1034    }
1035    /**
1036        Remove a jar file from the database.
1037
1038        @param jar SQL name of jar to be replaced.
1039        @param undeploy Ignored.
1040
1041        @exception SQLException Error removing jar file.
1042    */
1043    public static void REMOVE_JAR(String jar, int undeploy)
1044        throws SQLException {
1045
1046        try {
1047
1048            String [] st = IdUtil.parseQualifiedName(jar.trim(), true);
1049
1050            String schemaName = null;
1051            String sqlName = null;
1052
1053            switch (st.length) {
1054            case 1:
1055                schemaName = null;
1056                sqlName = st[0];
1057                break;
1058            case 2:
1059                schemaName = st[0];
1060                sqlName = st[1];
1061            default:
1062                ; // RESOLVE
1063 }
1064
1065            checkJarSQLName(sqlName);
1066
1067            JarDDL.drop(schemaName, sqlName);
1068        }
1069        catch (StandardException se) {
1070            throw PublicAPI.wrapStandardException(se);
1071        }
1072    }
1073
1074    private static void checkJarSQLName(String sqlName)
1075        throws StandardException {
1076
1077        // weed out a few special cases that cause problems.
1078 if ( (sqlName.length() == 0)
1079            || (sqlName.indexOf(':') != -1)
1080            ) {
1081
1082            throw StandardException.newException(SQLState.ID_PARSE_ERROR);
1083        }
1084    }
1085
1086    /**
1087     * Export data from a table to given file.
1088     * <p>
1089     * Will be called by system procedure:
1090     * SYSCS_EXPORT_TABLE(IN SCHEMANAME VARCHAR(128),
1091     * IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) ,
1092     * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1093     * IN CODESET VARCHAR(128))
1094     * @exception StandardException Standard exception policy.
1095     **/
1096    public static void SYSCS_EXPORT_TABLE(
1097    String schemaName,
1098    String tableName,
1099    String fileName,
1100    String columnDelimiter,
1101    String characterDelimiter,
1102    String codeset)
1103        throws SQLException
1104    {
1105        Connection conn = getDefaultConn();
1106        Export.exportTable(conn, schemaName , tableName , fileName ,
1107                              columnDelimiter , characterDelimiter, codeset);
1108        //export finished successfully, issue a commit
1109 conn.commit();
1110    }
1111
1112    
1113    /**
1114     * Export data from a select statement to given file.
1115     * <p>
1116     * Will be called as
1117     * SYSCS_EXPORT_QUERY(IN SELECTSTATEMENT VARCHAR(32672),
1118     * IN FILENAME VARCHAR(32672) ,
1119     * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1120     * IN CODESET VARCHAR(128))
1121     *
1122     * @exception StandardException Standard exception policy.
1123     **/
1124    public static void SYSCS_EXPORT_QUERY(
1125    String selectStatement,
1126    String fileName,
1127    String columnDelimiter,
1128    String characterDelimiter,
1129    String codeset)
1130        throws SQLException
1131    {
1132        Connection conn = getDefaultConn();
1133        Export.exportQuery(conn, selectStatement, fileName ,
1134                               columnDelimiter , characterDelimiter, codeset);
1135        
1136        //export finished successfully, issue a commit
1137 conn.commit();
1138    }
1139
1140    /**
1141     * Import data from a given file to a table.
1142     * <p>
1143     * Will be called by system procedure as
1144     * SYSCS_IMPORT_TABLE(IN SCHEMANAME VARCHAR(128),
1145     * IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) ,
1146     * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1147     * IN CODESET VARCHAR(128), IN REPLACE SMALLINT)
1148     * @exception StandardException Standard exception policy.
1149     **/
1150    public static void SYSCS_IMPORT_TABLE(
1151    String schemaName,
1152    String tableName,
1153    String fileName,
1154    String columnDelimiter,
1155    String characterDelimiter,
1156    String codeset,
1157    short replace)
1158        throws SQLException
1159    {
1160        Connection conn = getDefaultConn();
1161        try{
1162            Import.importTable(conn, schemaName , tableName , fileName ,
1163                                   columnDelimiter , characterDelimiter, codeset, replace);
1164        }catch(SQLException se)
1165        {
1166            //issue a rollback on any errors
1167 conn.rollback();
1168            throw se;
1169        }
1170        //import finished successfull, commit it.
1171 conn.commit();
1172    }
1173
1174
1175    /**
1176     * Import data from a given file into the specified table columns from the
1177     * specified columns in the file.
1178     * <p>
1179     * Will be called as
1180     * SYSCS_IMPORT_DATA (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128),
1181     * IN INSERTCOLUMNLIST VARCHAR(32762), IN COLUMNINDEXES VARCHAR(32762),
1182     * IN FILENAME VARCHAR(32762), IN COLUMNDELIMITER CHAR(1),
1183     * IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128),
1184     * IN REPLACE SMALLINT)
1185     *
1186     * @exception StandardException Standard exception policy.
1187     **/
1188    public static void SYSCS_IMPORT_DATA(
1189    String schemaName,
1190    String tableName,
1191    String insertColumnList,
1192    String columnIndexes,
1193    String fileName,
1194    String columnDelimiter,
1195    String characterDelimiter,
1196    String codeset,
1197    short replace)
1198        throws SQLException
1199    {
1200        Connection conn = getDefaultConn();
1201        try{
1202            Import.importData(conn, schemaName , tableName ,
1203                                  insertColumnList, columnIndexes, fileName,
1204                                  columnDelimiter, characterDelimiter,
1205                                  codeset, replace);
1206        }catch(SQLException se)
1207        {
1208            //issue a rollback on any errors
1209 conn.rollback();
1210            throw se;
1211        }
1212
1213        //import finished successfull, commit it.
1214 conn.commit();
1215    }
1216
1217    /**
1218     * Perform bulk insert using the specificed vti .
1219     * <p>
1220     * Will be called as
1221     * SYSCS_BULK_INSERT (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128),
1222     * IN VTINAME VARCHAR(32762), IN VTIARG VARCHAR(32762))
1223     *
1224     * @exception StandardException Standard exception policy.
1225     **/
1226    public static void SYSCS_BULK_INSERT(
1227    String schemaName,
1228    String tableName,
1229    String vtiName,
1230    String vtiArg
1231    )
1232        throws SQLException
1233    {
1234        Connection conn = getDefaultConn();
1235        
1236        String entityName = (schemaName == null ? tableName : schemaName + "." + tableName);
1237        String binsertSql =
1238            "insert into " + entityName +
1239            " --DERBY-PROPERTIES insertMode=bulkInsert \n" +
1240            "select * from new " + vtiName +
1241            "(" +
1242            "'" + schemaName + "'" + ", " +
1243            "'" + tableName + "'" + ", " +
1244            "'" + vtiArg + "'" + ")" +
1245            " as t";
1246
1247        PreparedStatement ps = conn.prepareStatement(binsertSql);
1248        ps.executeUpdate();
1249        ps.close();
1250    }
1251    
1252    /**
1253     * Method to return the constant PI.
1254     * SYSFUN.PI().
1255     * @return PI
1256     */
1257    public static double PI()
1258    {
1259        return StrictMath.PI;
1260    }
1261    
1262    /**
1263     * Constant for natural log(10).
1264     */
1265    private static final double LOG10 = StrictMath.log(10.0d);
1266    
1267    /**
1268     * Base 10 log function. SYSFUN.LOG10
1269     * Calculated by
1270     * <code>
1271     * log(value) / log(10)
1272     * </code>
1273     * where log is the natural log.
1274     */
1275    public static double LOG10(double value)
1276    {
1277        return StrictMath.log(value) / LOG10;
1278    }
1279}
1280

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags