diff options
Diffstat (limited to 'src/interfaces/ecpg/doc/ecpg.texinfo')
| -rw-r--r-- | src/interfaces/ecpg/doc/ecpg.texinfo | 679 |
1 files changed, 679 insertions, 0 deletions
diff --git a/src/interfaces/ecpg/doc/ecpg.texinfo b/src/interfaces/ecpg/doc/ecpg.texinfo new file mode 100644 index 00000000000..a76bc8b2a5a --- /dev/null +++ b/src/interfaces/ecpg/doc/ecpg.texinfo @@ -0,0 +1,679 @@ +\input texinfo @c -*-texinfo-*- +@c %**start of header +@setfilename ecpg +@settitle Ecpg - Embedded SQL in C for Postgres95 +@setchapternewpage odd +@c %**end of header + +@ifinfo +This file documents an embedded SQL in C package for Postgres 95. + +Copyright 1996 Linus Tolke + +Permission is granted to copy and use in the same way as you are allowed +to copy and use the rest of the Postgres 95. +@end ifinfo + +@c This title page illustrates only one of the +@c two methods of forming a title page. + +@titlepage +@title ECPG +@subtitle Embedded SQL in C for Postgres95 +@author Linus Tolke + +@c The following two commands +@c start the copyright page. +@page +@vskip 0pt plus 1filll +Copyright @copyright{} 1996 Linus Tolke + +Published by Linus Tolke + +Permission is granted to copy and use in the same way as you are allowed +to copy and use the rest of the Postgres 95. +@end titlepage + +@node Top, Why embedded SQL, (dir), (dir) +@comment node-name, next, previous, up + +@ifinfo +Ecpg is an embedded sql preprocessor for C and library for Postgres95. + +It is written by Linus Tolke <linus@@epact.se> + +This texinfo page and the code is all the documentation you get. There +will not be any separate manual page, installation description or +buglist. +@end ifinfo + +@menu +* Why embedded SQL:: +* Simple description of the concept:: +* How to use it:: +* How it works:: +* Limitations:: +* Porting from other DBMSs:: +* Installation:: +* Index:: + + --- The Detailed Node Listing --- + +How to use it + +* Preprocessor:: +* Library:: +* Error handling:: + +How it works + +* The preprocessor:: +* A complete example:: +* The library:: + +Limitations + +* What can be done with this concept:: +* What will never be included and why:: +@end menu + +@node Why embedded SQL, Simple description of the concept, Top, Top +@comment node-name, next, previous, up +@chapter Why embedded SQL + +Embedded SQL has some small advantages over other ways to handle SQL +queries. It takes care of all the tidious moving of information to and +from variables in your c-program. + +There is an ANSI-standard describing how the embedded language should +work. Most embedded sql preprocessors I have seen and heard of makes +extensions so it is difficult to obtain portability even between them +anyway. I have not read the standard but I hope that my implementation +does not deviate to much and that it would be possible to port programs +with embedded sql written for other DBMS:s to Postgres95 and thus +promoting the spirit of free software. + + +@node Simple description of the concept, How to use it, Why embedded SQL, Top +@comment node-name, next, previous, up +@chapter Simple description of the concept + +You write your program in C with some special sql things. +For declaring variables that can be used in SQL statements you need to +put them in a special declare section. +You use a special syntax for the sql queries. + +Before compiling you run the file through the embedded sql c +preprocessor and it converts the SQL statements you used to function +calls with the variables used as arguments. Both variables that are used +as input to the SQL statements and variables that will contain the +result are passed. + +Then you compile and at link time you link with a special library that +contains the functions used. These functions (actually it is mostly one +single function) fetches the information from the arguments, performs +the SQL query using the ordinary interface (pq) and puts back +the result in the arguments dedicated for output. + +Then you run your program and when the control arrives to the SQL +statement the SQL statement is performed against the database and you +can continue with the result. + + +@node How to use it, How it works, Simple description of the concept, Top +@comment node-name, next, previous, up +@chapter How to use it + +This chapter describes how to use the ECPG tool. + +@menu +* Preprocessor:: +* Library:: +* Error handling:: +@end menu + +@node Preprocessor, Library, How to use it, How to use it +@comment node-name, next, previous, up +@section Preprocessor + +@cindex preprocessor +@cindex @code{ecpg} +The preprocessor is called @code{ecpg}. After installation it resides in +the postgres @code{bin} directory. It accepts two arguments like +@code{iname=filename} and @code{oname=filename}. Both arguments must be +present or an error will occur. + +In the alpha version the preprocessor has a lot of flaws: +@table @asis +@item Debug text output +It writes every token parsed to the @code{stderr}. +@item Looses line numbering +The line numbers and file name information is lost in the preprocessor. +This means that when running the program through a debugger you end up +in the @code{.c}-file that is the output from the preprocessor and not +in the input to the preprocessor. This can be fixed! +@item The interface is strange, to say the least +It would be better with a consistant unix arguments interface, perhaps +builtin default filenames so they won't have to be given all the time. +@item Cannot do syntax checking on your SQL statements +Whatever you write is copied more or less exactly to the postgres95 and +you will not be able to locate your errors until run-time. +@end table + +@node Library, Error handling, Preprocessor, How to use it +@section Library + +@cindex library functions +@cindex @code{libecpg.a} +@cindex @code{-lecpg} +The library is called @code{libecpg.a}. The library used the pq library +for the communication to the postgres server so you will have to link +your program with @code{-lecpg -lpq}. + +The library has some methods that are "hidden" but that could prove very +useful sometime. + +@table @asis +@item @code{ECPGdebug(int)} +@cindex @code{ECPGdebug(int)} +@cindex debuglogging +If this is called, with a non-zero argument, then debuglogging is turned +on. Debuglogging is done on @code{stderr}. Most SQL statement logs its +arguments and result. + +The most important one (@code{ECPGdo}) that is called on all SQL +statements except @code{EXEC SQL COMMIT}, @code{EXEC SQL ROLLBACK}, +@code{EXEC SQL CONNECT} logs both its expanded string, i.e. the string +with all the input variables inserted, and the result from the +postgres95 server. This can be very useful when searching for errors +in your SQL statements. + +@item @code{ECPGstatus()} +@cindex @code{ECPGstatus()} +This method returns TRUE if we are connected to a database and FALSE if +not. +@end table + +@node Error handling, , Library, How to use it +@comment node-name, next, previous, up +@section Error handling + +@cindex @code{sqlca.h} +@cindex @code{struct sqlca} +@cindex @code{sqlcode} +@cindex @code{error messages} +To be able to detect errors from the postgres server you include a line +like: +@example +exec sql include sqlca; +@end example +in the include section of your file. This will define a struct and a +variable with the name @code{sqlca} as following: +@example +struct sqlca @{ + int sqlcode; + struct @{ + int sqlerrml; + char sqlerrmc[1000]; + @} sqlerrm; +@} sqlca; +@end example + +If an error occured in the last SQL statement then @code{sqlca.sqlcode} +will be non-zero. If @code{sqlca.sqlcode} is less that 0 then this is +some kind of serious error, like the database definition does not match +the query given. If it is bigger than 0 then this is a normal error like +the table did not contain the requested row. + +sqlca.sqlerrm.sqlerrmc will contain a string that describes the error. +The string ends with @code{line 23.} where the line is the line number +in the source file (actually the file generated by the preprocessor but +I hope I can fix this to be the line number in the input file.) + +List of errors that can occur: +@cindex error list +@table @asis +@item -1, Unsupported type %s on line %d. +Does not normally occur. This is a sign that the preprocessor has +generated something that the library does not know about. Perhaps you +are running incompatible versions of the preprocessor and the library. + +@item -1, Too many arguments line %d. +@itemx -1, Too few arguments line %d. +The preprocessor has goofed up and generated some incorrect code. + +@item -1, Error starting transaction line %d. +Postgres95 signalled to us that we cannot open the connection. + +@item -1, Postgres error: %s line %d. +Some postgres95 error. The message contains the error message from the +postgres95 backend. + +@item 1, Data not found line %d. +This is a "normal" error that tells you that what you are quering cannot +be found or we have gone through the cursor. + +@item -1, To many matches line %d. +This means that the query has returned several lines. The @code{SELECT} +you made probably was not unique. + +@item -1, Not correctly formatted int type: %s line %d. +This means that the host variable is of an @code{int} type and the field +in the postgres95 database is of another type and contains a value that +cannot be interpreted as an @code{int}. The library uses @code{strtol} +for this conversion. + +@item -1, Not correctly formatted unsigned type: %s line %d. +This means that the host variable is of an @code{unsigned int} type and +the field in the postgres95 database is of another type and contains a +value that cannot be interpreted as an @code{unsigned int}. The library +uses @code{strtoul} for this conversion. + +@item -1, Not correctly formatted floating point type: %s line %d. +This means that the host variable is of an @code{float} type and +the field in the postgres95 database is of another type and contains a +value that cannot be interpreted as an @code{float}. The library +uses @code{strtod} for this conversion. + +@item -1, Too few arguments line %d. +This means that the postgres95 has returned more records than we have +matching variables. Perhaps you have forgotten a couple of the host +variables in the @code{INTO :var1,:var2}-list. + +@item -1, Too many arguments line %d. +This means that th postgres95 has returned fewer records than we have +host variables. Perhaps you have to many host variables in the +@code{INTO :var1,:var2}-list. + +@item -1, Empty query line %d. +Postgres95 returned PGRES_EMPTY_QUERY. + +@item -1, Error: %s line %d. +Postgres95 returned PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR or +PGRES_BAD_RESPONSE. Which one and why is hopefully explained in the +message. + +@item -1, Postgres error line %d. +Postgres95 returns something that the library does not know how to +handle. This is probably because the version of postgres95 does not +match the version of the ecpg library. + +@item -1, Error committing line %d. +Error during @code{COMMIT}. @code{EXEC SQL COMMIT} is translated to an +@code{end} operation in postgres95 and that is the operation that could +not be performed. + +@item -1, Error rolling back line %d. +Error during @code{ROLLBACK}. @code{EXEC SQL ROLLBACK} is translated to +an @code{abort} operation in postgres95 and that is the operation that +could not be performed. + +@item -1, ECPGconnect: could not open database %s. +The connect to the database did not work. + +@end table + +@node How it works, Limitations, How to use it, Top +@chapter How it works +@comment node-name, next, previous, up + +This chapter describes how the things work. The ambition is to make this +chapter contain things for those that want to have a look inside and the +chapter on How to use it should be enough for all normal questions. + +So, read this before looking at the internals of the @code{ecpg}. If +you are not interested in how it really works, skip this chapter. + +@menu +* The preprocessor:: +* A complete example:: +* The library:: +@end menu + +@node The preprocessor, A complete example, How it works, How it works +@comment node-name, next, previous, up +@section The preprocessor + +First three lines are written to the output. A comment and two include +lines necessary for the interface to the library. + +Then the preprocessor works in one pass only reading the input file and +writing to the output as it goes along. Normally it just echoes +everything to the output without looking at it further. + +When it comes to an @code{EXEC SQL} statements it interviens and +changes them depending on what iit is. The @code{EXEC SQL} statement can +be one of these: + +@itemize @bullet + +@item Declare sections +@cindex Declare section +Declare sections begins with +@example +exec sql begin declare section; +@end example +and ends with +@example +exec sql end declare section; +@end example +In the section only variable declarations are allowed. Every variable +declare within this section is also entered in a list of variables +indexed on their name together with the corresponding type. + +The declaration is echoed to the file to make the variable a normal +C-variable also. + +The special types VARCHAR and VARCHAR2 are converted into a named struct +for every variable. A declaration like: +@example +VARCHAR var[180]; +@end example +is converted into +@example +struct varchar_var @{ int len; char arr[180]; @} var; +@end example + + +@item Include statements +@cindex Include statement +An include statement looks like: +@example +exec sql include filename; +@end example +It is converted into +@example +#include <filename.h> +@end example + +@item Connect statement +@cindex Connect statement +A connect statements looks like: +@example +exec sql connect 'databasename'; +@end example +That statement is converted into +@example +ECPGconnect("databasename"); +@end example + +@item Open cursor statement +@cindex Open cursor statement +An open cursor statement looks like: +@example +exec sql open blablabla; +@end example +and that is ignore and not copied from the output. + +@item Commit statement +@cindex Commit statement +A commit statement looks like +@example +exec sql commit; +@end example +and is translated on the output to +@example +ECPGcommit(__LINE__); +@end example + +@item Rollback statement +@cindex Rollback statement +A rollback statement looks like +@example +exec sql rollback; +@end example +and is translated on the output to +@example +ECPGrollback(__LINE__); +@end example + +@item Other statements +Other SQL statements are other statements that start with +@code{exec sql} and ends with @code{;}. Everything inbetween is treated +as an sql statement and parsed for variable substitution. + +Variable substitution occur when a symbol starts with a colon +(@code{:}). Then a variable with that name is found among the variables +that were previously declared within a declare section and depending on +whether or not the SQL statements knows it to be a variable for input or +output the pointers to the variables are written to the output to allow +for access by the function. + +For every variable that is part of the SQL request the function gets +another five arguments. +@enumerate +@item The type as a special symbol +@item A pointer to the value +@item The size of the variable if it is a varchar +@item Number of elements in the array (for array fetches) +@item The offset to the next element in the array (for array fetches) +@end enumerate +Since the array fetches are not implemented yet the two last arguments +are not really important. They could perhaps have been left out. + +@end itemize + + +@node A complete example, The library, The preprocessor, How it works +@comment node-name, next, previous, up +@section A complete example +Here is a complete example describing the output of the preprocessor: +@example +exec sql begin declare section; +int index; +int result; +exec sql end declare section; +... + exec sql select res into :result from mytable where index = :index; +@end example +is translated into: +@example +/* These two include files are added by the preprocessor */ +#include <ecpgtype.h> +#include <ecpglib.h> +/* exec sql begin declare section */ + + int index; + int result; +/* exec sql end declare section */ + +... + ECPGdo(__LINE__, "select res from mytable where index = ;;", + ECPGt_int,&index,0,0,sizeof(int), + ECPGt_EOIT, + ECPGt_int,&result,0,0,sizeof(int), + ECPGt_EORT ); +@end example +(the indentation in this manual is added for readability and not +something that the preprocessor can do.) + + +@node The library, , A complete example, How it works +@comment node-name, next, previous, up +@section The library +The most important function in the library is the @code{ECPGdo} +function. It takes a variable amount of arguments. Hopefully we wont run +into machines with limits on the amount of variables that can be +accepted by a varchar function. This could easily add up to 50 or so +arguments. + +The arguments are: +@table @asis +@item A line number +This is a line number for the original line used in error messages only. +@item A string +This is the sql request that is to be issued. This request is modified +by the input variables, i.e. the variables that where not known at +compile time but are to be entered in the request. Where the variables +should go the string contains @code{;;}. +@item Input variables +As described in the section about the preprocessor every input variable +gets five arguments. +@item ECPGt_EOIT +An enum telling that there are no more input variables. +@item Output variables +As described in the section about the preprocessor every input variable +gets five arguments. These variables are filled by the function. +@item ECPGt_EORT +An enum telling that there are no more variables. +@end table + +All the SQL statements are performed in one transaction unless you issue +a commit transaction. This works so that the first transaction or the +first after a commit or rollback always begins a transaction. + +To be completed: entries describing the other entries. + +@node Limitations, Porting from other DBMSs, How it works, Top +@chapter Limitations +@comment node-name, next, previous, up + +I separate the limitations in two different groups. Those that are of +the kind that I have not gotten around to it yet and those that I will +never bother to look at. + +@menu +* What can be done with this concept:: +* What will never be included and why:: +@end menu + +@node What can be done with this concept, What will never be included and why, Limitations, Limitations +@comment node-name, next, previous, up +@section What can be done with this concept + +This is a list of things that I have plans to include in some future. + +@table @asis + +@item no restriction to strings only +The PQ interface, and most of all the PQexec function, that is used by +the ecpg relies on that the request is built up as a string. In some +cases, like when the data contains the null character, this will be a +serious problem. + +@item line numbering +The preprocessor should generate output with directions to the compiler +to generate debugging code including the file name and line numbers of +the input to the preprocessor. + +@item error codes +There should be different error numbers for the different errors instead +of just -1 for them all. + +@item library functions +to_date et al. + +@item records +@cindex records +Possibility to define records or @code{struct}s in the declare section +in a way that the record can be filled from one row in the database. + +This is a simpler way to handle an entire row at a time. + +@item array operations +@cindex array operations +Oracle has array operations that enhances speed. When implementing it in +@code{ecpg} it is done for compatibility reasons only. For them to +improve speed would require a lot more insight in the postgres internal +mechanisms than I possess. + +@item indicator variables +@cindex indicator variables +@cindex @code{VARCHAR2} +Oracle has indicator variables that tell if a value is @code{null} or if +it is empty. This largely simplifies array operations and provides for a +way to hack around some design flaws in the handling of @code{VARCHAR2} +@footnote{like that an empty string isn't distinguishable from a +@code{null} value}. I am not sure if this is an Oracle extension or part +of the ANSI standard. + +@item typedefs +@cindex typedef +As well as complex types like records and arrays, typedefs would be +a good thing to take care of. + +@item conversion of scripts +@cindex conversion of scripts +To set up a database you need a few scripts with table definitions and +other configuration parameters. If you have these scripts for an old +database you would like to just apply them to get a postgres database +that works in the same way. + +The functionality could be accomplished with some conversion scripts. +Speed will never be accomplished in this way. To do this you need a +bigger insight in the database construction and the use of the database +than could be realised in a script. + +@end table + + +@node What will never be included and why, , What can be done with this concept, Limitations +@comment node-name, next, previous, up +@section What will never be included and why + +@table @asis + +@item oracles single tasking possibility +@cindex single tasking +Oracle version 7.0 on AIX 3 uses the OS-supported locks on the shared +memory segments and allows the application designer to link an +application in a so called single tasking way. Instead of starting one +client process per application process both the database part and the +application part is run in the same process. In later versions of oracle +this is no longer supported. + +This would require a total redesign of the postgres access model and +that effort can not justify the performance gained. + +@end table + + +@node Porting from other DBMSs, Installation, Limitations, Top +@chapter Porting from other DBMSs +@comment node-name, next, previous, up + +To be written by persons that knows the different DBMSs and that +actually does port something... + +@node Installation, Index, Porting from other DBMSs, Top +@comment node-name, next, previous, up +@chapter Installation +@cindex installation + +Step by step installation (if everything goes ok): + +@enumerate +@item Fetch everything and unpack + +If you are reading this documentation you have probably managed this +step already. + +@item @code{./configure --with-postgres=/path/to/postgres} + +This is to be done in the ecpg directory, i.e. the directory containing +the @file{configure} file. + +The @file{/path/to/postgres} is the path to the installed postgres. It +points out the directory where the include, lib and bin directories +reside. The include directory is used when building the library and all +three of them become residents for ecpg include files, library and +binaries. + +@item @code{make all} + +@item As the postgres user @code{make install} + +The postgres user is the owner of the postgres include, lib and bin +directories. The installation procedure installs its files there +alongside the postgres files. +@item Done. + +@end enumerate + + +@node Index, , Installation, Top +@unnumbered Index + +@printindex cp + +@contents |