Current database connection and execution of database procedures and functions.

Version :
12.03.2024, 15:56 Uhr

Current database connection and execution of database procedures and functions. Transactions can also be executed using the current database connection, in this case use sql::query and the class query.

Query erfordert #include "internal/types.h"

static DBC dbconnection()

Get the current database connection. The database connection may never be deleted by means of a script command.

Name Type Default Description
Return DBC   Current database connection of InDesign® or 0


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

static DBC connection()

Get the current database connection. The database connection may never be deleted by means of a script command.

Name Type Default Description
Return DBC   Current database connection of InDesign® or 0

Version 1.2 (15 June 2005)

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

static DBC login(
  char* service = 0,
  char* user = 0,
  char* passwd = 0,
  char* dbname = 0,
  char* client = 0)

Create a database connection to a database service. If one of the specifications equals 0 (not ""!!), the login dialog will be opened. The result is in every case to be checked for 0. The received pointer must be deleted with sql::logout.

Name Type Default Description
Return DBC   Pointer to a database connection with the successful connection or 0. The pointer must be deleted again with sql::logout!
service String or char* 0 Service name
user String or char* 0 User name
passwd String or char* 0 Password
dbname String or char* 0 Database name
client String or char* 0 Client informationen

Create a connection to the database service werk2. A login dialog will be automatically shown which is initialised with the values werk2 and paul as user name. 

DBC      	dbc 	= sql::login (
 					"werk2",
 					"paul",
 					0,
 					0,
 					0);
:
if (!dbc) ...
:
sql::logout (dbc);

Create a connection to the database service werk2. The connection is created with login dialog. 

 
DBC      	dbc 	= sql::login (
 					"werk2",
 					"paul",
 					"pauls_password",
 					"",
 					"");
:
if (!dbc) ...
:
sql::logout (dbc);

Version 1.2 (15 June 2005)

priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

logout

static int logout(DBC dbc)

End the connection to a database service.

Name Type Default Description
Return int   0 or error code
dbc dbc - Pointer to a database connection create with a login. The command may not delete the database connection of InDesign® (sql::connection)!


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

static Query query(DBC dbc)

Create a new transaction query. The InDesign® database connection may never be deleted using a script command.. Der

Name Type Default Description
Return Query   A new query to manage database transactions. If dbc is empty, the call returns 0. You should in every case test the query received for 0.
dbc DBC - Valid database connection, dbc can also contain a SOAP connection. All commands, which are directed at the query, are executed by this service.

Create a new transaction query 

DBC	dbc	= sql::dbconnection ();
Query qu = sql::query (dbc);

if (!qu)
{
    Error notice and possible termination
}


priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator

query::send
query::input
query::output
query::exec
query::fetch
comet.CSOAP.createQuery

static int call(
  DBC dbc,
  char* fname,
  void* ret_val,
  ...)

Using the methods sql::call and sql::calln procedures and functions (stored procedures) of a database can be executed. This process is generally designated with remote procedure call (RPC). RPCs can have input and output parameters and return values. cscript supports the standard data types for parameters and return values : whole numbers, real numbers and strings.
If strings are used as the outparameter of a call, for this at least SQL_MAXSTRLEN (=4001) bytes should be reserved!sql::call does not work with OCI connections. Use sql::calln instead.

[err_code = ] sql::call (
 	dbc, 
 	proc_name,		// Procedure name
 	return_value | 0	// Return value or 0
 	{ 
 	, parameter1
 	, parameter2
 	:
 	, parameters
 	})
A distinction is made between two methods, the first, sql::call, does not require description of the return value and parameter. It is easier to program but, because this information must first be enquired of the database, it is significantly slower.

Name Type Default Description
Return int   0 or ErrorCode
1 No database connection
2 Procedure or function not found
3 Procedure parameters could not be ascertained
4 Return value of a function not specified
5 Error in executing the RPC
6 wrong type, must be kInt | kFloat | kString
7 wrong column type, must be kIn | kOut | kInout
-41 No more active store
dbc DBC - Current database connection
fname String or char* - Name of a database function or procedure
ret_value Address - Pointer to a return variable for functions or 0 for procedures. It is the responsibility of the script programmer to select the correct data type.
[parameter] Value or address - Input parameter : Value or address
Input and output parameter : Address
Output parameter : Address

see sql::calln 







priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator











static   int calln(
 	DBC dbc,
 	char*  fname,
 	void*  ret_val,
 	int ret_type,
 	int param_count,
 	...)



Execute a database function or procedure.
This process is generally designated with remote procedure call (RPC).
RPCs can have input and output parameters and 
return values. cscript supports the standard data types for parameters and return values : whole numbers, real numbers and strings.
.
If strings are used as the outparameter of a call, for this
at least SQL_MAXSTRLEN (=4001) bytes should be reserved!
[err_code = ] sql::calln (
 	dbc, 
 	proc_name, ret|0, type,
 	counter, // Anzahl der Parameter
 	[
 		, val, inout, type	// 1st Parameter
 		, val, inout, type	// 2nd Parameter
 		:
 	])

		


A distinction is made between two methods. In sql::calln a description of the 
parameter and the return value is expected. The write effort leads to a significantly
faster execution of the function than that of the comparable function sql::call, which must first ascertain the parameter and 
return types from the database.







Name
Type
Default
Description





Return

int
 

0 or ErrorCode

1 No database connection

2 Procedure or function not found

3 Procedure parameters could not be ascertained

4 Return value of a function not specified

5 Error in executing the RPC

6 wrong type, must be kInt | kFloat  | kString

7 wrong column type, must be kIn | kOut | kInout

-41 Out of memory






dbc

DBC
-

Current database connection






fname

String or char*
-

Name of a database function or procedure






ret_val

Address
-

Pointer to a return variable for functions or 0 for procedures






ret_type

int
-

On the return value of suitable type specification suitable to the function (kInt, kFloat , kString) oder
kIgnore for procedures.






param_count

int
-

Number of  the parameter of the database function or procedure. param_count Triples
of the following parameters must ensue.






param_value

Value or address
-

param_inout == kIn Value or address

param_inout == kInout or kOut address







param_inout

int
-

Is the parameter an input and/or output parameter?  Select one of the following specifications kIn,
kInout, kOut. It is the responsibility of the script programmer to
select the correct parameter type. kInout must not be used with OCI connections!






param_type

int
-

Which data type have value and parameter. Select one of the folllowing data types kString,
kFloat ,kString. It is the responsibility of the script programmer to
select the correct parameter type.






:

 
 

Further triples according to param_count









Following test table are defined : 


create table PP1_Table (
 	number(10) not null,
 	varchar2(255),
 	number(10,2));




And following function is defined : 


create or replace function PP1_Function_Str (
 	id number,
 	i out number,
 	str in out varchar2,
 	f out number)  RETURN VARCHAR2 AUTHID CURRENT_USER
is
begin
 	i := id;
 	str :=  str || ' : ' || to_char (i)||' - inserted' ;
 	f := i/2.0;
 	insert into PP1_Table values (id, str,f);
 	commit;
 	return str;
end PP1_Procedure;
/




The folllowing script should have two messages : 



    a=10, b='Hihi ... : 10 - inserted', c=5.0000

    a=10, b='Hihi ... : 11 - inserted', c=5.5000


Check the contents of the PP1_Table, two entries should be found here
sein.


#include "internal/types.h"
 
int main ()
 {
 	char	*	name	= "PP1_Function_str";
 	char		s[SQL_MAXSTRLEN];
 	int		a;
 	char		b[SQL_MAXSTRLEN];
 	float		c;
 	int		err;
 
 	strcpy (b, "Yeah, it works, that was InDesign");
 
 	err = sql::call (
 			sql::dbconnection (),	// current connection
 			name,			// function
 			s,			// return value
 			10, &a, b, &c);
 
 	if (err != 0)
 		showerror ("Fehler %d bei sql::call '%s'", err, name);
 	else
 		showmessage ("a=%d, b='%s', c=%f", a, b, c);
 
 	err = sql::calln (
 			sql::dbconnection (),	// current connection
 			name, s, kString,	// return value
 			4,			// Number of parameters
 			11, 	kIn, 	kInt,
 			&a, 	kOut, 	kInt,
 			b,	kInout, kString,
 			&c, 	kOut, 	kFloat);
 
 	if (err != 0)
 		showerror ("Fehler %d bei sql::call '%s'", err, name);
 	else
 		showmessage ("a=%d, b='%s', c=%f", a, b, c);
 
 	return 0;
}










priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





dbconnection

calln

dbconnection

call








static   char* server(DBC dbc, char*  str)



 Ascertain the server name of a database connection.







Name
Type
Default
Description





Return

String or char*
 

(Depends on parameter str)		Name of the server which is using the specified database connection.
The return value is identical with the call parameter str.






dbc

DBC
-

Database connection






str

String or char*
-

Reserved memory for the server name. 
When using char*, the memory should be at least 256 bytes in size.










char		s[256];

showmessage ("%s", sql::server (sql::dbconnection (), s));



Version 1.1.6





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getServer








static   char* user(DBC dbc, char*  str)



 Ascertain the user name with which a database connection is created.







Name
Type
Default
Description





Return

String or char*
 

(Depends on parameter str)		User name for the specified database connection.
The return value is identical with the call parameter str.






dbc

DBC
-

Database connection






str

String or char*
-

Reserved memory for the user name.
When using char*, the memory should be at least 256 bytes in size.










char		s[256];

showmessage ("%s", sql::user (sql::dbconnection (), s));



Version 1.1.6





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getUser








static   int userid(DBC dbc)



 Ascertain a unique iQuest Comet user ID of the database connection.







Name
Type
Default
Description





Return

int
 

User ID or 0






dbc

DBC
-

Database connection










showmessage ("%d", sql::userid (sql::dbconnection ()));



Version 1.1.7, Januar 2005





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getUser








static   char* dbname(DBC dbc, char*  str)



 Ascertain the name of the database to which a connection is created..







Name
Type
Default
Description





Return

String or char*
 

(Depends on parameter str)		Database name/schema for the specified database connection.
The return value is identical with the call parameter str.






dbc

DBC
-

Database connection






str

String or char*
-

Reserved memory for the server name.
When using char*, the memory should be at least 256 bytes in size.










char		s[256];

showmessage ("%s", sql::dbname (sql::dbconnection (), s));



Version 1.1.6





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getDBName








static   char* client(DBC dbc, char*  str)



 Acertain the client which is to be specified when logging in to the database.







Name
Type
Default
Description





Return

String or char*
 

(Depends on parameter str)		Client for the specified database connection.
The return value is identical with the call parameter str.






dbc

DBC
-

Database connection






str

String or char*
-

Reserved memory for the client name.
When using char*, the memory should be at least 256 bytes in size.










char		s[256];

showmessage ("%s", sql::client (sql::dbconnection (), s));



Version 1.1.9, 14 March 2005





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getClient








static   int charset(DBC dbc)



Characterset of database connection.







Name
Type
Default
Description





Return

int
 

-1 : Error

0 : System characterset

1 : UTF8

2 : Unicode 16 Bit

3 : Unicode 32 Bit






dbc

DBC
-

database connection







v4.0.5 R14333, 4. Jan. 2017





priint:comet InDesign® Plug-Ins, comet_pdf, Illustrator





comet.CSQL.getCharset








Author
Paul Seidel



Version
12.03.2024, 15:56 Uhr



Since
Plugin Version 1.0.3



See Also
query::close



Alphabetic index HTML hierarchy of classes or Java