Усовершенствованные механизмы работы с UDR-процедурами в IBM Informix Dynamic Server V10

Опции документа Опции документа, требующие включения JavaScript, не отображаются Обсудить Выскажите мнение об этой странице Помогите нам улучшить содержание

Уровень сложности: средний

Винаяк Шенои, инженер-консультант по программному обеспечению, IBM

08.06.2007

В статье рассматриваются усовершенствования поддержки UDR (user-defined routine – определяемая пользователем процедура), реализованные в сервере данных Informix® Dynamic Server:

• Множественные параметры OUT (Dynamic Server версии 9.4 и выше)
• Множественные параметры INOUT (Dynamic Server версии 10.00 и выше)
• Именованные возвращаемые параметры (Dynamic Server версии 9.4 и выше)

Новые возможности проиллюстрированы приведенными в статье законченными, работающими и готовыми к использованию примерами, которые помогают реализовать возврат нескольких значений из UDR-процедур и разработку переносимых приложений.

Краткое введение в UDR-процедуры

Informix Dynamic Server (IDS) предоставляет обширный набор возможностей расширения, которые позволяют прикладным программистам повышать функциональность решения посредством встраивания специально написанных приложений в сервер баз данных. Возможности расширения IDS основаны на поддержке определяемых пользователем процедур (UDR-процедур). Начиная с ранних версий поколения IDS V9 поддерживаются UDR-процедуры, написанные на различных языках, а именно: SPL, C и Java™. UDR-процедуры на языке SPL (SPL UDR) естественным образом поддерживаются сервером Dynamic Server как унаследованные хранимые процедуры, в то время как UDR-процедуры на языках C и Java рассматриваются как программы на внешнем языке. UDR-процедуры на языке C могут быть реализованы с помощью интерфейса программирования IDS Datablade API, а UDR-процедуры на языке Java (Java UDR) могут быть реализованы с помощью интерфейса программирования Server Side JDBC API.

Параметры OUT/INOUT

Важнейшее различие между UDR-процедурами на языке SPL и UDR-процедурами на других языках состоит в том, что программа SPL UDR может возвращать несколько значений, а другие UDR-процедуры могут возвращать только одно значение. Это различие обуславливается естественными языковыми ограничениями. Чтобы преодолеть указанные ограничения, несколько возвращаемых значений можно объединить в свойственный применяемому языку структурный тип, который затем отображается в определяемый пользователем тип данных (User Defined Type, UDT) сервера IDS.

Хотя подход с использованием UDT сравнительно прост в реализации, для преодоления указанных ограничений существует еще более простой способ. Именно здесь проявляются истинные возможности параметров OUT и INOUT. Параметры OUT и INOUT обеспечивают переносимый и эффективный способ возвращения нескольких значений из UDR-процедуры. СУБД других поставщиков также поддерживают эти типы параметров, что упрощает миграцию приложений на IDS.

До версии IDS V9.40 поддержка параметров OUT и INOUT была ограниченной, а именно:

• Допускался только один параметр OUT.
• Параметр OUT должен был быть последним параметром в списке аргументов UDR-процедуры.
• Параметры INOUT не поддерживались.

В версии IDS V9.40 была дополнительно реализована поддержка нескольких параметров OUT. Начиная с версии V10.00 сервер Dynamic Server поддерживает в UDR-процедурах несколько параметров OUT и INOUT. Параметры OUT и INOUT могут находиться в любом месте списка аргументов, а общее количество таких аргументов ограничено только индивидуальными ограничениями на аргументы конкретной UDR-процедуры. Кроме того, эти параметры могут иметь любой тип, включая тип UDT и составные типы.

Прежде чем двигаться дальше, рассмотрим различные типы аргументов UDR и тонкие различия между ними. В табл. 1 приведены различные типы аргументов UDR и их отличия друг от друга.

Исключительно мощный вариант использования параметров OUT реализован в модуле Excalibur Text Search (ETX) DataBlade. Модуль ETX DataBlade поддерживает изощренные возможности индексирования текста и поиска. ETX предоставляет для сопоставления текста собственный оператор etx_contains(), подобный LIKE или MATCHES. Оператор etx_contains() использует параметр OUT типа integer для указания относительной степени совпадения с шаблоном. Этот параметр OUT возвращает целое число, при этом чем больше величина этого числа, тем лучше совпадение с шаблоном или степень доверия к результатам. Более подробную информацию можно получить в документе Модуль Excalibur Text Search DataBlade. Руководство пользователя

Настройка среды для исполнения примеров

В этом разделе приведены примеры использования параметров OUT и INOUT в сервере данных IDS. Это работающие примеры, которые вы можете легко видоизменить в соответствии с собственными потребностями.

Для исполнения указанных примеров необходимо следующее программное обеспечение:

• IDS 10.00.xC5 или более поздняя версия
• JDBC 3.00.JC3 или более поздняя версия
• J2SE 1.4 или более поздняя версия
• Компилятор языка C для компиляции программ UDR, написанных на C

Для настройки среды исполнения указанных примеров выполните следующие шаги:

1. Настройте экземпляр сервера IDS и сконфигурируйте его для исполнения UDR-процедур на Java. Порядок настройки экземпляра сервера IDS описан в документе IDS Adminstator's Guide and Reference (Руководство и справочник администратора IDS). Информация по конфигурированию UDR-процедур на Java и информация по их разработке приведена в Руководстве J/Foundation Guide.
2. Для установки демонстрационной базы данных выполните следующую команду:
   

   dbaccessdemo7 -log

   
   Демонстрационная база данных имеет имя stores_demo. Она содержит несколько таблиц, которые используются в примерах.

Цель примеров

Наша цель:

UDR-процедура, которая с помощью параметров OUT/INOUT возвращает имя, фамилию и номер клиента с самым крупным заказом на закупку, а также общую сумму покупки в долларах.

Пример UDR-процедуры на SPL

В листинге 1 приведен пример простого SQL-сценария, создающего процедуру best_customer_spl в базе данных stores_demo. Процедура best_customer_spl() имеет четыре параметра (каждый из которых является параметром OUT), которые возвращают имя, фамилию, номер клиента и общую сумму его покупок. В строках 2-5 демонстрируется синтаксис, посредством которого для аргументов UDR-процедуры на SPL в качестве режима параметра указывается OUT. В строках 7 и 8 показано, как задается значение параметров OUT с помощью простого оператора SELECT. Указатель FOREACH и оператор SELECT возвращают данные о клиенте с самым крупным заказом на закупку и заполняют значения параметра OUT с помощью конструкции INTO внутри SPL. Обратите внимание на простоту и при этом мощные возможности, обеспечиваемые использованием параметров OUT для возврата нескольких значений из UDR-процедуры.

Эту SPL-программу можно использовать одним из двух способов:

• С помощью JDBC-программы с использованием интерфейса CallableStatement
• С использованием локальных переменных SLV в команде dbaccess

Более подробная информация по выполнению UDR-процедур приведена в разделе Выполнение UDR-процедур с параметрами OUT/INOUT.

                
1  DROP PROCEDURE best_customer_spl;
2  CREATE PROCEDURE best_customer_spl (OUT customer_num integer,
3                                 OUT fname char(15),
4                                 OUT lname char(15),
5                                 OUT total_sales decimal(16,2)) RETURNS INTEGER;
6 	FOREACH cursor1 FOR 
7 	SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
8         			fname, lname, customer_num ,total_sales
9  FROM customer c, orders o, items i
10 WHERE c.customer_num=o.customer_num
11     	AND o.order_num = i.order_num
13 GROUP BY c.fname, c.lname, c.customer_num
14 ORDER BY 4 desc
15 END FOREACH;
16 RETURN 1;
17 END PROCEDURE;

UDR-процедуру на языке SPL, показанную в Листинге 1 можно легко изменить, использовав параметр INOUT вместо параметра OUT. Различия будут заключаться в следующем. Значение для параметра INOUT должно предоставляться программой, вызывающей UDR-процедуру, в то время как параметры OUT всегда передаются со значением NULL. Кроме того, значения параметра INOUT можно использовать в SPL-программе точно так же, как значения обычных параметров IN.

Пример UDR-процедуры на языке C

В Листинге 2 показан пример UDR-процедуры на языке C, которая функционально аналогична UDR-процедуре на SPL из предыдущего раздела. Эта программа использует вызовы через интерфейс IDS Datablade API выполняя запрос SELECT и присваивая значения параметрам OUT. Большая часть кода в этой UDR-процедуре на C представляет собой стандартный код, используемый прикладными программистами для выполнения SQL-запросов из UDR-процедур на C. Для данной статьи основное значение имеют строки 115 – 130, в которых по результатам выполнения SQL-запроса задается значение параметра OUT.

Обратите внимание на несколько важных моментов, относящихся к объявлению функции в UDR и к стилю передачи аргументов. Строки 7-10 –это значения параметров OUT, являющихся аргументами UDR-процедуры. Все эти значения передаются по ссылке; внутри UDR-процедуры на С это реализуется с помощью указателей языка C. Сервер IDS передает для параметров OUT значения SQL NULL, а присвоение соответствующих значений, отличных от NULL, осуществляет UDR-процедура.

В строках 115-118 осуществляется восстановление NULL-индикатора для параметра OUT в структуре FPARAM Это заставляет сервер IDS после выполнения UDR-процедуры обращаться с параметром OUT как с имеющим значение «не-NULL».

Строка 119 устанавливает значение параметра OUT для переменной customer_num которая в данном случае имеет тип SQL INTEGER. Обратите внимание, что для задания значения параметра OUT мы просто присвоили ему значение mi_integer.

Примечание: Общее эмпирическое правило – присвоить указателю параметра OUT надлежащим образом размещенное значение IDS Datablade API эквивалентного типа.

В строках 120-129 устанавливаются значения OUT-параметров для имени и фамилии клиента. Поскольку имя и фамилия имеют символьный тип, их обрабатывают не так, как тип INTEGER. Строка 120 сначала выделяет буфер, в котором будет содержаться значение параметра OUT. Строка 121 копирует результат оператора SELECT в этот только что выделенный буфер. Затем в строках 122 и 123 осуществляется присвоение параметру OUT содержимого буфера и длины буфера. Фактически эти строки задают значение параметра OUT посредством изменения соответствующих указателей. Для получения детальной информации по функциональности операторов mi_set_vardata() и mi_set_varlen() обратитесь к Руководству IDS Datablade API Manual. Аналогичный процесс можно использовать для других символьных или строковых типов, например, LVARCHAR, NVARCHAR и т.д.

В строке 130 задается значение последнего параметра OUT, который имеет тип SQL DECIMAL. С помощью вызова deccopy() мы создаем копию значения mi_decimal возвращаемого из оператора SELECT, и присваиваем это значение указателю параметра OUT.

Приведенный выше код для UDR-процедуры на C можно легко адаптировать, использовав параметры INOUT вместо параметров OUT. Параметры INOUT определяются и используются аналогично параметрам OUT. Единственная разница состоит в том, что значения параметра INOUT доступны для использования внутри UDR-процедуры, в то время как значения параметра OUT задаются сервером как NULL. В данном примере, если бы переменная customer_num была параметром INOUT, то then *customer_num было бы указателем на значение этого параметра INOUT, а остальной фрагмент кода для задания значения был бы аналогичен коду, показанному в строке 119.

                
1     #include <stdio.h>
2     #include "milib.h"
3 
4     /* INOUT parameters in the middle of parameters list */
5 
6     mi_integer best_customer_c(
7         mi_integer *customer_num,  /* OUT parameter */
8         mi_string* fname,       /* OUT parameter */
9         mi_string* lname,       /* OUT parameter */
10        mi_decimal* total_sales ,  /* OUT parameter */
11        MI_FPARAM *fp)
12        {
13 
14             MI_CONNECTION   *conn;
15             char            cmd_buffer[512];
16             char            *cmd = NULL;
17             MI_STATEMENT    *stmt_hdl1 = NULL;
18             mi_integer      count, result;
19 
20             MI_ROW          *row;
21             MI_ROW_DESC     *rowdesc;
22             mi_integer      error;
23             mi_integer      numcols;
24             mi_integer      i;
25             mi_integer      res = 0;
26             mi_integer      *collen;
27             MI_DATUM        *colval;
28             MI_TYPE_DESC    **coltypedesc;
29             MI_MEMORY_DURATION lastduration;
30 
31 
32             if ((conn = mi_open(NULL, NULL, NULL)) == NULL){
33                 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Open database");
34             }
35 
36             sprintf(cmd_buffer,
37                   "SELECT FIRST 1 c.fname, c.lname, c.customer_num, SUM(total_price) \
38                    FROM customer c, orders o, items i \
39                    WHERE c.customer_num=o.customer_num \
40                    AND o.order_num = i.order_num\
42                    GROUP BY c.fname, c.lname, c.customer_num \
43                    ORDER BY 4 desc “);
44 
45             if ((stmt_hdl1 = mi_prepare(conn,cmd_buffer, NULL)) == NULL)         {
46                 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Prepare Query");
47             }
48 
49             if ((result = mi_exec_prepared_statement(stmt_hdl1, MI_BINARY, 0,
50                             0, NULL, 0, 0, NULL, 0, NULL)) == MI_ERROR)
51             {
52                 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Prepare Query");
53             }
54 
55             while ((result = mi_get_result(conn)) != MI_NO_MORE_RESULTS)
56             {
57                 switch(result)
58                 {
59                     case MI_ERROR:
60                         mi_db_error_raise( NULL, MI_FATAL,
61                                 "Fatal Error : Cannot Get Query Results");
62                     case MI_DDL:   /* fall through */
63                     case MI_DML:
64                         cmd = mi_result_command_name(conn);
65                         if ((count = mi_result_row_count(conn)) == MI_ERROR)
66                         {
67                             mi_db_error_raise( NULL, MI_FATAL,
68                                     "Fatal Error : Cannot Get Row Counts");
69                         }
70                         else
71                             break;
72                     case MI_ROWS:
73                         /* for first row */
74                         if ((row = mi_next_row(conn, &error)) != NULL) {
75                             rowdesc = mi_get_row_desc_without_row(conn);
76                             numcols = mi_column_count(rowdesc);
77 
78                             colval = mi_alloc(sizeof(MI_DATUM) * numcols);
79                             collen = mi_alloc(sizeof(mi_integer) * numcols);
80                             coltypedesc = mi_alloc(sizeof(MI_TYPE_DESC *) * numcols);
81 
82                             for (i = 0; i > numcols; i++) {
83                                 res = mi_value(row, i, &colval[i], &collen[i]);
84                                 coltypedesc[i] = mi_column_typedesc(rowdesc, i);
85 
86                                 switch(res) {
87                                     case MI_ERROR:
88                                         mi_db_error_raise( NULL, MI_FATAL,
89                                                 "Fatal Error : Unknown Results");
90                                     case MI_NULL_VALUE:
91                                         mi_fp_setargisnull(fp, i, MI_TRUE);
92                                         break;
93                                     case MI_NORMAL_VALUE:
94                                     case MI_COLLECTION_VALUE:
95                                     case MI_ROW_VALUE:
96                                         break;
97                                     default:
98                                         mi_db_error_raise( NULL, MI_FATAL,
99                                                 "Fatal Error : Unknown Results");
100                                 } /* switch */
101                             } /* for */
102                         }
103                         if (error == MI_ERROR) {
104                             mi_db_error_raise( NULL, MI_FATAL, "Fatal Er ror : Cannot 
						Get Row");
105                         }
106                         break;
107                     default:
108                         mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Unknown 
					Results");
110                 }
111             }
112 
113 
114             /* set OUT values as Non-NULL */
115             mi_fp_setargisnull(fp, 0, MI_FALSE);
116             mi_fp_setargisnull(fp, 1, MI_FALSE);
117             mi_fp_setargisnull(fp, 2, MI_FALSE);
118             mi_fp_setargisnull(fp, 3, MI_FALSE);
119             *customer_num = (mi_integer) colval[2];
120             if ((cmd = mi_alloc(collen[0])) == NULL)
121                 mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Cannot Get memory 
								for OUT parmeter");
122             bycopy((char*)mi_get_vardata((mi_lvarchar*)colval[0]), (char *)cmd, 
								collen[0]);
123             mi_set_varptr((mi_lvarchar*)fname,cmd);
124             mi_set_varlen((mi_lvarchar*)fname,collen[0]);
125             if ((cmd = mi_alloc(collen[1])) == NULL)
126                 mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Cannot Get memory 
								for OUT parameter");
127             bycopy((char*)mi_get_vardata((mi_lvarchar*)colval[1]), (char *)cmd, 
								collen[1]);
128             mi_set_varptr((mi_lvarchar*)lname,cmd);
129             mi_set_varlen((mi_lvarchar*)lname,collen[1]);
130             deccopy((mi_decimal*) colval[3], total_sales);
131 
132             if (mi_query_finish(conn) == MI_ERROR) {
133               mi_db_error_raise( NULL,MI_FATAL,"Fatal Error: Cannot Complete Query");
134             }
135 
136             if ( conn != NULL )
137                 mi_close(conn);
138 
139             return(1);
140         }

Компиляция представленной в Листинге 2 UDR-процедуры может быть осуществлена посредством процедуры, показанной в Листинге 3. В данном случае процедура соответствует 32-разрядной операционной системе (ОС) Solaris 2.8. Это стандартная процедура для компиляции кода UDR-процедуры на C и подготовки его к исполнению. Для получения информации по адаптации этой процедуры к другим ОС обратитесь к Руководству User Defined Routine Manual или IDS Datablade API.

                
1  cc -c -v -Xa  -K PIC -I$INFORMIXDIR/incl/dmi -I$INFORMIXDIR/incl/esql 
			-I$INFORMIXDIR/incl/public -DMI_SERVBUILD best_customer_c.c
2 
3  ld -G best_customer_c.o -o best_customer_c.udr
4
5  chmod 660 best_customer_c.udr
6  cp best_customer_c.udr $INFORMIXDIR/tmp

После того как в результате компиляции UDR-процедуры на C будет создан т.н. «совместно используемый объект» (shared object), необходимо создать реальную UDR-процедуру на C и зарегистрировать ее на сервере Dynamic Server. В Листинге 4 показан пример SQL-сценария для решения этой задачи. Строки 2-7 создают UDR-процедуру на C с четырьмя параметрами OUT. Замена OUT на INOUT переключает режим параметров на INOUT.

Обратите внимание на дополнительный оператор ALTER FUNCTION в строке 8. Это очень важный момент для всех UDR-процедур на C, которые должны обрабатывать значения SQL NULL, передаваемые извне в качестве параметров. Добавление к процедуре модификатора HANDLESNULL указывает серверу IDS, что он может безопасно передавать значения SQL NULL в UDR-процедуру на C, и что данная UDR-процедура способна интерпретировать такие значения. Как указывалось выше, параметры OUT всегда передаются как NULL, поэтому необходимо, чтобы UDR-процедура на C могла обрабатывать значения NULL.

                
1    DROP FUNCTION best_customer_c;

2    CREATE FUNCTION best_customer_c (OUT customer_num integer,
3                                   OUT fname char(15),
4                                   OUT lname char(15),
5                                   OUT total_sales decimal(12,2)) RETURNING INT  ;
6    EXTERNAL NAME '$INFORMIXDIR/tmp/best_customer_c.udr'
7    LANGUAGE C;
8    ALTER FUNCTION best_customer_c WITH (ADD HANDLESNULLS);

Исполнение UDR-процедуры рассмотрено в разделе Выполнение UDR-процедур с параметрами OUT/INOUT

Пример UDR-процедуры на Java

В Листинге 5 показан пример UDR-процедуры на Java, которая функционально эквивалентна UDR-процедурам из предыдущих разделов. В этом фрагменте кода нас интересуют строки 9-12 и 37-61, поскольку остальной код является обычным кодом JDBC DirectConnection, предназначенным для исполнения оператора SQL внутри UDR-процедуры на Java. Более подробную информацию об этом стандартном коде можно найти в руководствах JDBC Programmers Guide и J/Foundation Guide.

В строках 9-12 параметр OUT объявляется как одномерный массив типа Native Java, отображаемый из соответствующего SQL-типа. Отображение Java-типа в SQL-тип и наоборот подробно объясняется в упомянутых выше руководствах.

Строки 37-61 извлекают результаты из оператора SQL и присваивают параметрам OUT значения или Java-объекты. Первый элемент массива Java-эквивалента для параметра OUT содержит значение параметра OUT, которое подлежит передаче в сервер IDS.

Как и предыдущие примеры UDR-процедур, эту UDR-процедуру на Java можно легко адаптировать к использованию параметров INOUT. Первый элемент массива Java-эквивалента для параметра INOUT содержит значение параметра INOUT, передаваемое внутрь программы вызывающей программой. Код для задания значения параметра INOUT аналогичен коду в строках 37-61.

                
1     import java.lang.*;
2     import java.sql.*;
3     import java.math.*;
4 
5 
6     public class best_customer_j_class {
7 
8         public static int best_customer_j(
9              java.lang.Integer[] customer_num,
10             java.lang.String[] fname,
11             java.lang.String[] lname,
12             java.math.BigDecimal[] total_sales
13             )
14         throws SQLException
15         {
16             Connection conn = null;
17             try {
18             Class.forName("com.informix.jdbc.IfxDriver");
19             } catch (Exception e) {
20             throw new SQLException(e.toString());
21             }
22             conn = DriverManager.getConnection("jdbc:informix-direct");
23  
24             Statement stmt = conn.createStatement();
25             String query = "SELECT FIRST 1 c.fname, c.lname , "
26                                +    "c.customer_num, "
27                                +    "SUM(total_price) "
28                                + "FROM customer c, orders o, items i "
29                                + "WHERE c.customer_num=o.customer_num "
30                                +  "    AND o.order_num = i.order_num "
31                                + " GROUP BY c.fname, c.lname, c.customer_num"
32                                + "    ORDER BY 4 desc";
33 
34             System.out.println(query);
35             stmt.execute(query);
36             ResultSet rs = stmt.getResultSet();
37             if (rs.next()) {
38             if (rs.getObject(0) != null) {
39                 fname[0] =
40                 (java.lang.String)rs.getObject(0) ;
41             } else {
42                 fname[0] = null;
43             }
44             if (rs.getObject(1) != null) {
45                 lname[0] =
46                 (java.lang.String)rs.getObject(1) ;
47             } else {
48                 lname[0] = null;
49             }
50             if (rs.getObject(2) != null) {
51                 customer_num[0] =
52                 (java.lang.Integer)rs.getObject(2) ;
53             } else {
54                 customer_num[0] = null;
55             }
56             if (rs.getObject(3) != null) {
57                 total_sales[0] =
58                 (java.math.BigDecimal)rs.getObject(3) ;
59             } else {
60                 total_sales[0] = null;
61             }
62             }
63             rs.close();
64             return 1;
65         }

В Листинге 6 показана процедура, необходимая для компиляции UDR-процедуры на Java и создания jar-файла, который затем может быть зарегистрирован на сервере IDS. Эта процедура не отличается от процедуры компиляции обычного Java-приложения. Единственным отличием является необходимость соблюдения изложенных в Руководстве J/Foundation Guide рекомендаций по определению класса Java Class и наличию соответствующей переменной CLASSPATH для компиляции Java.

                
1   javac best_customer_j_class.java
2   jar cvf best_cust.jar best_customer_j_class.java
3   cp best_cust.jar $INFORMIXDIR/tmp

В Листинге 7 показан пример SQL-сценария, регистрирующего Jar-файл на сервере IDS и создающего на сервере IDS UDR-процедуру на Java с параметром OUT. Как и в предыдущем примере, замена ключевого слова OUT на INOUT переключает режим параметра на INOUT.

                
1   DROP FUNCTION best_customer_j;
2   EXECUTE PROCEDURE install_jar (
3         "file:$INFORMIXDIR/tmp/best_cust.jar","best_cust",0);
4   CREATE FUNCTION best_customer_j (OUT customer_num integer,
5                     OUT fname char(15),
6                     OUT lname char(15),
7                     OUT total_sales decimal(12,2)) RETURNING INT ;
8   EXTERNAL NAME 'best_cust:best_customer_j_class.best_customer_j(java.lang.Integer[], 
                                                 java.lang.String[], 
                                                 java.lang.String[], 
                                                 java.math.BigDecimal[])'
9   LANGUAGE JAVA;

10  ALTER FUNCTION best_customer_j WITH (ADD HANDLESNULLS);

Выполнение UDR-процедур с параметрами OUT/INOUT

IDS поддерживает два основных режима исполнения UDR-процедур с параметрами OUT.

• Программа JDBC-клиента с использованием JDBC-интерфейса CallableStatement
• С использованием переменных Statement Local Variable (SLV)

UDR-процедуры с параметрами INOUT могут исполняться только через JDBC-клиент, но не с использованием переменных SLV. Причина этого будет разъяснена ниже.

Сервер IDS не поддерживает исполнение UDR-процедур с параметрами OUT/INOUT посредством каких-либо других клиентов, отличных от упомянутых выше. Это объясняется тем, что оператор EXECUTE FUNCTION/PROCEDURE/ROUTINE не поддерживает UDR-процедуры с параметрами OUT/INOUT. Любая попытка выполнения UDR-процедуры с параметрами OUT/INOUT будет отклонена с кодом ошибки 9752. JDBC-клиент – это единственный клиент IDS, в состав которого входит специальный код для исполнения таких UDR-процедур на сервере IDS. По этой причине во всех примерах исполнения UDR-процедур в этой статье используется JDBC-клиент.

В Листинге 8 показана стандартная клиентская JDBC-программа, которая может быть использована для вызова UDR-процедур с параметрами OUT/INOUT. Показанный в этом примере код необходимо адаптировать в соответствии с переменными среды конкретного сервера Dynamic Server. Для успешного выполнения этой программы необходимо задать ссылки на HOSTNAME, PORTNO, INFORMIXSERVER, SERVERNAME, USERNAME и PASSWORD. В представленном в этом листинге примере осуществляется вызов UDR-процедуры на Java, описанной в предыдущих разделах. Не считая перекомпиляции, единственным отличием при исполнении UDR-процедуры на C или SPL была бы необходимость замены имени UDR-процедуры best_customer_j на имя соответствующей UDR-процедуры на C или SPL.

Строки 34-35 готовят объект CallableStatement для вызываемой UDR-процедуры. Строки 37-50 с помощью стандартных методов объекта CallableStatement регистрируют параметры OUT в JDBC-клиенте. Если бы какой-либо параметр имел тип INOUT вместо OUT, то программе JDBC перед исполнением нужно было бы задать значение этого параметра. В строке 41, помеченной как комментарий, показан пример задания значения для переменной customer_num для случая, когда это параметр типа INOUT.

В строках 64-74 показано, как извлекаются значения OUT-параметров из объекта CallableStatement после завершения исполнения UDR-процедуры. Рекомендуется сначала извлекать возвращаемый параметр UDR-процедуры, и лишь потом значения OUT-параметров.

                
1     import com.informix.jdbc.*;
2     import com.informix.lang.*;
3     import java.sql.*;
4     import java.util.*;
5     import java.math.*;
6 
7     public class query_best_cust
8     {
9 
10         public query_best_cust() { }
11 
12         public static void main(String args[]) {
13             Connection myConn = null;
14             try {
15                 Class.forName("com.informix.jdbc.IfxDriver");
16                 myConn = DriverManager.getConnection(
17                         "jdbc:informix-sqli:>HOSTNAME<:>PORTNO</stores_demo:"
18                         +"INFORMIXSERVER=>SERVERNAME<;user=>USERNAME<;"
19                         +"password=>PASSWORD<;");
20             }
21             catch (ClassNotFoundException e) {
22                 System.out.println(
23                         "problem with loading Ifx Driver\n" + e.getMessage());
24             }
25             catch (SQLException e) {
26                 System.out.println(
27                         "problem with connecting to db\n" + e.getMessage());
28             }
29
30             Connection conn = myConn;
31 
32             try
33             {
34                 String command = "{? = call best_customer_j(?, ?, ?, ?)}        ";
35                 CallableStatement cstmt = conn.prepareCall (command);
36 
37                 // Register customer_num INOUT parameter
38                 cstmt.registerOutParameter(1, Types.INTEGER);
39 
40                 // Pass in value for INOUT parameter if customer_num is INOUT
41                 // cstmt.setInt(1,10);
42 
43                 // Register fname OUT parameter
44                 cstmt.registerOutParameter(2, Types.CHAR);
45 
46                 // Register lname OUT parameter
47                 cstmt.registerOutParameter(3, Types.CHAR);
48 
49                 // Register total_sales OUT parameter
50                 cstmt.registerOutParameter(4, Types.DECIMAL);
51 
52                 // Execute udr
53                 ResultSet rs = cstmt.executeQuery();
54 
55                 // executeQuery returns values via a resultSet
56                 while (rs.next())
57                 {
58                     // get value returned by myudr
59                     int ret = rs.getInt(1);
60                     System.out.println("return value from UDR = " + ret)        ;
61                 }
62    
63                 // Retrieve OUT parameters from UDR
64                 int cnum = cstmt.getInt(1);
65                 System.out.println("customer_num OUT parameter value = " + cnum);
66    
67                 String fname = cstmt.getString(2);
68                 System.out.println("fname OUT parameter value = " + fname);
69    
70                 String lname = cstmt.getString(3);
71                 System.out.println("lname OUT parameter value = " + lname);
72    
73                 BigDecimal total_sales = cstmt.getBigDecimal(4);
74                 System.out.println("total_sales OUT parameter value = " 
                            + total_sales.doubleValue());
75    
76                 rs.close();
77                 cstmt.close();
78                 conn.close();
79             }
80             catch (SQLException e)
81             {
82                 System.out.println("SQLException: " + e.getMessage());
83                 System.out.println("ErrorCode: " + e.getErrorCode());
84                 e.printStackTrace();
85             }
86         }
87     }

Для компиляции приведенной выше JDBC-программы мы используем следующие шаги, исходя из допущения, что установлен драйвер JDBC-клиент и переменная среды CLASSPATH содержит необходимые элементы для компиляции любой программы Java/JDBC.

javac query_best_cust.java

После исполнения этой программы возвращается следующий результат.

> java query_best_cust

return value from UDR = 1
customer_num OUT parameter value = 106
fname OUT parameter value = George         
lname OUT parameter value = Watson         
total_sales OUT parameter value = 2856.0

Теперь, когда мы рассмотрели способ исполнения UDR-процедур через JDBC, перейдем к способу с использованием SLV. Хотя оператор EXECUTE FUNCTION/PROCEDURE/ROUTINE нельзя использовать для исполнения UDR-процедур с параметрами OUT/INOUT, сервер IDS поддерживает использование переменных SLV для извлечения значений OUT-параметров из UDR-процедур в структуре WHERE оператора SELECT. Переменная SLV передает параметры OUT из определенной пользователем функции в другие части оператора SQL.

В Листинге 9 приведен пример SQL-запроса, в котором для исполнения UDR-процедур, описанных в предыдущих разделах, используется переменная SLV. Эту технологию можно использовать для исполнения любых разновидностей UDR-процедур с единственным требованием – UDR-процедура должна возвращать значение. Это объясняется тем, что программа UDR вызывается как предикат для фильтрации результатов в структуре WHERE оператора SELECT. Кроме того, переменные SLV можно использовать для извлечения значений параметров OUT, но нельзя использовать для задания значений параметров INOUT. Поэтому для исполнения UDR-процедур с параметрами INOUT этот способ не подходит.

Используемый ниже оператор SELECT представляет собой запрос, возвращающий одну строку со значениями SLV. Значения SLV задаются и генерируются процедурой best_customer_spl() в структуре WHERE оператора SELECT. Обратите внимание на синтаксис SLV, который используется для определения значений параметра OUT. Эта синтаксическая конструкция состоит из трех частей и имеет вид <slvname>#<out parameter type>. Компоненту slvname можно использовать в списке проекций (projection list) или в любом другом фильтре, который вычисляется после фильтра UDR.

                
SELECT customer_num, fname, lname, total_sales
FROM systables
WHERE tabid =1 AND
    best_customer_spl (customer_num#integer ,
                   fname#char(15),
                   lname#char(15),
                   total_sales#money) = 1;

После исполнения оператора SELECT возвращается следующий результат.

dbaccess -e stores_demo slv.sql

Database selected.

SELECT customer_num, fname, lname, total_sales
FROM systables
WHERE tabid =1 AND
    best_customer_spl (customer_num#integer ,
                   fname#char(15),
                   lname#char(15),
                   total_sales#money) = 1;

customer_num 	fname           lname                total_sales 

         106 	George          Watson                  $2856.00

1 row(s) retrieved.

Database closed.

Мы видим, что реализация и исполнение UDR-процедур с параметрами OUT/INOUT достаточно просты. Поскольку используется стандартный JDBC-интерфейс, код приложения сохраняет высокую степень переносимости между разными серверами баз данных. Использование переменных SLV также является простым и мощным инструментом для исполнения UDR-процедур с параметрами OUT/INOUT.

Именованные возвращаемые параметры

До появления версии IDS V9.4 не существовало механизмов для именования возвращаемых параметров в UDR-процедурах на SPL. При исполнении такой UDR-процедуры команда dbaccess отображала имена возвращаемых значений в виде "(expression)". Далее показан SQL-сценарий и пример выходных данных UDR-процедуры на языке SPL с несколькими безымянными возвращаемыми значениями. Эта программа SPL UDR является адаптацией UDR-процедуры на SPL из Листинга 1, в которой параметры OUT преобразованы в обычные возвращаемые значения.

В Листинге 10 показан пример UDR-процедуры на SPL, которая для клиента с самым крупным заказом на закупку возвращает значения customer_num, first name, last name и total sales. В этом примере именованные возвращаемые параметры не используются, а в результатах, приведенных в Листинге 11, имена возвращаемых значений показаны в виде "(expression)". Это не делает результаты нечитаемыми, однако вынуждает программиста запоминать, какой смысл имеет каждое значение в контексте данного приложения.

                
1     DROP PROCEDURE best_customer_spl;
2     CREATE PROCEDURE best_customer_spl () RETURNS 
         INTEGER, CHAR(15), CHAR (15) , MONEY;
3     DEFINE customer_num INTEGER;
4     DEFINE fname, lname CHAR(15);
5     DEFINE total_sales money;
6     FOREACH cursor1 FOR
7     SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
8         fname, lname, customer_num ,total_sales
9     FROM customer c, orders o, items i
10    WHERE c.customer_num=o.customer_num
11           AND o.order_num = i.order_num
12    GROUP BY c.fname, c.lname, c.customer_num
13    ORDER BY 4 desc
14    END FOREACH;
15    RETURN  customer_num,fname, lname, total_sales;
16    END PROCEDURE;
17     
18    EXECUTE PROCEDURE best_customer_spl();

                
EXECUTE PROCEDURE best_customer_spl();

(expression) (expression)    (expression)        (expression) 

         106 George          Watson                  $2856.00

Этот же пример после применения именованных возвращаемых параметров будет иметь вид, показанный в Листинге 12. Обратите внимание, что в строках 3-6 возвращаемые параметры поименованы с использованием синтаксиса AS. Это не только повышает наглядность UDR-процедуры на SPL, но и снабжает возвращаемые параметры осмысленными именами для вывода результатов UDR-процедуры. В Листинге 13 показан новый улучшенный вывод данных с использованием имен возвращаемых параметров.

                
1     DROP PROCEDURE best_customer_spl;
2     CREATE PROCEDURE best_customer_spl () RETURNS 
3 		INTEGER AS customer_num, 
4		CHAR(15) AS fname, 
5 		CHAR (15) AS lname, 
6		MONEY AS total_sales;
7     DEFINE customer_num INTEGER;
8     DEFINE fname, lname CHAR(15);
9     DEFINE total_sales money;
10     FOREACH cursor1 FOR
11     SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
12         fname, lname, customer_num ,total_sales
13     FROM customer c, orders o, items i
14     WHERE c.customer_num=o.customer_num
15           AND o.order_num = i.order_num
16     GROUP BY c.fname, c.lname, c.customer_num
17     ORDER BY 4 desc
18     END FOREACH;
19     RETURN  customer_num,fname, lname, total_sales;
16     END PROCEDURE;
17     
18     EXECUTE PROCEDURE best_customer_spl();

                
EXECUTE PROCEDURE best_customer_spl();

customer_num	fname           lname                total_sales 

         106 	George          Watson                  $2856.00

Заключение

Реализованные в версии IDS V10 простые и вместе с тем мощные усовершенствования UDR-инфраструктуры предоставляют прикладным программистам платформу для создания переносимых приложений с более высокой степенью соответствия стандартам SQL. Кроме того, эти усовершенствования упрощают портирование приложений из СУБД других поставщиков на платформу IDS V10.

Благодарности

Я хотел бы выразить свою благодарность Кешава Мурти (Keshava Murthy), специалисту по SQL-архитектуре IDS, за технические консультации и помощь в анализе технических аспектов данной статьи, а также Дэвиду Оберштадту (David H. Oberstadt), специалисту по разработке информационных ресурсов для сервера IDS, за ценные комментарии к данной статье.

Ресурсы

• Оригинал статьи: UDR enhancements in IBM Informix Dynamic Server V10.
  
  
• Информационный центр IBM Informix Dynamic Server v10.00: Подробная информация о сервере данных Informix.
  
  
• Информационная страница IBM Informix: Информация о продуктах Informix
  
  
• Раздел Informix портала developerWorks: Статьи, учебные пособия и другие ресурсы для повышения квалификации по продуктам Informix.
  
  
• Дополнительная техническая литература по этой и другим темам.
  
  

• Загрузите бесплатную пробную версию Informix Dynamic Server.
  
  
• Загрузите пробное программное обеспечение IBM и приобретите опыт работы с инструментами разработки и продуктами связующего уровня семейств DB2®, Informix®, Lotus®, Rational®, Tivoli® и WebSphere®.
  
  

• Примите участие в обсуждении материала на форуме.
  
  
• Блоги developerWorks: Присоединяйтесь к сообществу developerWorks.
  
  
• Примите участие в форуме по Information Management.

Об авторе

		Винаяк Шенои является руководителем группы по SQL-разработкам для сервера данных IDS. Он занимается сервером данных IDS почти семь лет. Шенои участвовал в разработках в таких областях, как SQL, RTREE, Java, распределенные запросы и средства расширения IDS. Он разработал средства поддержки множественных OUT-параметров для версии IDS v9.4 и внес существенный вклад в разработку поддержки множественных INOUT-параметров параметра для версии IDS v10. В. Шенои имеет ученую степень магистра в области вычислительной техники, полученную в Университете штата Калифорния (Сакраменто).

Выскажите мнение об этой странице

Пожалуйста, найдите минутку и заполните форму, чтобы повысить уровень сервиса. Помогла ли данная информация достичь Вашей цели? Да Нет Не знаю   Пожалуйста, напишите комментарий, чтобы мы могли улучшить данную страницу:   Насколько полезна эта информация? (1 = Бесполезна, 5 = Очень полезна) 1 2 3 4 5

В начало