A database connection can send and execute SQL instructions to the database with the assistance of a query, and retrieve the results of the instructions.

You will find a general example for the use of query here.

Version :
12.03.2024, 15:56 Uhr

A database connection can send and execute SQL instructions to the database with the assistance of a query, and retrieve the results of the instructions. A user-defined number of queries, including nested ones, can be opened for a database connection.

Query requires #include "internal/types.h"

The following schema applies for all queries :

  1. sql::query open query
  2. query::send send command
  3. query::input connect entry value
  4. query::output define output variables
  5. query::exec execute commands
  6. query::fetch get results in a loop
  7. query::close close query or return to 2.
Entry values and output variables are managed in the query until the execution. It is also possible to connect value prior to sending. It is likewise possible define output variables either already prior to sending or only after the execution. But it is important that the variables, which are to receive the results, are still valid when fetched. This applies in particular when the results are bundled in a procedure - it must be ensured in this case, that the variables are still in existence after leaving the procedure!

Queries are created with the command sql::query.

static int send(Query qu, char* cmd_part)

Send an SQL instruction or parts of an SQL instruction. The SQL instructions should contain placeholders in place of input values. Placeholders for input values can be specified in two forms :

The first format is certainly the easier to use, but input values have be connected in the sequence of their application. If this is not possible, the second format can be used, input values will then be connected according to the numbering.

If strings are connected, no quotations marks need be inserted around the value placeholders. It is likewise not necessary to mask quotation marks in the strings.

All instructions sent with send are collected in the query and executed with exec as one instruction. Following the execution of the instruction, the instruction buffer in the query is emptied. The connections to the entry values are likewise deleted following execution.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
cmd_part String or char* - Instruction or part of an SQL instruction


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

sql::query
query::isend
query::fsend
soap::call
soap::getlist
comet.CQuery.send

static int isend(Query qu, int i)

Send an int number to an SQL instruction. The function is analogous to the query::send function with the difference that it is not a string but an integer that will be sent. The effect of the command can be described simply so : the transferred number is appended as text to the current instruction text of the query.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
i int - Integer that is to be sent to the query.

Version 1.1.7, January 2005

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

sql::query
query::send
query::fsend
comet.CQuery.send

static int fsend(Query qu, float f)

Send a float number to an SQL query. The function is analogous to the query::send function with the difference that it is not a string but a decimal that will be sent. The effect of the command can be described simply so : the transferred number is appended as text to the current instruction text of the query.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
f float - Decimal that is to be sent to the query.

Version 1.1.7, Januar 2005

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

sql::query
query::send
query::isend
comet.CQuery.send

static int input(
  Query qu,
  int type,
  char* value,
  int fsize = 0,
  char* ftype = "JPEG")

Connect an integer value to the corresponding value placeholder in the SQL command.

If a variable to connected, it must be ensured that this variable is still in existence at the time of execution.

The specification of the second parameter is mandatory and must match the data type of the associated attribute.

Name Type Default Description
Return int   1 = Okay
0 = Error
qu Query - A query opened with sql::query.
type int - Type of value. The data type, which belongs to a variable placeholder, is derived from the respective SQL instruction. It is the task of the script programmer to connect the value types.
kInt
kFloat
kString
kImage
kBinfile : File path
value depends - This value will be sent to the database in place of the associated variable placeholder (? or :n). With the specification of kBinfile the file or a preview of the file will be stored on the database. If the path specification is empty ("" or 0), the target blob is emptied. Non matching pairs can cause InDesign crashes or inavlid data onyour data base.

kInt: int, short, long (z.B -1, 0, 1, 2)
kFloat : float, real (z.B. -1.0, 1.0, 1.1)
kString : String or char* (for instance "", "paul")
kImage : Image, see example below
kBinfile : char*, see example below, If empty (0 or ""), an empty blob is written to data base
⇨ When storing files (type = kBinfile) there is a choice of storing the original file or a preview of the file. (Previews have precedence over image files!). When storing previews the frame size and format can be selected. The following parameters are valid for type = kBinfile only. Previews are created with a bit depth of 24 of a resolution of 72 dpi always
fsize int kCopyLocalFile What is to be stored?
kCopyLocalFile : Original file
kOriginalSize : Preview in oringal image size
> 0 : Size (in points) of a square frame for the preview
ftype String or char* "JPEG" Image format of the preview. The here are the permissible formats. "origin" : 1:1 copy of file (since Comet 3.1 R2014, 19. July 2010)
#include "internal/types.h"

Where the following command is sent

select name, age, cv from persons where name like ? and age > ?

Then input connections of the following type are expected: 

query::input (qu, kString, "Paul%");
query::input (qu, kInt, 10);

To write a preview of an image to data base, you have two possibilities : The first script creates the preview by it self. You are able to choose the previews resolution and color depth. The second script creats the previews internally with the standard resolution (72 dpi) and color depth (24). 


//PCrate and write preview
int main ()
{
    DBC 	dbc 	= sql::connection ();
    Query 	qu 		= sql::query (dbc);
    Image	img 	= image::alloc ("$DESKTOP/clara.JPG");
    Image	prv		= image::preview (img, 140, 72, 24, "PICT");

    query::send (qu, "update pageitems set preview = ? where id = ?");
    query::input (qu, kImage, prv);
    query::input (qu, kInt, 17);
    query::exec (qu);

    image::release (img);
    image::release (prv);
    query::close (qu);

    return 0;
}

//Write preview
int main ()
{
    DBC 	dbc 	= sql::connection ();
    Query 	qu 	= sql::query (dbc);

    query::send (qu, "update pageitems set preview = ? where id = ?");
    query::input (qu,
     	kBinfile,
     	"$DESKTOP/clara.JPG"
     	140,
     	"PICT");
    query::input (qu, kInt, 17);
    query::exec (qu);

    query::close (qu);

    return 0;
}

Get the preview of a template from the current database and save it onto the desktop. 

#include "internal/types.h"
#include "internal/text.h"
int main ()
{
    Query 	qu 	= sql::query (sql::dbconnection ());
    Image	img	= 0;

    /// Init
    if (!qu)
    {
        showmessage ("No database connction");
        return 0;
    }

    img	= image::alloc ();

    // Retreive image
    query::send (qu, "select preview from pageitems where ID = 15"); 
    query::output (qu, kImage, img);
    if (!query::exec(qu))
    {
        query::close (qu);
        image::release (img);
        return 0;
    }
    while (query::fetch (qu)) {}
    query::close (qu);

    // Save image file
    wlog ("", "Size of image : %d\n", image::bytes (img));
    image::save (img, "$DESKTOP/aaa/pageitem15.jpg", 1);
    image::release (img);

    return 0;
}

type = kBinfile and associated parameters since Version 1.3.1 (1 February 2006)
type = kImage since version 1.3.3, P/R 60

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

comet.CQuery.input

static int output(
  Query qu,
  int type,
  int* result)

Define an int output variable for the n-th column of a select instruction. The definition of the variables must correspond to the type and sequence of the output column. It is not necessary to define all output columns, but if you want to receive the results of the n-th column, all the preceding n-th columns must be defined.

Although Oracle makes a clear distinction between strings and texts, you can ignore these differences in scripts. It does not matter if you send a varchar2 value or a CLOB, cscript can independently cater for differences.

If an output variable is defined, it must be ensured that this variable is still in existence at the time the instruction is executed. The specification of the second parameter kInt is mandatory, other specifications lead to significant errors in the script processing and can cause InDesign® to crash.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
type int - Data type of the associated column, a kInt must be located here. The data type which belongs to a variable, is derived from the respective SQL instruction. It is the task of the script programmer to define the correct variable types.
result int* - Value of a result record
#include "internal/types.h"

If the following command is sent

select name, age, cv from persons where name like ? and age > ?

Then value definitions of the following type can be made: 

char   name[256];
int    age
char   *cv = 0;
:
cv     = alloc (50000+1);
:
query::output (qu, kString, name);
query::output (qu, kInt, &age);
query::output (qu, kString, cv, 50000);
:
release (cv);

You can use kImage to dump blob data into files. #include "internal/types.h"
int main () { DBC dbc = sql::connection (); Query qu = sql::query (dbc); Image img = image::alloc ();
query::send (qu, "select preview from comet_preview where id = 1"); query::output (qu, kImage, img); query::exec (qu); while (query::fetch (qu)) {} query::close (qu);
wlog ("", "Size of image : %d\n", image::bytes (img)); image::save (img, "$DESKTOP/aaaPPP.indd", 1); image::release (img);
return 0; }


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

comet.CQuery.output

static int output(
  Query qu,
  int type,
  float* result)

Define a float output variable for the n-th column of a select instruction. The definition of the variables must correspond to the type and sequence of the output column. It is not necessary to define all output columns, but if you want to get the results of the n-th column, all the preceding n-1 columns must be defined. If an output variable is defined, it must be ensured that this variable is still in existence at the time of the execution of the instruction. The specification of the second parameter kFloat is mandatory, other specifications lead to significant errors in the script processing and can cause InDesign® to crash.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
type int - Data type of the associated column, kFloat must be located here. The data type which belongs to a XXXX, is derived from the respective SQL instruction. It is the task of the script programmer to define the correct variable types.
result float* - value of a result record
#include "internal/types.h"


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

comet.CQuery.output

static int output(
  Query qu,
  int type,
  char* result,
  int max_len =0)

Define a String or char* output variable for the n-th column of a select instruction.The definition of the variables must correspond to the type and sequence of the output column. It is not necessary to define all output columns, but if you want to get the results of the n-th column, all the preceding n-1 columns must be defined.

Although Oracle makes a clear distinction between strings and texts, you can ignore these differences in scripts. It does not matter if you send a varchar2 value or a CLOB, cscript can independently cater for differences. What is important for you is simply that sufficient memory for the return is reserved.

If an output variable is defined, it must be ensured that this variable is still in existence at the time of the execution of the instruction. The specification of the second parameter kString is mandatory, other specifications lead to significant errors in the script processing and can cause InDesign® to crash.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
type int - Data type of the associated column, kString must be located here. The data type which belongs to a XXXX, is derived from the respective SQL instruction. It is the task of the script programmer to define the correct variable types.
result String or char* - Value of a result record
max_len int 4001 maximum length of the result. If the result is longer it will be shortened to this length.

When the type of result is String, this parameter is ignored.

Reserve a byte for the end of the string : For varchar[10000] the max_len should then amount at the most to 9999. If max_len is greater then the amount of allocated bytes, InDesign® can crash. Take care to allocate at least 4001 bytes for the result, if max_len is ommited. If you are connected to a Unicode database, the value must not be greater than (allocated size -1) / 8.
#include "internal/types.h"


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

comet.CQuery.output

static int exec(Query qu, int replaceBindings = 1)

The sent command parts are sent as one instruction to the database. All value placeholders in the command are thereafter replaced by the defined input value and the command is executed. After the command is executed, the command buffer is emptied and all connections to the input values are deleted.

Name Type Default Description
Return int   1 = Okay
Return int   0 = Error
qu Query - A query opened with sql::query.
replaceBindings int 1 Replace ? markers before executing?

1 : Yes (recommended)
0 : No. This value is used in SOAP connections only. Possible bound values from previous calls to query::input are ignored in this case.

Parameter replaceBindings since v4.0.5 R17019, 19. Apr 2017

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

comet.CQuery.execute

static int fetch(Query qu)

Fetch the next line of the results of the database command. This command is to be called in a loop, after each command from which a result is expected, until it returns 0. After the last data has been fetched, all connections to the output variable are deleted. The query now can be used for the processing of a further command.

Name Type Default Description
Return int   1 = A result line was fetched
Return int   0 = no further results
qu Query - a query opened with sql::query.
#include "internal/types.h"

if the following command is sent

select name, age, cv from persons where name like ? and age > ?

Then results can be fetched as follows: 

char   name[256];
int    age
char   *cv = 0;
:
cv     = alloc (50000+1);
:
query::output (qu, kString, name);
query::output (qu, kInt, &age);
query::output (qu, kString, cv, 50000);

while (query::fetch (qu))
{
    // name, age and cv now contain current values
}


release (cv);


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

comet.CQuery.fetch

static int close(Query qu)

Closing a query. Each opened query must be closed again using close.

Name Type Default Description
qu Query - A query opened with sql::query.


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

static int commit(Query qu, char* message = 0)

Confirm the changes to a query.

Name Type Default Description
Return int   0 or ErrorCode
qu Query - a query opened with sql::query.
message String or char* 0 Description text for the action


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

comet.CQuery.commit

static int rollback(Query qu, char* message = 0)

Rollback the changes made to a query.

Name Type Default Description
Return int   0 or ErrorCode
qu Query - A query opened with sql::query.
message String or char* 0 Description text for the action


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

comet.CQuery.rollback

static int set_method(Query qu, char* method)

Set the SOAP call methods for the query. SOAP calls to call expect a method name and a command string. Set the command string with send.

Name Type Default Description
Return int   0 or ErrorCode
qu Query - A query opened with sql::query.
method String or char* - Methods which are to be executed by call.


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

static char* get_command(Query qu, int replaceBinds = 0)

Complete command already send (but not executed) to the query.

Name Type Default Description
Return char*   Complete command already send (but not executed) to the query.
"" : Error or nothing send until now

The return value is read only, do NOT CHANGE. The result will be overridden on following calls to the function. See here to learn more about functions returning r/o char* values.
qu Query - A query opened with sql::query.
replaceBinds int 0 Replace ? inside the command by their current values?

0 : No
1 : Yes. Replacing can only be done for question marks already bound to a value by calls to query::input!

v4.0.5 R16000, 14. Feb 2017

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

comet.CQuery.getCommand

Preconditions
#include "internal/types.h" 
#include "internal/types.h"

main ()
{
    Query	qu;
    char	name[512];
    int		id;

    // Open query
    qu = sql::query (sql::dbconnection ());
    if (!qu) return 0;

    // Send command
    query::send (qu, "select name, id  from pageitems where ");
    query::send (qu, "name like ?");

    // Bind input variables
    query::input (qu, kString, "a%");

    // Define output variables
    query::output (qu, kString, name);
    query::output (qu, kInt, &id);	// Take care to take the ADDRESS!

    // Execute command
    if (query::exec (qu))
    {
        // Fetch results
        while (query::fetch (qu))
        {
            // Do something with name and id
            showmessage ("%d\t%s", id, name);
        }
    }

    // Clean up
    query::close (qu);

    return 0;
}

Author
Paul Seidel
Version
12.03.2024, 15:56 Uhr
Since
Plugin Version 1.0.3
See Also
sql::query

Alphabetic index HTML hierarchy of classes or Java