ur); i++) {
printf("Name: %s\n",sqlrcur_getColumnName(cur,i));
}
// column names will be forced to lower case
sqlrcur_lowerCaseColumnNames(cur);
sqlrcur_endQuery(cur,"select * from my_table");
sqlrcon_endSession(con);
for (i=0; i<sqlrcur_colCount(cur); i++) {
printf("Name: %s\n",sqlrcur_getColumnName(cur,i));
}
// column names will be the same as they are in the database
sqlrcur_mixedCaseColumnNames(cur);
sqlrcur_endQuery(cur,"select * from my_table");
sqlrcon_endSession(con);
for (i=0; i<sqlrcur_colCount(cur); i++) {
printf("Name: %s\n",sqlrcur_getColumnName(cur,i));
}
sqlrcur_free(cur);
sqlrcon_free(con);
}
Stored Procedures
Many databases support stored procedures. Stored procedures are sets of queries and procedural code that are executed inside of the database itself. For example, a stored procedure may select rows from one table, iterate through the result set and, based on the values in each row, insert, update or delete rows in other tables. A client program could do this as well, but a stored procedure is generally more efficient because queries and result sets don''''t have to be sent back and forth between the client and database. Also, stored procedures are generally stored in the database in a compiled state, while queries may have to be re-parsed and re-compiled each time they are sent. While many databases support stored procedures. The syntax for creating and executing stored procedures varies greatly between databases. SQL Relay supports stored procedures for most databases, but there are some caveats. Stored procedures are not currently supported when using FreeTDS against Sybase or Microsoft SQL Server. Blob/Clob bind variables are only supported in Oracle 8i or higher. Sybase stored procedures must use varchar output parameters. Stored procedures typically take input paramters from client programs through input bind variables and return values back to client programs either through bind variables or result sets. Stored procedures can be broken down into several categories, based on the values that they return. Some stored procedures don''''t return any values, some return a single value, some return multiple values and some return entire result sets. No Values
Some stored procedures don''''t return any values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. Oracle
To create the stored procedure, run a query like the following. create procedure testproc(in1 in number, in2 in number, in3 in varchar2) is
begin
insert into mytable values (in1,in2,in3);
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"begin testproc(:in1,:in2,:in3); end;");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
To drop the stored procedure, run a query like the following. drop procedure testproc
Sybase and Microsoft SQL Server
To create the stored procedure, run a query like the following. create procedure testproc @in1 int, @in2 float, @in3 varchar(20) as
insert into mytable values (@in1,@in2,@in3)
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"exec testproc");
sqlrcur_inputBindLong(cur,"in1",1);
sqlrcur_inputBindDouble(cur,"in2",1.1,2,1);
sqlrcur_inputBindString(cur,"in3","hello");
sqlrcur_executeQuery(cur);
To drop the stored procedure, run a query like the following. drop procedure testproc
Interbase and Firebird
To create the stored procedure, run a query like the following. create procedure testproc(in1 integer, in2 float, in3 varchar(20)) as
begin
insert into mytable values (in1,in2,in3);
suspend;
end;
To execute the stored procedure from an SQL Relay program, use code like the following. sqlrcur_prepareQuery(cur,"execute procedure testproc ?, ?, ?");
sqlrcur_inputBindLong(cur,"1",1);
sqlrcur_inputBindDouble(cur,"2",1.1,2,1);
sqlrcur_inputBindString(cur,"3","hello");
sqlrcur_executeQuery(cur);
To drop the stored procedure, run a query like the following. drop procedure testproc
DB2
To 上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] ... 下一页 >> |
