?????????? ????????? - ??????????????? - /home/agenciai/public_html/cd38d8/Tutorial.tar
???????
Web.pod 0000644 00000064422 15126703322 0005776 0 ustar 00 #============================================================= -*-perl-*- # # Template::Tutorial::Web # # DESCRIPTION # Tutorial on generating web content with the Template Toolkit # # AUTHOR # Andy Wardley <abw@wardley.org> # # COPYRIGHT # Copyright (C) 1996-2008 Andy Wardley. All Rights Reserved. # # This module is free software; you can redistribute it and/or # modify it under the same terms as Perl itself. # #======================================================================== =head1 NAME Template::Tutorial::Web - Generating Web Content Using the Template Toolkit =head1 Overview This tutorial document provides a introduction to the Template Toolkit and demonstrates some of the typical ways it may be used for generating web content. It covers the generation of static pages from templates using the L<tpage|Template::Tools::tpage> and L<ttree|Template::Tools::ttree> scripts and then goes on to show dynamic content generation using CGI scripts and Apache/mod_perl handlers. Various features of the Template Toolkit are introduced and described briefly and explained by use of example. For further information, see L<Template>, L<Template::Manual> and the various sections within it. e.g perldoc Template # Template.pm module usage perldoc Template::Manual # index to manual perldoc Template::Manual::Config # e.g. configuration options The documentation is also available in HTML format to read online, or download from the Template Toolkit web site: http://template-toolkit.org/docs/ =head1 Introduction The Template Toolkit is a set of Perl modules which collectively implement a template processing system. A template is a text document with special markup tags embedded in it. By default, the Template Toolkit uses 'C<[%>' and 'C<%]>' to denote the start and end of a tag. Here's an example: [% INCLUDE header %] People of [% planet %], your attention please. This is [% captain %] of the Galactic Hyperspace Planning Council. As you will no doubt be aware, the plans for development of the outlying regions of the Galaxy require the building of a hyperspatial express route through your star system, and regrettably your planet is one of those scheduled for destruction. The process will take slightly less than [% time %]. Thank you. [% INCLUDE footer %] Tags can contain simple I<variables> (like C<planet> and C<captain>) and more complex I<directives> that start with an upper case keyword (like C<INCLUDE>). A directive is an instruction that tells the template processor to perform some action, like processing another template (C<header> and C<footer> in this example) and inserting the output into the current template. In fact, the simple variables we mentioned are actually C<GET> directives, but the C<GET> keyword is optional. People of [% planet %], your attention please. # short form People of [% GET planet %], your attention please. # long form Other directives include C<SET> to set a variable value (the C<SET> keyword is also optional), C<FOREACH> to iterate through a list of values, and C<IF>, C<UNLESS>, C<ELSIF> and C<ELSE> to declare conditional blocks. The Template Toolkit processes all I<text> files equally, regardless of what kind of content they contain. So you can use TT to generate HTML, XML, CSS, Javascript, Perl, RTF, LaTeX, or any other text-based format. In this tutorial, however, we'll be concentrating on generating HTML for web pages. =head1 Generating Static Web Content Here's an example of a template used to generate an HTML document. [% INCLUDE header title = 'This is an HTML example'; pages = [ { url = 'http://foo.org' title = 'The Foo Organisation' } { url = 'http://bar.org' title = 'The Bar Organisation' } ] %] <h1>Some Interesting Links</h1> <ul> [% FOREACH page IN pages %] <li><a href="[% page.url %]">[% page.title %]</a> [% END %] </ul> [% INCLUDE footer %] This example shows how the C<INCLUDE> directive is used to load and process separate 'C<header>' and 'C<footer>' template files, including the output in the current document. These files might look something like this: header: <html> <head> <title>[% title %]</title> </head> <body> footer: <div class="copyright"> © Copyright 2007 Arthur Dent </div> </body> </html> The example also uses the C<FOREACH> directive to iterate through the 'C<pages>' list to build a table of links. In this example, we have defined this list within the template to contain a number of hash references, each containing a 'C<url>' and 'C<title>' member. The C<FOREACH> directive iterates through the list, aliasing 'C<page>' to each item (in this case, hash array references). The C<[% page.url %]> and C<[% page.title %]> directives then access the individual values in the hash arrays and insert them into the document. =head2 Using tpage Having created a template file we can now process it to generate some real output. The quickest and easiest way to do this is to use the L<tpage|Template::Tools::tpage> script. This is provided as part of the Template Toolkit and should be installed in your usual Perl bin directory. Assuming you saved your template file as F<example.html>, you would run the command: $ tpage example.html This will process the template file, sending the output to C<STDOUT> (i.e. whizzing past you on the screen). You may want to redirect the output to a file but be careful not to specify the same name as the template file, or you'll overwrite it. You may want to use one prefix for your templates (e.g. 'C<.tt>') and another (e.g. 'C<.html>') for the output files. $ tpage example.tt > example.html Or you can redirect the output to another directory. e.g. $ tpage templates/example.tt > html/example.html The output generated would look like this: <html> <head> <title>This is an HTML example</title> </head> <body> <h1>Some Interesting Links</h1> <ul> <li><a href="http://foo.org">The Foo Organsiation</a> <li><a href="http://bar.org">The Bar Organsiation</a> </ul> <div class="copyright"> © Copyright 2007 Arthur Dent </div> </body> </html> The F<header> and F<footer> template files have been included (assuming you created them and they're in the current directory) and the link data has been built into an HTML list. =head2 Using ttree The L<tpage|Template::Tools::tpage> script gives you a simple and easy way to process a single template without having to write any Perl code. The L<ttree:Template::Tools::ttree> script, also distributed as part of the Template Toolkit, provides a more flexible way to process a number of template documents in one go. The first time you run the script, it will ask you if it should create a configuration file (F<.ttreerc>) in your home directory. Answer C<y> to have it create the file. The L<ttree:Template::Tools::ttree> documentation describes how you can change the location of this file and also explains the syntax and meaning of the various options in the file. Comments are written to the sample configuration file which should also help. In brief, the configuration file describes the directories in which template files are to be found (C<src>), where the corresponding output should be written to (C<dest>), and any other directories (C<lib>) that may contain template files that you plan to C<INCLUDE> into your source documents. You can also specify processing options (such as C<verbose> and C<recurse>) and provide regular expression to match files that you don't want to process (C<ignore>, C<accept>)> or should be copied instead of being processed as templates (C<copy>). An example F<.ttreerc> file is shown here: $HOME/.ttreerc: verbose recurse # this is where I keep other ttree config files cfg = ~/.ttree src = ~/websrc/src lib = ~/websrc/lib dest = ~/public_html/test ignore = \b(CVS|RCS)\b ignore = ^# You can create many different configuration files and store them in the directory specified in the C<cfg> option, shown above. You then add the C<-f filename> option to C<ttree> to have it read that file. When you run the script, it compares all the files in the C<src> directory (including those in sub-directories if the C<recurse> option is set), with those in the C<dest> directory. If the destination file doesn't exist or has an earlier modification time than the corresponding source file, then the source will be processed with the output written to the destination file. The C<-a> option forces all files to be processed, regardless of modification times. The script I<doesn't> process any of the files in the C<lib> directory, but it does add it to the C<INCLUDE_PATH> for the template processor so that it can locate these files via an C<INCLUDE>, C<PROCESS> or C<WRAPPER> directive. Thus, the C<lib> directory is an excellent place to keep template elements such as header, footers, etc., that aren't complete documents in their own right. You can also specify various Template Toolkit options from the configuration file. Consult the L<ttree|Template::Tools::ttree> documentation and help summary (C<ttree -h>) for full details. e.g. $HOME/.ttreerc: pre_process = config interpolate post_chomp The C<pre_process> option allows you to specify a template file which should be processed before each file. Unsurprisingly, there's also a C<post_process> option to add a template after each file. In the fragment above, we have specified that the C<config> template should be used as a prefix template. We can create this file in the C<lib> directory and use it to define some common variables, including those web page links we defined earlier and might want to re-use in other templates. We could also include an HTML header, title, or menu bar in this file which would then be prepended to each and every template file, but for now we'll keep all that in a separate C<header> file. $lib/config: [% root = '~/abw' home = "$root/index.html" images = "$root/images" email = 'abw@wardley.org' graphics = 1 webpages = [ { url => 'http://foo.org', title => 'The Foo Organsiation' } { url => 'http://bar.org', title => 'The Bar Organsiation' } ] %] Assuming you've created or copied the C<header> and C<footer> files from the earlier example into your C<lib> directory, you can now start to create web pages like the following in your C<src> directory and process them with C<ttree>. $src/newpage.html: [% INCLUDE header title = 'Another Template Toolkit Test Page' %] <a href="[% home %]">Home</a> <a href="mailto:[% email %]">Email</a> [% IF graphics %] <img src="[% images %]/logo.gif" align=right width=60 height=40> [% END %] [% INCLUDE footer %] Here we've shown how pre-defined variables can be used as flags to enable certain feature (e.g. C<graphics>) and to specify common items such as an email address and URL's for the home page, images directory and so on. This approach allows you to define these values once so that they're consistent across all pages and can easily be changed to new values. When you run F<ttree>, you should see output similar to the following (assuming you have the verbose flag set). ttree 2.9 (Template Toolkit version 2.20) Source: /home/abw/websrc/src Destination: /home/abw/public_html/test Include Path: [ /home/abw/websrc/lib ] Ignore: [ \b(CVS|RCS)\b, ^# ] Copy: [ ] Accept: [ * ] + newpage.html The C<+> in front of the C<newpage.html> filename shows that the file was processed, with the output being written to the destination directory. If you run the same command again, you'll see the following line displayed instead showing a C<-> and giving a reason why the file wasn't processed. - newpage.html (not modified) It has detected a C<newpage.html> in the destination directory which is more recent than that in the source directory and so hasn't bothered to waste time re-processing it. To force all files to be processed, use the C<-a> option. You can also specify one or more filenames as command line arguments to C<ttree>: tpage newpage.html This is what the destination page looks like. $dest/newpage.html: <html> <head> <title>Another Template Toolkit Test Page</title> </head> <body> <a href="~/abw/index.html">Home</a> <a href="mailto:abw@wardley.org">Email me</a> <img src="~/abw/images/logo.gif" align=right width=60 height=40> <div class="copyright"> © Copyright 2007 Arthur Dent </div> </body> </html> You can add as many documents as you like to the C<src> directory and C<ttree> will apply the same process to them all. In this way, it is possible to build an entire tree of static content for a web site with a single command. The added benefit is that you can be assured of consistency in links, header style, or whatever else you choose to implement in terms of common templates elements or variables. =head1 Dynamic Content Generation Via CGI Script The L<Template> module provides a simple front-end to the Template Toolkit for use in CGI scripts and Apache/mod_perl handlers. Simply C<use> the L<Template> module, create an object instance with the L<new()> method and then call the L<process()> method on the object, passing the name of the template file as a parameter. The second parameter passed is a reference to a hash array of variables that we want made available to the template: #!/usr/bin/perl use strict; use warnings; use Template; my $file = 'src/greeting.html'; my $vars = { message => "Hello World\n" }; my $template = Template->new(); $template->process($file, $vars) || die "Template process failed: ", $template->error(), "\n"; So that our scripts will work with the same template files as our earlier examples, we'll can add some configuration options to the constructor to tell it about our environment: my $template->new({ # where to find template files INCLUDE_PATH => ['/home/abw/websrc/src', '/home/abw/websrc/lib'], # pre-process lib/config to define any extra values PRE_PROCESS => 'config', }); Note that here we specify the C<config> file as a C<PRE_PROCESS> option. This means that the templates we process can use the same global variables defined earlier for our static pages. We don't have to replicate their definitions in this script. However, we can supply additional data and functionality specific to this script via the hash of variables that we pass to the C<process()> method. These entries in this hash may contain simple text or other values, references to lists, others hashes, sub-routines or objects. The Template Toolkit will automatically apply the correct procedure to access these different types when you use the variables in a template. Here's a more detailed example to look over. Amongst the different template variables we define in C<$vars>, we create a reference to a L<CGI> object and a C<get_user_projects()> sub-routine. #!/usr/bin/perl use strict; use warnings; use Template; use CGI; $| = 1; print "Content-type: text/html\n\n"; my $file = 'userinfo.html'; my $vars = { 'version' => 3.14, 'days' => [ qw( mon tue wed thu fri sat sun ) ], 'worklist' => \&get_user_projects, 'cgi' => CGI->new(), 'me' => { 'id' => 'abw', 'name' => 'Andy Wardley', }, }; sub get_user_projects { my $user = shift; my @projects = ... # do something to retrieve data return \@projects; } my $template = Template->new({ INCLUDE_PATH => '/home/abw/websrc/src:/home/abw/websrc/lib', PRE_PROCESS => 'config', }); $template->process($file, $vars) || die $template->error(); Here's a sample template file that we might create to build the output for this script. $src/userinfo.html: [% INCLUDE header title = 'Template Toolkit CGI Test' %] <a href="mailto:[% email %]">Email [% me.name %]</a> <p>This is version [% version %]</p> <h3>Projects</h3> <ul> [% FOREACH project IN worklist(me.id) %] <li> <a href="[% project.url %]">[% project.name %]</a> [% END %] </ul> [% INCLUDE footer %] This example shows how we've separated the Perl implementation (code) from the presentation (HTML). This not only makes them easier to maintain in isolation, but also allows the re-use of existing template elements such as headers and footers, etc. By using template to create the output of your CGI scripts, you can give them the same consistency as your static pages built via L<ttree|Template::Tools::ttree> or other means. Furthermore, we can modify our script so that it processes any one of a number of different templates based on some condition. A CGI script to maintain a user database, for example, might process one template to provide an empty form for new users, the same form with some default values set for updating an existing user record, a third template for listing all users in the system, and so on. You can use any Perl functionality you care to write to implement the logic of your application and then choose one or other template to generate the desired output for the application state. =head1 Dynamic Content Generation Via Apache/Mod_Perl Handler B<NOTE:> the L<Apache::Template> module is available from CPAN and provides a simple and easy to use Apache/mod_perl interface to the Template Toolkit. Although basic, it implements most, if not all of what is described below, and it avoids the need to write your own handler. However, in many cases, you'll want to write your own handler to customise processing for your own need, and this section will show you how to get started. The L<Template> module can be used from an Apache/mod_perl handler. Here's an example of a typical Apache F<httpd.conf> file: PerlModule CGI; PerlModule Template PerlModule MyOrg::Apache::User PerlSetVar websrc_root /home/abw/websrc <Location /user/bin> SetHandler perl-script PerlHandler MyOrg::Apache::User </Location> This defines a location called C</user/bin> to which all requests will be forwarded to the C<handler()> method of the C<MyOrg::Apache::User> module. That module might look something like this: package MyOrg::Apache::User; use strict; use Apache::Constants qw( :common ); use Template; use CGI; our $VERSION = 1.59; sub handler { my $r = shift; my $websrc = $r->dir_config('websrc_root') or return fail($r, SERVER_ERROR, "'websrc_root' not specified"); my $template = Template->new({ INCLUDE_PATH => "$websrc/src/user:$websrc/lib", PRE_PROCESS => 'config', OUTPUT => $r, # direct output to Apache request }); my $params = { uri => $r->uri, cgi => CGI->new, }; # use the path_info to determine which template file to process my $file = $r->path_info; $file =~ s[^/][]; $r->content_type('text/html'); $r->send_http_header; $template->process($file, $params) || return fail($r, SERVER_ERROR, $template->error()); return OK; } sub fail { my ($r, $status, $message) = @_; $r->log_reason($message, $r->filename); return $status; } The handler accepts the request and uses it to determine the C<websrc_root> value from the config file. This is then used to define an C<INCLUDE_PATH> for a new L<Template> object. The URI is extracted from the request and a L<CGI> object is created. These are both defined as template variables. The name of the template file itself is taken from the C<PATH_INFO> element of the request. In this case, it would comprise the part of the URL coming after C</user/bin>, e.g for C</user/bin/edit>, the template file would be C<edit> located in C<$websrc/src/user>. The headers are sent and the template file is processed. All output is sent directly to the C<print()> method of the Apache request object. =head1 Using Plugins to Extend Functionality As we've already shown, it is possible to bind Perl data and functions to template variables when creating dynamic content via a CGI script or Apache/mod_perl process. The Template Toolkit also supports a plugin interface which allows you define such additional data and/or functionality in a separate module and then load and use it as required with the C<USE> directive. The main benefit to this approach is that you can load the extension into any template document, even those that are processed "statically" by C<tpage> or C<ttree>. You I<don't> need to write a Perl wrapper to explicitly load the module and make it available via the stash. Let's demonstrate this principle using the C<DBI> plugin written by Simon Matthews (available from CPAN). You can create this template in your C<src> directory and process it using C<ttree> to see the results. Of course, this example relies on the existence of the appropriate SQL database but you should be able to adapt it to your own resources, or at least use it as a demonstrative example of what's possible. [% INCLUDE header title = 'User Info' %] [% USE DBI('dbi:mSQL:mydbname') %] <table border=0 width="100%"> <tr> <th>User ID</th> <th>Name</th> <th>Email</th> </tr> [% FOREACH user IN DBI.query('SELECT * FROM user ORDER BY id') %] <tr> <td>[% user.id %]</td> <td>[% user.name %]</td> <td>[% user.email %]</td> </tr> [% END %] </table> [% INCLUDE footer %] A plugin is simply a Perl module in a known location and conforming to a known standard such that the Template Toolkit can find and load it automatically. You can create your own plugin by inheriting from the L<Template::Plugin> module. Here's an example which defines some data items (C<foo> and C<people>) and also an object method (C<bar>). We'll call the plugin C<FooBar> for want of a better name and create it in the C<MyOrg::Template::Plugin::FooBar> package. We've added a C<MyOrg> to the regular C<Template::Plugin::*> package to avoid any conflict with existing plugins. package MyOrg::Template::Plugin::FooBar; use base 'Template::Plugin' our $VERSION = 1.23; sub new { my ($class, $context, @params) = @_; bless { _CONTEXT => $context, foo => 25, people => [ 'tom', 'dick', 'harry' ], }, $class; } sub bar { my ($self, @params) = @_; # ...do something... return $some_value; } The plugin constructor C<new()> receives the class name as the first parameter, as is usual in Perl, followed by a reference to something called a L<Template::Context> object. You don't need to worry too much about this at the moment, other than to know that it's the main processing object for the Template Toolkit. It provides access to the functionality of the processor and some plugins may need to communicate with it. We don't at this stage, but we'll save the reference anyway in the C<_CONTEXT> member. The leading underscore is a convention which indicates that this item is private and the Template Toolkit won't attempt to access this member. The other members defined, C<foo> and C<people> are regular data items which will be made available to templates using this plugin. Following the context reference are passed any additional parameters specified with the USE directive, such as the data source parameter, C<dbi:mSQL:mydbname>, that we used in the earlier DBI example. If you don't or can't install it to the regular place for your Perl modules (perhaps because you don't have the required privileges) then you can set the PERL5LIB environment variable to specify another location. If you're using C<ttree> then you can add the following line to your configuration file instead. $HOME/.ttreerc: perl5lib = /path/to/modules One further configuration item must be added to inform the toolkit of the new package name we have adopted for our plugins: $HOME/.ttreerc: plugin_base = 'MyOrg::Template::Plugin' If you're writing Perl code to control the L<Template> modules directly, then this value can be passed as a configuration parameter when you create the module. use Template; my $template = Template->new({ PLUGIN_BASE => 'MyOrg::Template::Plugin' }); Now we can create a template which uses this plugin: [% INCLUDE header title = 'FooBar Plugin Test' %] [% USE FooBar %] Some values available from this plugin: [% FooBar.foo %] [% FooBar.bar %] The users defined in the 'people' list: [% FOREACH uid = FooBar.people %] * [% uid %] [% END %] [% INCLUDE footer %] The C<foo>, C<bar>, and C<people> items of the FooBar plugin are automatically resolved to the appropriate data items or method calls on the underlying object. Using this approach, it is possible to create application functionality in a single module which can then be loaded and used on demand in any template. The simple interface between template directives and plugin objects allows complex, dynamic content to be built from a few simple template documents without knowing anything about the underlying implementation. =head1 AUTHOR Andy Wardley E<lt>abw@wardley.orgE<gt> L<http://wardley.org/> =head1 COPYRIGHT Copyright (C) 1996-2007 Andy Wardley. All Rights Reserved. This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut # Local Variables: # mode: perl # perl-indent-level: 4 # indent-tabs-mode: nil # End: # # vim: expandtab shiftwidth=4: Datafile.pod 0000644 00000034064 15126703322 0006771 0 ustar 00 #============================================================= -*-perl-*- # # Template::Tutorial::Datafile # # DESCRIPTION # # AUTHOR # Dave Cross <dave@dave.org.uk> # # COPYRIGHT # Copyright (C) 1996-2008 Andy Wardley. All Rights Reserved. # # This module is free software; you can redistribute it and/or # modify it under the same terms as Perl itself. # #======================================================================== =head1 NAME Template::Tutorial::Datafile - Creating Data Output Files Using the Template Toolkit =head1 DESCRIPTION =head1 Introducing the Template Toolkit There are a number of Perl modules that are universally recognised as The Right Thing To Use for certain tasks. If you accessed a database without using DBI, pulled data from the WWW without using one of the LWP modules or parsed XML without using XML::Parser or one of its subclasses then you'd run the risk of being shunned by polite Perl society. I believe that the year 2000 saw the emergence of another 'must have' Perl module - the Template Toolkit. I don't think I'm alone in this belief as the Template Toolkit won the 'Best New Module' award at the Perl Conference last summer. Version 2.0 of the Template Toolkit (known as TT2 to its friends) was recently released to the CPAN. TT2 was designed and written by Andy Wardley E<lt>abw@wardley.orgE<gt>. It was born out of Andy's previous templating module, Text::Metatext, in best Fred Brooks 'plan to throw one away' manner; and aims to be the most useful (or, at least, the most I<used>) Perl templating system. TT2 provides a way to take a file of fixed boilerplate text (the template) and embed variable data within it. One obvious use of this is in the creation of dynamic web pages and this is where a lot of the attention that TT2 has received has been focussed. In this article, I hope to demonstrate that TT2 is just as useful in non-web applications. =head1 Using the Template Toolkit Let's look at how we'd use TT2 to process a simple data file. TT2 is an object oriented Perl module. Having downloaded it from CPAN and installed it in the usual manner, using it in your program is as easy as putting the lines use Template; my $tt = Template->new; in your code. The constructor function, C<new>, takes a number of optional parameters which are documented in the copious manual pages that come with the module, but for the purposes of this article we'll keep things as simple as possible. To process the template, you would call the C<process> method like this $tt->process('my_template', \%data) || die $tt->error; We pass two parameters to C<process>, the first is the name of the file containing the template to process (in this case, my_template) and the second is a reference to a hash which contains the data items that you want to use in the template. If processing the template gives any kind of error, the program will die with a (hopefully) useful error message. So what kinds of things can go in C<%data>? The answer is just about anything. Here's an example showing data about English Premier League football teams. my @teams = ({ name => 'Man Utd', played => 16, won => 12, drawn => 3, lost => 1 }, { name => 'Bradford', played => 16, won => 2, drawn => 5, lost => 9 }); my %data = ( name => 'English Premier League', season => '2000/01', teams => \@teams ); This creates three data items which can be accessed within the template, called C<name>, C<season> and C<teams>. Notice that C<teams> is a complex data structure. Here is a template that we might use to process this data. League Standings League Name: [% name %] Season : [% season %] Teams: [% FOREACH team = teams -%] [% team.name %] [% team.played -%] [% team.won %] [% team.drawn %] [% team.lost %] [% END %] Running this template with this data gives us the following output League Standings League Name: English Premier League Season : 2000/01 Teams: Man Utd 16 12 3 1 Bradford 16 2 5 9 Hopefully the syntax of the template is simple enough to follow. There are a few points to note. =over 4 =item * Template processing directives are written using a simple language which is not Perl. =item * The keys of the C<%data> have become the names of the data variables within the template. =item * Template processing directives are surrounded by C<[%> and C<%]> sequences. =item * If these tags are replaced with C<[%-> C<-%]> then the preceding or following linefeed is suppressed. =item * In the C<FOREACH> loop, each element of the C<teams> list was assigned, in turn, to the temporary variable C<team>. =item * Each item assigned to the C<team> variable is a Perl hash. Individual values within the hash are accessed using a dot notation. =back It's probably the first and last of these points which are the most important. The first point emphasises the separation of the data acquisition logic from the presentation logic. The person creating the presentation template doesn't need to know Perl, they only need to know the data items which will be passed into the template. The last point demonstrates the way that TT2 protects the template designer from the implementation of the data structures. The data objects passed to the template processor can be scalars, arrays, hashes, objects or even subroutines. The template processor will just interpret your data correctly and Do The Right Thing to return the correct value to you. In this example each team was a hash, but in a larger system each team might be an object, in which case C<name>, C<played>, etc. would be accessor methods to the underlying object attributes. No changes would be required to the template as the template processor would realise that it needed to call methods rather than access hash values. =head2 A more complex example Stats about the English Football League are usually presented in a slightly more complex format than the one we used above. A full set of stats will show the number of games that a team has won, lost or drawn, the number of goals scored for and against the team and the number of points that the team therefore has. Teams gain three points for a win and one point for a draw. When teams have the same number of points they are separated by the goal difference, that is the number of goals the team has scored minus the number of team scored against them. To complicate things even further, the games won, drawn and lost and the goals for and against are often split between home and away games. Therefore if you have a data source which lists the team name together with the games won, drawn and lost and the goals for and against split into home and away (a total of eleven data items) you can calculate all of the other items (goal difference, points awarded and even position in the league). Let's take such a file, but we'll only look at the top three teams. It will look something like this: Man Utd,7,1,0,26,4,5,2,1,15,6 Arsenal,7,1,0,17,4,2,3,3,7,9 Leicester,4,3,1,10,8,4,2,2,7,4 A simple script to read this data into an array of hashes will look something like this (I've simplified the names of the data columns - w, d, and l are games won, drawn and lost and f and a are goals scored for and against; h and a at the front of a data item name indicates whether it's a home or away statistic): my @cols = qw(name hw hd hl hf ha aw ad al af aa); my @teams; while (<>) { chomp; my %team; @team{@cols} = split /,/; push @teams, \%team; } We can then go thru the teams again and calculate all of the derived data items: foreach (@teams) { $_->{w} = $_->{hw} + $_->{aw}; $_->{d} = $_->{hd} + $_->{ad}; $_->{l} = $_->{hl} + $_->{al}; $_->{pl} = $_->{w} + $_->{d} + $_->{l}; $_->{f} = $_->{hf} + $_->{af}; $_->{a} = $_->{ha} + $_->{aa}; $_->{gd} = $_->{f} - $_->{a}; $_->{pt} = (3 * $_->{w}) + $_->{d}; } And then produce a list sorted in descending order: @teams = sort { $b->{pt} <=> $b->{pt} || $b->{gd} <=> $a->{gd} } @teams; And finally add the league position data item: $teams[$_]->{pos} = $_ + 1 foreach 0 .. $#teams; Having pulled all of our data into an internal data structure we can start to produce output using out templates. A template to create a CSV file containing the data split between home and away stats would look like this: [% FOREACH team = teams -%] [% team.pos %],[% team.name %],[% team.pl %],[% team.hw %], [%- team.hd %],[% team.hl %],[% team.hf %],[% team.ha %], [%- team.aw %],[% team.ad %],[% team.al %],[% team.af %], [%- team.aa %],[% team.gd %],[% team.pt %] [%- END %] And processing it like this: $tt->process('split.tt', { teams => \@teams }, 'split.csv') || die $tt->error; produces the following output: 1,Man Utd,16,7,1,0,26,4,5,2,1,15,6,31,39 2,Arsenal,16,7,1,0,17,4,2,3,3,7,9,11,31 3,Leicester,16,4,3,1,10,8,4,2,2,7,4,5,29 Notice that we've introduced the third parameter to C<process>. If this parameter is missing then the TT2 sends its output to C<STDOUT>. If this parameter is a scalar then it is taken as the name of a file to write the output to. This parameter can also be (amongst other things) a filehandle or a reference to an object which is assumed to implement a C<print> method. If we weren't interested in the split between home and away games, then we could use a simpler template like this: [% FOREACH team = teams -%] [% team.pos %],[% team.name %],[% team.pl %],[% team.w %], [%- team.d %],[% team.l %],[% team.f %],[% team.a %], [%- team.aa %],[% team.gd %],[% team.pt %] [% END -%] Which would produce output like this: 1,Man Utd,16,12,3,1,41,10,6,31,39 2,Arsenal,16,9,4,3,24,13,9,11,31 3,Leicester,16,8,5,3,17,12,4,5,29 =head1 Producing XML This is starting to show some of the power and flexibility of TT2, but you may be thinking that you could just as easily produce this output with a C<foreach> loop and a couple of C<print> statements in your code. This is, of course, true; but that's because I've chosen a deliberately simple example to explain the concepts. What if we wanted to produce an XML file containing the data? And what if (as I mentioned earlier) the league data was held in an object? The code would then look even easier as most of the code we've written earlier would be hidden away in C<FootballLeague.pm>. use FootballLeague; use Template; my $league = FootballLeague->new(name => 'English Premier'); my $tt = Template->new; $tt->process('league_xml.tt', { league => $league }) || die $tt->error; And the template in C<league_xml.tt> would look something like this: <?xml version="1.0"?> <!DOCTYPE LEAGUE SYSTEM "league.dtd"> <league name="[% league.name %]" season="[% league.season %]"> [% FOREACH team = league.teams -%] <team name="[% team.name %]" pos="[% team.pos %]" played="[% team.pl %]" goal_diff="[% team.gd %]" points="[% team.pt %]"> <stats type="home"> win="[% team.hw %]" draw="[%- team.hd %]" lose="[% team.hl %]" for="[% team.hf %]" against="[% team.ha %]" /> <stats type="away"> win="[% team.aw %]" draw="[%- team.ad %]" lose="[% team.al %]" for="[% team.af %]" against="[% team.aa %]" /> </team> [% END -%] &/league> Notice that as we've passed the whole object into C<process> then we need to put an extra level of indirection on our template variables - everything is now a component of the C<league> variable. Other than that, everything in the template is very similar to what we've used before. Presumably now C<team.name> calls an accessor function rather than carrying out a hash lookup, but all of this is transparent to our template designer. =head1 Multiple Formats As a final example, let's suppose that we need to create output football league tables in a number of formats. Perhaps we are passing this data on to other people and they can't all use the same format. Some of our users need CSV files and others need XML. Some require data split between home and away matches and other just want the totals. In total, then, we'll need four different templates, but the good news is that they can use the same data object. All the script needs to do is to establish which template is required and process it. use FootballLeague; use Template; my ($name, $type, $stats) = @_; my $league = FootballLeague->new(name => $name); my $tt = Template->new; $tt->process("league_${type}_$stats.tt", { league => $league } "league_$stats.$type") || die $tt->error; For example, you can call this script as league.pl 'English Premier' xml split This will process a template called C<league_xml_split.tt> and put the results in a file called C<league_split.xml>. This starts to show the true strength of the Template Toolkit. If we later wanted to add another file format - perhaps we wanted to create a league table HTML page or even a LaTeX document - then we would just need to create the appropriate template and name it according to our existing naming convention. We would need to make no changes to the code. I hope you can now see why the Template Toolkit is fast becoming an essential part of many people's Perl installation. =head1 AUTHOR Dave Cross E<lt>dave@dave.org.ukE<gt> =head1 VERSION Template Toolkit version 2.19, released on 27 April 2007. =head1 COPYRIGHT Copyright (C) 2001 Dave Cross E<lt>dave@dave.org.ukE<gt> This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut # Local Variables: # mode: perl # perl-indent-level: 4 # indent-tabs-mode: nil # End: # # vim: expandtab shiftwidth=4: index.html 0000644 00000000425 15127642425 0006552 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Dokumenttitel </TITLE> </HEAD> <frameset Cols="25%,75%" noresize frameborder="0" > <frame Name="navi" src="navi.html"> <frame Name="inha" src="intro.html"> </FRAMESET> </HTML> odbc.css 0000755 00000015221 15127642425 0006201 0 ustar 00 /* Style Sheet for odbc documentation =20 M.Rathmann */ BODY {background-color: white; color: black; font-family: Arial; font-style: normal; font-size: medium; background-attachment: fixed; } /*=20 2. Die Verweise: Standard: Text unterstrichen link: #008080=20 vlink=3D"#800000" alink=3D"#008080" */ a:link {color: #2222ff; text-decoration: none; } a:visited {color: #3333ee; text-decoration: none; } a:active {color: #4444dd; text-decoration: none; } TH {border-style: none; background-color: #dddddd; font-family: Arial; font-size: normal; color: white; vertical-align: top; text-align: center; } TD {border-style: none; background-color: white; font-family: Arial; font-size: normal; color: black; border-width: 3px; border-color: white; vertical-align: top; text-align: justify; } TD.center {border-style: none; background-color: #ddddff; text-align: center; font-family: Arial; font-size: medium; color: black; border-width: 3px; border-color: white; vertical-align: top; } TD.head {border-style: none; background-color: #ddddff; text-align: left; font-family: Arial; font-size: medium; color: black; font-weight: bold; vertical-align: top; } Th.head {border-style: none; background-color: #dddddd;=09 text-align: center; font-family: Arial; font-size: medium; color: black; font-weight: bold; vertical-align: top; } TD.big { font-family: Arial; font-size: x-large; } TD.small { font-family: Arial; font-size: x-small; } /* 5. =DCberschriften immer in Rot, Zeichensatz Arial */ H4 { color: black; font-family: Arial; font-size: small; font-weight: bold; } H5 { color: black; font-size: x-small; font-family: courier; } /* 6. Blockquote St=E4rkere Einr=FCckung=20 */ Blockquote {margin-left: 10pt; } CODE.list {margin-left: 10 pt; font-size: xx-small; } } /* 7. Listen */ UL {color: black; font-family: Arial } OL {color: black; list-style-type: decimal} LI {color: black} P {font-family: Arial; font-size: normal; } TD P {font-family: Arial; font-size: normal; } small {font-family: Arial; font-size: x-small } From - Sun Jun 6 14:11:04 1999 Return-path: <M.Rathmann@ping.de> Envelope-to: pharvey@interlog.com Delivery-date: Sun, 6 Jun 1999 03:55:51 -0400 Received: from plus.interlog.com ([207.34.202.21] ident=root) by mailhub4.interlog.com with esmtp (Exim 2.05 #1) id 10qXmY-0002kw-00 for pharvey@interlog.com; Sun, 6 Jun 1999 03:55:50 -0400 Received: from lilly.ping.de (qmailr@lilly.ping.de [195.37.120.2]) by plus.interlog.com (8.9.3/8.9.3) with SMTP id DAA23367 for <pharvey@codebydesign.com>; Sun, 6 Jun 1999 03:55:48 -0400 (EDT) Received: (qmail 26070 invoked by alias); 6 Jun 1999 07:55:45 -0000 Received: (qmail 26063 invoked from network); 6 Jun 1999 07:55:37 -0000 Received: from suprimo-54.ping.de (HELO koala) (195.37.122.54) by lilly.ping.de with SMTP; 6 Jun 1999 07:55:37 -0000 Message-ID: <001a01beaff2$66b90320$030aa8c0@koala> From: "Markus Rathmann" <M.Rathmann@ping.de> To: "Peter Harvey" <pharvey@codebydesign.com> Subject: The newest docu, to be sure :) Date: Sun, 6 Jun 1999 09:58:43 +0200 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0017_01BEB003.29819060" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.1 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 X-Mozilla-Status: 8001 X-Mozilla-Status2: 00000000 X-UIDL: ea3859ca9b1d73dac919ee5e69391b0b This is a multi-part message in MIME format. ------=_NextPart_000_0017_01BEB003.29819060 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi Peter, I'm sorry, but I think that you haven't got the newest version. Therefore I'm sending it to you again. It has one more page (Obtaining Datasource names) and a few display glitches (mainly Netscape) solved. Furthermore I've added a link to the unixODBC Homepage, so that you may use the complete browser window (if you like). Bye Markus ------=_NextPart_000_0017_01BEB003.29819060 Content-Type: text/css; name="ODBC.CSS" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="ODBC.CSS" /* Style Sheet for odbc documentation =20 M.Rathmann */ BODY {background-color: white; color: black; font-family: Arial; font-style: normal; font-size: medium; background-attachment: fixed; } /*=20 2. Die Verweise: Standard: Text unterstrichen link: #008080=20 vlink=3D"#800000" alink=3D"#008080" */ a:link {color: #2222ff; text-decoration: none; } a:visited {color: #3333ee; text-decoration: none; } a:active {color: #4444dd; text-decoration: none; } TH {border-style: none; background-color: #dddddd; font-family: Arial; font-size: normal; color: white; vertical-align: top; text-align: center; } TD {border-style: none; background-color: white; font-family: Arial; font-size: normal; color: black; border-width: 3px; border-color: white; vertical-align: top; text-align: justify; } TD.center {border-style: none; background-color: #ddddff; text-align: center; font-family: Arial; font-size: medium; color: black; border-width: 3px; border-color: white; vertical-align: top; } TD.head {border-style: none; background-color: #ddddff; text-align: left; font-family: Arial; font-size: medium; color: black; font-weight: bold; vertical-align: top; } Th.head {border-style: none; background-color: #dddddd;=09 text-align: center; font-family: Arial; font-size: medium; color: black; font-weight: bold; vertical-align: top; } TD.big { font-family: Arial; font-size: x-large; } TD.small { font-family: Arial; font-size: x-small; } /* 5. =DCberschriften immer in Rot, Zeichensatz Arial */ H4 { color: black; font-family: Arial; font-size: small; font-weight: bold; } H5 { color: black; font-size: x-small; font-family: courier; } /* 6. Blockquote St=E4rkere Einr=FCckung=20 */ Blockquote {margin-left: 10pt; } CODE.list {margin-left: 10 pt; font-size: xx-small; } } /* 7. Listen */ UL {color: black; font-family: Arial } OL {color: black; list-style-type: decimal} LI {color: black} P {font-family: Arial; font-size: normal; } TD P {font-family: Arial; font-size: normal; } small {font-family: Arial; font-size: x-small } query.html 0000644 00000007020 15127642425 0006606 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>executing a query</TITLE> <link rel="stylesheet" Href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Executing a query</TD> </TR> <TR> <TD colspan="3"><P>If you want to execute a query you will need to specify a handle (<CODE>SQL_HANDLE_STMT</CODE>) for a SQL-statement. In order to get one you have to allocate one with <CODE><A HREF="gloss.html#alloc">SQLAllocHandle</A></CODE>. </P> Then you have to think about the SQL statement you want to execute. As I mentioned in the introduction I assume that we have a table <CODE><A HREF="intro.html#tab">tkeyuser</A></CODE> which contains the following data:</P> <TABLE COLS=3 cellspacing="1" border="0" bgcolor="#eeeeee" cellpadding=2 width="50%"> <TR> <TH class="head">iduser</TH> <TH class="head">dtname</TH> <TH class="head">dtmaxSize</TH> </TR> <TR> <TD>1</TD> <TD>Christa</TD> <TD>10000</TD> </TR> <TR> <TD>2</TD> <TD>Nicole</TD> <TD>9000</TD> </TR> </TABLE> <P> In this example, we want to execute a query which returns all the rows for the fields <CODE>iduser</CODE> and <CODE>dtname</CODE> in this table ordered by <code>iduser</CODE>. So the SQL statement would be:</P> <CODE> SELECT iduser,dtname FROM tkeydata ORDER BY iduser </CODE> <P> If you execute this statement you would get two rows each with two <A HREF="gloss.html#col">columns</A> of data. This data has to be stored somewhere so that your programm can actually use it, so you need to define a variable for each of the columns. So you need to bind a column to variable in your program. Binding a variable automatically stores the data of the column in the variable when you retrieve a result <A HREF="gloss.html#row">row</A> from the connection. It is important that your variables match the <A HREF="gloss.html#dtyp">type</A> of the column in the table within the database.</P> <P> So we need to bin column #1 to a variable of type <CODE>SQLINTEGER</CODE> and the second column to a variable of type <CODE>char</CODE>. This is done by <CODE><A HREF="gloss.html#bind">SQLBindCol</A></CODE>. Therefore we add the variables:</P> <CODE class="list"> SQLHSTMT V_OD_hstmt; <FONT COLOR="blue">// Handle for a statement</FONT><BR> SQLINTEGER V_OD_err,V_OD_id;<BR> char V_OD_buffer[200]; </CODE> <P> Now we can bind the variables:</P> <CODE class="list"><PRE> SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,200,&V_OD_err); SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,sizeof(V_OD_id),&V_OD_err); </PRE> </CODE> <P> Yes you should check for the return code of the function call. I'm to lazy to code it here once again :( </P> Now we can execute the query by calling <A HREF="gloss.html#exec"><CODE>SQLExecDirect</CODE></A>: </P> <CODE class="list"><PRE> V_OD_erg=SQLExecDirect(V_OD_hstmt, "SELECT dtname,iduser FROM tkeyuser order by iduser",SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error Select %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err, V_OD_msg,100,&V_OD_mlen); printf("%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } </PRE> </CODE> </TD> </TR> </TABLE> </BODY> </HTML> conne.html 0000644 00000011652 15127642425 0006551 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Connecting </TITLE> <LINK REL="StyleSheet" Href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Connecting to a Datasource</TD> </TR> <TR> <TD colspan="3"><P>First thing you will need is a variable of type <CODE>SQLHENV</CODE>. This is a handle (pointer) to an internal ODBC structure which holds all informationen about the ODBC environment. Without a handle of that kind you won't be able do to very much. To get this handle you call <CODE><A HREF="gloss.html#alloc">SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &V_OD_Env)</A></CODE>. <CODE>V_OD_Erg</CODE> is a variable of type <CODE>SQLHENV</CODE> which holds the allocated environment handle.</P> <P> If you have allocated the handle you need to define which version of ODBC to use. Therefore you should call <CODE><A HREF="gloss.html#envattr">SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0)</A></CODE>. The constant <CODE>SQL_ATTR_ODBC_VERSION</CODE> defines that the needed version of ODBC will be defined and <CODE>SQL_OV_ODBC3</CODE> says that the program will need ODBC 3.0. </P> <P>Next thing to do is to create a handle for the database connection which is of the type <CODE>SQLHDBC</CODE>. Once again you call <CODE>SQLAllocHandle</CODE> this time with <CODE>SQL_HANDLE_DBC</CODE> and the variable to the environment returned by the first call to <CODE>SQLAllocHandle</CODE>. </P><P> Then you may choose to modify the connection attributes, mainly the timeout for any given action on the connection. You do this by calling <CODE><A HREF="gloss.html#conattr">SQLSetConnectAttr</A></CODE> with the connection handle, attribute and value pointer and the string length (if available).</P> <P> Finally we are able to connect to the database via <CODE><A href="gloss.html#conn">SQLConnect</A></CODE>, which needs the name of the data source, the username and password as parameters. For each parameter you need to specify how long the string is or just gib <COde>SQL_NTS</CODE> which says that it is a string which length has to be determined by <CODE>SQLConnect</CODE> </P> That's it, we are connected to the database. Please note, that all functions mentioned on this page return either <CODE>SQL_SUCCESS</CODE>, <CODE>SQL_SUCCESS_WITH_INFO</CODE> if all went smoothly or <CODE>SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE> in case of an error. We will have a look on how to get diagnostic messages a little later. <P> So let's have a look at the code: </TD> </TR> </TABLE> <TABLE> <TR> <TD> <PRE><CODE class="list"> <A NAME="list"></A> /* odbc.c testing unixODBC */ #include <stdlib.h> #include <stdio.h> #include <odbc/sql.h> #include <odbc/sqlext.h> #include <odbc/sqltypes.h> SQLHENV V_OD_Env; <FONT COLOR="green">// Handle ODBC environment</FONT> long V_OD_erg; <FONT COLOR="green">// result of functions</FONT> SQLHDBC V_OD_hdbc; <FONT COLOR="green">// Handle connection</FONT> char V_OD_stat[10]; <FONT COLOR="green">// Status SQL</FONT> SQLINTEGER V_OD_err,V_OD_rowanz,V_OD_id; SQLSMALLINT V_OD_mlen; char V_OD_msg[200],V_OD_buffer[200]; int main(int argc,char *argv[]) { <FONT COLOR="green">// 1. allocate Environment handle and register version </FONT> V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHandle\n"); exit(0); } V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } <FONT COLOR="green">// 2. allocate connection handle, set timeout</FONT> V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHDB %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0); <FONT COLOR="green">// 3. Connect to the datasource "web" </FONT> V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "web", SQL_NTS, (SQLCHAR*) "christa", SQL_NTS, (SQLCHAR*) "", SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SQLConnect %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf("%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Connected !\n"); <FONT COLOR="green">/* continued on next page */</FONT> </CODE> </PRE> </TD> </TR> </TABLE> </BODY> </HTML> dsn.html 0000644 00000005134 15127642425 0006231 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Obtainign Datasources</TITLE> <LINK REL="Stylesheet" href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Obtaining a Datasourcename</TD> </TR> <TR> <TD colspan="3"><P>A simple query should be a nobrainer right now. But what if your programm runs on a system where you can't be sure which datasource names are configured?</P> <P>Then you should use <A hrEF="gloss.html#dsn"><CODE>SQLDataSources()</CODE></a>. After allocating a environment handle you may use this to find out about the DSN and the supplied description for the datasource.</P> <P>As ODBC knows systemwide and userwide datasources, you need to give a direction which datasource types you are looking for. There you may specify either of the following values:</P> <TABLE COLS="2" border="0" width="90%"> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of all available datasources (either user or systemwide). </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST_USER</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of the available user datasources. </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST_SYSTEM</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of the available system datasources. </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_NEXT</CODE></TD> <TD>Fetches the next datasource. Depending on <CODE>SQL_FETCH_FIRST_USER</CODE>, <CODE>SQL_FETCH_FIRST_SYSTEM</CODE> or <CODE>SQL_FETCH_FIRST</CODE> this may only be a user datasource, only a system datasource or one of either. </TR> </TABLE> <P>So let's have a look on a small function, which will return all available datasource names. You may insert this code into the program which you built before and call it somewhere after you've obtained an environment handle.</P> <CODE class="list"><PRE> void OD_ListDSN(void) { char l_dsn[100],l_desc[100]; short int l_len1,l_len2,l_next; l_next=SQL_FETCH_FIRST; while( SQLDataSources(V_OD_Env,l_next,l_dsn, sizeof(l_dsn), &l_len1, l_desc, sizeof(l_desc), &l_len2) == SQL_SUCCESS) { printf("Server=(%s) Beschreibung=(%s)\n",l_dsn,l_desc); l_next=SQL_FETCH_NEXT; } } </PRE></CODE> </TD> </TR> </TABLE> </BODY> </HTML> navi.html 0000644 00000001777 15127642425 0006413 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Navigation </TITLE> <LINK REL="stylesheet" href="odbc.css"> </HEAD> <BODY bgcolor="white"> <TABLE Width="90%" cellspacing="1" bgcolor="black"> <TR> <TD class="center">ODBC Programming Tutorial</TD> </TR> </TABLE> <TABLE Width="90%"> <TR> <TD> </TD> </TR> <TR> <TD><a href="intro.html" target="inha">Introduction</A> </TD> </TR> <TR> <TD><a href="conne.html" target="inha">Connection</A> </TD> </TR> <TR> <TD><a href="close.html" target="inha">Closing</A> </TD> </TR> <TR> <TD><a href="query.html" target="inha">Queries</A> </TD> </TR> <TR> <TD><A href="resul.html" target="inha">Results</A> </TD> </TR> <TR> <TD><A href="dsn.html" target="inha">Obtaining DSN</A> </TD> </TR> <TR> <TD> <A Href="gloss.html" target="inha">Index / Glossary</A> </TD> </TR> <TR> <TD> <A Href="http://genix.net/unixODBC/" target="_top">unixODBC Home</A> </TD> </TR> </TABLE> </BODY> </HTML> close.html 0000644 00000002704 15127642425 0006552 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Closing a connection</TITLE> <LINK REL="Stylesheet" href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Closing a connection</TD> </TR> <TR> <TD colspan="3"><P>Before your program terminates you need to free all resources you have allocated. If you checked the source on the previous page, you certainly spotted <CODE><A HREF="gloss.html#free">SQLFreeHandle</A></CODE>. This function must be used to free each allocated handle. It expects a parameter which states the type of the handle to be freed and the handle itself. So if you want to free an environment handle you should call (in our example program):</P> <CODE> SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); </CODE> <P> Before you free any handle make sure that it is no longer needed, wouldn't be funny, if you released to connection handle but forgot to close the connection ;)</P> <P> And if you want to close a connection you need <CODE><A HREF="gloss.html#clos">SQLDisconnect</A></CODE>. This closes the connection associated with the connection handle offered as argument to <CODE>SQLDisconnect</CODE>. In our programm we need to call:</P> <CODE> SQLDisconnect(V_OD_hdbc); </CODE> <P> If you need source code, please have a look <A HREF="conne.html#list">here</A>.</P> </TD> </TR> </TABLE> </BODY> </HTML> resul.html 0000644 00000015454 15127642425 0006605 0 ustar 00 <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Fetching Data</TITLE> <LINK REL="StyleSheet" Href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Fetching data from a result set</TD> </TR> <TR> <TD colspan="3"><P>If the execution of the statement went fine you are now able to fetch the data <A Href="gloss.html#col">column</A> by column. May be you would first like to know how many columns there are in the result set (if you use a <CODE> SELECT * FROM tkeyuser</CODE> you wouldn't know in your program). A call to <a href="gloss.html#ccol"><CODE>SQLNumResultCols</CODE></A>. This function takes the statement handle and a pointer to an integer variable which will yield the number of columns after the call.</P> Knowing that much we can add this to our program: <CODE><PRE> <FONT COLOR="green">// At the beginning add:</FONT> SQLSMALLINT V_OD_colanz; <FONT COLOR="green">// Num of columns</FONT> <FONT COLOR="green">// At the end add:</FONT> V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Fehler im ResultCols %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Number of Columns %d\n",V_OD_colanz); </PRE></CODE> <P> The next thing you need to know is how many <A href="gloss.html#row">rows</A> have been returned by the query. A call to <A HREF="gloss.html#crow"><CODE>SQLRowCount</CODE></A> should quench your thirst for knowledge. </P> <P> The last thing to do is to fetch the data itself from the result set. You should call <A HREF="gloss.html#fetc"><CODE>SQLFetch</CODE></A> with a statement handle (which has been allocated and <CODE>SQLBind</CODE> has been called for all columns). <CODE>SQLFetch</CODE> returns <CODE>SQL_NO_DATA</CODE> if there is no more data in the result set. </P> <P>So here is the complete source code. Real C-programmers will moan in disgust how inefficient the program is coded, but I prefer it that way (doesn't it look a little bit like PASCAL?). Take it as a starting point for your own endeavors with ODBC.</P> <CODE class="list"><PRE> /* odbc.c testing unixODBC */ #include <stdlib.h> #include <stdio.h> #include <odbc/sql.h> #include <odbc/sqlext.h> #include <odbc/sqltypes.h> SQLHENV V_OD_Env; <FONT COLOR="green">// Handle ODBC environment</FONT> long V_OD_erg; <FONT COLOR="green">// result of functions</FONT> SQLHDBC V_OD_hdbc; <FONT COLOR="green">// Handle connection</FONT> char V_OD_stat[10]; <FONT COLOR="green">// Status SQL</FONT> SQLINTEGER V_OD_err,V_OD_rowanz,V_OD_id; SQLSMALLINT V_OD_mlen,V_OD_colanz; char V_OD_msg[200],V_OD_buffer[200]; int main(int argc,char *argv[]) { <FONT COLOR="green">// 1. allocate Environment handle and register version </FONT> V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHandle\n"); exit(0); } V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } <FONT COLOR="green">// 2. allocate connection handle, set timeout</FONT> V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHDB %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0); <FONT COLOR="green">// 3. Connect to the datasource "web" </FONT> V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "web", SQL_NTS, (SQLCHAR*) "christa", SQL_NTS, (SQLCHAR*) "", SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SQLConnect %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); printf("%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Connected !\n"); V_OD_erg=SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Fehler im AllocStatement %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen); printf("%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err); SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err); V_OD_erg=SQLExecDirect(V_OD_hstmt,"SELECT dtname,iduser FROM tkeyuser order by iduser",SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error in Select %d\n",V_OD_erg); SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen); printf("%s (%d)\n",V_OD_msg,V_OD_err); SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Number of Columns %d\n",V_OD_colanz); V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Number ofRowCount %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Number of Rows %d\n",V_OD_rowanz); V_OD_erg=SQLFetch(V_OD_hstmt); while(V_OD_erg != SQL_NO_DATA) { printf("Result: %d %s\n",V_OD_id,V_OD_buffer); V_OD_erg=SQLFetch(V_OD_hstmt); } ; SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); return(0); } </PRE> </CODE> If I find some more time I will add something about diagnostics, cursor positioning updating and and and... </TD> </TR> </TABLE> </BODY> </HTML> gloss.html 0000644 00000047222 15127642425 0006600 0 ustar 00 <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Developer Studio"> <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1"> <TITLE>Glossary </TITLE> <LINK REL="StyleSheet" Href="odbc.css"> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0" cellspacing="0"> <TR> <TD colspan="3" class="big">Index / Glossary</TD> </TR> <TR> <TD colspan="3"> <TABLE Cols="13" width=90% bgcolor="black"> <TR> <TH class="head"><a href="#A">A</A></TH> <TH CLASS="head"><a href="#B">B</A></TH> <TH CLASS="head"><a href="#C">C</A></TH> <TH CLASS="head"><a href="#D">D</A></TH> <TH CLASS="head"><a href="#E">E</A></TH> <TH CLASS="head"><a href="#F">F</A></TH> <TH CLASS="head"><a href="#G">G</A></TH> <TH CLASS="head"><a href="#H">H</A></TH> <TH CLASS="head"><a href="#I">I</A></TH> <TH CLASS="head"><a href="#J">J</A></TH> <TH CLASS="head"><a href="#K">K</A></TH> <TH CLASS="head"><a href="#L">L</A></TH> <TH CLASS="head"><a href="#M">M</A></TH> </TR> <TR> <TH CLASS="head"><a href="#N">N</A></TH> <TH CLASS="head"><a href="#O">O</A></TH> <TH CLASS="head"><a href="#P">P</A></TH> <TH CLASS="head"><a href="#Q">Q</A></TH> <TH CLASS="head"><a href="#R">R</A></TH> <TH CLASS="head"><a href="#S">S</A></TH> <TH CLASS="head"><a href="#T">T</A></TH> <TH CLASS="head"><a href="#U">U</A></TH> <TH CLASS="head"><a href="#V">V</A></TH> <TH CLASS="head"><a href="#W">W</A></TH> <TH CLASS="head"><a href="#X">X</A></TH> <TH CLASS="head"><a href="#Y">Y</A></TH> <TH CLASS="head"><a href="#Z">Z</A></TH> </TR> </TABLE> </TD> </TR> </TABLE> <table width="90%" cols="3" border="0" cellspacing="0"> <TR> <TD class="head" width="60"><a name="C"><B>C</B></A></TD> <TD width="20" class="head"> </TD> <TD class="head"><A Name="col">Column</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD><P>A column is another name for field in a SQL table. It has a data type (Integer, Char, Money etc) and a name by which it is addressed.</P> <P>You specify the name of a column in a query (either DELETE, UPDATE, SELECT or INSERT)</P> </TD> </TR> <TR> <TD class="head" width="60"><a name="D"><B>D</B></A></TD> <TD width="20" class="head"> </TD> <TD class="head"><A Name="dsrc">data source</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>A data source defines all informationen needed by ODBC to connect to a database. This includes the name of the driver to use (Postgres, mySQL etc.), the name of the user, his password, the server name on which the database resides and of course the name of the database. There are a lot more options available. </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="dtyp">Data Types</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD><P> The following table show some ODBC data types and how the relate to standard C data types:</P> <TABLE COLS="3" border="0" cellspacing="1"> <TR> <TH CLASS="head">Type identifier</TH> <TH CLASS="head">ODBC typedef</TH> <TH CLASS="head">C typedef</TH> </TR> <TR> <TD class="small">SQL_C_CHAR</TD> <TD class="small">SQLCHAR * </TD> <TD class="small">unsigned char * </TD> </TR> <TR> <TD class="small">SQL_C_SSHORT</TD> <TD class="small">SQLSMALLINT</TD> <TD class="small">short int </TD> </TR> <TR> <TD class="small">SQL_C_USHORT </TD> <TD class="small">SQLUSMALLINT </TD> <TD class="small">unsigned short int </TD> </TR> <TR> <TD class="small">SQL_C_SLONG </TD> <TD class="small">SQLINTEGER</TD> <TD class="small">long int</TD> </TR> <TR> <TD class="small">SQL_C_FLOAT</TD> <TD class="small">SQLREAL</TD> <TD class="small">float </TD> </TR> <TR> <TD class="small">SQL_C_DOUBLE</TD> <TD class="small">SQLDOUBLE, SQLFLOAT</TD> <TD class="small">double </TD> </TR> <TR> <TD class="small">SQL_C_BINARY </TD> <TD class="small">SQLCHAR *</TD> <TD class="small">unsigned char </TD> </TR> <TR> <TD class="small">SQL_C_TYPE_DATE</TD> <TD class="small">SQL_DATE_STRUCT</TD> <TD class="small"> struct <BR>tagDATE_STRUCT {<BR> SQLSMALLINT year; <BR> SQLUSMALLINT month; <BR> SQLUSMALLINT day; <BR> } DATE_STRUCT; </TD> </TR> <TR> <TD class="small">SQL_C_TYPE_TIME </TD> <TD class="small">SQL_TIME_STRUCT </TD> <TD class="small">struct<BR>tagTIME_STRUCT {<BR> SQLUSMALLINT hour; <BR> SQLUSMALLINT minute; <BR> SQLUSMALLINT second; <BR> } TIME_STRUCT; </TD> </TR> </TABLE> <P>You will need the type identifier in calls to <A HREF="#bind">SQLBindCol</A>. </TD> </TR> <TR> <TD colspan="2"> </TD> <TD > </TD> </TR> <TR> <TD class="head" width="60"><a name="O"><B>O</B></A></TD> <TD width="20" class="head"> </TD> <TD class="head"><A Name="oini">odbc.ini</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD><CODE>/etc/odbc.ini</CODE> is the configuration file for system <A HREF="#dsrc">data sources</A>. It contains information which will be needed when connecting to a database. It is modified by a graphical utility ODBCConfig. </TD> </TR> <TR> <TD class="head" width="60"><a name="R"><B>R</B></A></TD> <TD width="20" class="head"> </TD> <TD class="head"><A Name="row">Row</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>A row is a set of <A HREF="#col">columns</A> in a query. For example in our <A HREF="intro.html#tab">table</A> there are two users. Each user makes up a row in the table or in the result of our query. </TD> </TR> <TR> <TD class="head" width="60"><a name="S"><B>S</B></A></TD> <TD width="20" class="head"> </TD> <TD class="head"><A Name="alloc">SQLAllocHandle</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>allocates needed handles. <CODE><PRE> SQLRETURN SQLAllocHandle(SQLSMALLINT HandleType, SQLHANDLE InputHandle, SQLHANDLE *OutputHandlePtr); </PRE></CODE> <H4>Arguments</H4> <h5>HandleType</h5> <P> Defines the type of handle to be allocated by <CODE>SQLAllocHandle</CODE>. There are four possible values:</P> <CODE> SQL_HANDLE_ENV<BR> SQL_HANDLE_DBC<BR> SQL_HANDLE_STMT<BR> SQL_HANDLE_DESC </CODE> <H5>InputHandle</H5> This is the input handle in whose context the new handle will be allocated. If <CODE>HandleType</CODE> is <CODE>SQL_HANDLE_ENV</CODE>, this is <CODE>SQL_NULL_HANDLE</CODE>. For a handle of type <CODE>SQL_HANDLE_DBC</CODE>, this has to be an environment handle, and if it is <CODE>SQL_HANDLE_STMT</CODE> or <CODE>SQL_HANDLE_DESC</CODE>, it must be a connection handle. <H5>OutputHandlePtr</H5> Pointer to a buffer in which to return the allocated handle. <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>. <HR width="20%" align="center"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="bind">SQLBindCol</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > binds a variable to a column in the result. <CODE><PRE> SQLRETURN SQLBindCol(SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValuePtr, SQLINTEGER BufferLength, SQLINTEGER *StrLen_or_IndPtr); </PRE></CODE> <H4>Arguments</H4> <H5>StatementHandle</H5> <CODE>StatementHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and will hold all information and the result set of the statement. <H5>ColumnNumber</H5> Number of the column in the result set. Starts with 1. <H5>TargetType</H5> Type identifier of the <A href="#dtyp">data type</A> <H5>TargetValuePtr</H5> The pointer to the variable in which the data will be stored. <H5>BufferLength</H5> The size of the buffer <CODE>TargetValuePtr</CODE> points at in bytes. <H5>StrLen_or_IndPtr</H5> When data is fetched, returns either <UL> <LI>The length of the data available to return <LI>SQL_NO_TOTAL <LI>SQL_NULL_DATA </UL> <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE>. <HR align="center" width="20%"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="conn">SQLConnect</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > connects to a datasource <CODE><PRE> SQLRETURN SQLConnect(SQLHDBC ConnectionHandle, SQLCHAR *ServerName, SQLSMALLINT NameLength1, SQLCHAR *UserName, SQLSMALLINT NameLength2, SQLCHAR *Authentication, SQLSMALLINT NameLength3); </PRE></CODE> <H4>Arguments</H4> <H5>ConnectionHandle</H5> <CODE>ConnectionHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and will hold all information about the connection. <H5>ServerName</H5> Name of the database server <H5>NameLength1</H5> The length of <CODE>ServerName</CODE> or <CODE>SQL_NTS</CODE> <H5>UserName</H5> The name of the user who connects to the database. <H5>NameLength2</H5> The length of <CODE>UserName</CODE> or <CODE>SQL_NTS</CODE> <H5>Authentication</H5> Password of the user <H5>NameLength3</H5> The length of <CODE>Authentication</CODE> or <CODE>SQL_NTS</CODE> <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE>. <HR align="center" width="20%"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="dsn">SQLDataSources</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > fetches avaible datasource names either user, system or both. <CODE><PRE> SQLRETURN SQLDataSources(SQLHENV EnvironmentHandle, SQLUSMALLINT Direction, SQLCHAR *ServerName, SQLSMALLINT BufferLength1, SQLSMALLINT *NameLength1Ptr, SQLCHAR *Description, SQLSMALLINT BufferLength2, SQLSMALLINT *NameLength2Ptr); </PRE></CODE> <H4>Arguments</H4> <H5>EnvironmentHandle</H5> <CODE>EnvironmentHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE>. <H5>Direction</H5> Which DSN we are looking for. May be on of: <TABLE COLS="2" border="0"> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of all available datasources (either user or systemwide). </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST_USER</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of the available user datasources. </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_FIRST_SYSTEM</CODE></TD> <TD>Sets up <CODE>SQLDataSources()</CODE> to lookup the first of the available system datasources. </TR> <TR> <TD valign="top"><CODE>SQL_FETCH_NEXT</CODE></TD> <TD>Fetches the next datasource. Depending on <CODE>SQL_FETCH_FIRST_USER</CODE>, <CODE>SQL_FETCH_FIRST_SYSTEM</CODE> or <CODE>SQL_FETCH_FIRST</CODE> this may only be a user datasource, only a system datasource or one of either. </TR> </TABLE> <H5>ServerName</H5> The name of the datasource is returned herein. <H5>BufferLength1</H5> Defines how many chars <I>Servername</I> may contain at most. <H5>NameLength1Ptr</H5> The pointer to the variable in which the actual length of the datasource name is stored. If <I>NameLength1Ptr</I> is greater than <I>BufferLength1</I>, then the DSN in <I>ServerName</I> is truncated to fit. <H5>BufferLength</H5> The size of the buffer <CODE>TargetValuePtr</CODE> points at in bytes. <H5>Description</H5> The description supplied with the datasource, giving more information on the datasource in human readable form. <H5>BufferLength2</H5> Defines how many chars <I>Description</I> may contain at most. <H5>NameLength2Ptr</H5> The pointer to the variable in which the actual length of the description is stored. If <I>NameLength2Ptr</I> is greater than <I>BufferLength2</I>, then the description in <I>Description</I> is truncated to fit. <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE>, <CODE>SQL_NO_DATA></CODE> or <CODE>SQL_INVALID_HANDLE</CODE>. <HR align="center" width="20%"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="exec">SQLExecDirect</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > Executes a SQL statement <CODE><PRE> SQLRETURN SQLExecDirect(SQLHSTMT StatementHandle, SQLCHAR *StatementText, SQLINTEGER TextLength); </PRE></CODE> <H4>Arguments</H4> <H5>StatementHandle</H5> <CODE>StatementHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and will hold all information and the result set of the statement. <H5>StatementText</H5> The SQL statement to be executed <H5>TextLength</H5> The length of <CODE>StatementText</CODE> or <CODE>SQL_NTS</CODE> <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE>. <HR align="center" width="20%"> </TD> </TR> <TR> <TD width="20" colspan="2"> </TD> <TD class="head"><A Name="clos">SQLDisconnect</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>disconnects the specified connection <CODE><PRE> SQLRETURN SQLDisconnect(SQLHDBC ConnectionHandle); </PRE></CODE> <H4>Arguments</H4> <H5>ConnectionHandle</H5> The handle of the connection to be closed. <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>. <HR width="20%" align="center"> </TD> </TR> <TR> <TD width="20" colspan="2"> </TD> <TD class="head"><A Name="fetc">SQLFetch</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>Fetches the next row of the result set. <CODE><PRE> SQLRETURN SQLFetch(SQLHDBC StatementHandle); </PRE></CODE> <H4>Arguments</H4> <H5>StatementHandle</H5> The handle of the statement to be closed fromwhich the data should be fetched. <H4>Returns</H4> <P><CODE>SQL_SUCCESS, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>.</P> <HR width="20%" align="center"> </TD> </TR> <TR> <TD width="20" colspan="2"> </TD> <TD class="head"><A Name="free">SQLFreeHandle</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD> frees allocated handles. <CODE><PRE> SQLRETURN SQLFreeHandle(SQLSMALLINT HandleType, SQLHANDLE InputHandle); </PRE></CODE> <H4>Arguments</H4> <h5>HandleType</h5> <P> Defines the type of handle to be freed. There are four possible values:</P> <CODE> SQL_HANDLE_ENV<BR> SQL_HANDLE_DBC<BR> SQL_HANDLE_STMT<BR> SQL_HANDLE_DESC </CODE> <H5>InputHandle</H5> The handle to be freed. Should match the type stated by <CODE>HandleType</CODE> <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>. <HR width="20%" align="center"> </TD> </TR> <TR> <TD width="20" colspan="2"> </TD> <TD class="head"><A Name="ccol">SQLNumResultCols</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>returns the number of columns in the result set. <CODE><PRE> SQLRETURN SQLNumResultCols(SQLHSTMT StatementHandle, SQLSMALLINT *ColumnCountPtr); </PRE></CODE> <H4>Arguments</H4> <H5>StatementHandle</H5> <CODE>StatementHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and holds all information and the result set of the statement. <h5>ColumnCountPtr</H5> A pointer to a variable to hold the result value. <H4>Returns</H4> <P><CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>.</P> <HR width="20%" align="center"> </TD> </TR> <TR> <TD width="20" colspan="2"> </TD> <TD class="head"><A Name="crow">SQLRowCount</A></TD> </TR> <TR><TD Colspan="2"> </TD> <TD>returns the number of rows affected by INSERT, UPDATE or DELETE. Many drivers (but not all) return the number of rows returned by the last executed SELECT statement too. <CODE><PRE> SQLRETURN SQLSQLRowCount(SQLHSTMT StatementHandle, SQLSMALLINT *RowCountPtr); </PRE></CODE> <H4>Arguments</H4> <H5>StatementHandle</H5> <CODE>StatementHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and holds all information and the result set of the statement. <h5>RowCountPtr</H5> A pointer to a variable to hold the result value. <H4>Returns</H4> <P><CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_INVALID_HANDLE,</CODE> or <CODE>SQL_ERROR</CODE>.</P> <HR width="20%" align="center"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="conattr">SQLSetConnectAttr</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > modifies attributes of connections. <CODE><PRE> SQLRETURN SQLSetConnectAttr(SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength); </PRE></CODE> <H4>Arguments</H4> <H5>ConnectionHandle</H5> <CODE>ConnectionHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> and defines the connection which will be modified. <H5>Attribute</H5> which attribute to set <H5>ValuePtr</H5> Pointer to the value for <CODE>Attribute</CODE>. Depending on <CODE>Attribute</CODE>, <CODE>ValuePtr</CODE> will be a 32-bit integer value or a pointer to a null-terminated string. <H5>StringLength</H5> If <CODE>ValuePtr</CODE> points to a character string or a binary buffer, this argument should be the length of <CODE>*ValuePtr</CODE>. Otherwise, for <CODE>ValuePtr</CODE> of type integer <CODE>StringLength</CODE> is ignored. <H4>Returns</H4> <P><CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE>.</P> <HR align="center" width="20%"> </TD> </TR> <TR> <TD colspan="2"> </TD> <TD class="head"><A NAME="envattr">SQLSetEnvAttr</A></TD> </TR> <TR> <TD colspan="2"> </TD> <TD > sets attributes of environments. <CODE><PRE> SQLRETURN SQLSetEnvAttr(SQLHENV EnvironmentHandle, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength); </PRE></CODE> <H4>Arguments</H4> <H5>EnvironmentHandle</H5> <CODE>EnvironmentHandle</CODE> must have been allocated by <CODE>SQLAllocHandle</CODE> <H5>Attribute</H5> which attribute to set <H5>ValuePtr</H5> Pointer to the value for <CODE>Attribute</CODE>. Depending on <CODE>Attribute</CODE>, <CODE>ValuePtr</CODE> will be a 32-bit integer value or a pointer to a null-terminated string. <H5>StringLength</H5> If <CODE>ValuePtr</CODE> points to a character string or a binary buffer, this argument should be the length of <CODE>*ValuePtr</CODE>. Otherwise, for <CODE>ValuePtr</CODE> of type integer <CODE>StringLength</CODE> is ignored. <H4>Returns</H4> <CODE>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR</CODE> or <CODE>SQL_INVALID_HANDLE</CODE>. <HR width="20%" align="center"> </TD> </TR> </TABLE> </BODY> </HTML> intro.html 0000644 00000004506 15127642425 0006602 0 ustar 00 <HTML> <HEAD> <LINK REL="stylesheet" href="odbc.css"> <TITLE>Introduction</TITLE> </HEAD> <BODY bgcolor="white"> <table width="90%" cols="3" border="0"> <TR> <TD colspan="3" class="big">Introduction</TD> </TR> <TR> <TD colspan="3">Welcome to a short tutorial on ODBC programming. The goal of this tutorial is to introduce a C-Programmer to ODBC programming. During this tutorial we will code a simple program which connects to a database via ODBC and reads some data. There won't be any information on how to program ODBC Drivers or about <EM>unixODBC</EM> internals. Configuration won't be covered too. The program developed throughout this tutorial was originally coded under WinNT and later ported without any adjustments to Linux and <EM>unixODBC</EM>. This is how compatibility should work :) <P> The information given within this tutorial are brief at best. Please take it as a pointer where and how to start. </P><BR> </TD> </TR> </TABLE> <TABLE COLS="3" Width="90%" border="0"> <TR><TD class="head" width="25%">Requirements</TD> <TD width="2%"> </TD> <TD width="73%">I assume that you have</TD> </TR> <TR> <TD colspan="2"> </TD> <TD><UL> <LI>a system with unixODBC installed and with at least one working datasource configured. <LI>the include files installed under <CODE>/usr/include/odbc</CODE> <LI>a compiler installed and that you know how to use it ;) </UL> </TD> </TR> <TR><TD class="head" width="25%">Compiling</TD> <TD width="2%"> </TD> <TD>If gcc is installed type:</TD> </TR> <TR> <TD colspan="2"> </TD> <TD><CODE>gcc odbc.c -o odbc -lodbc</CODE><BR> which will result in an executable named "odbc". </TD> </TR> <TR><TD class="head"><A nAME="tab">Database</A></TD> <TD width="20"> </TD> <TD>Our database will have a single table:</TD> </TR> <TR> <TD colspan="2"> </TD> <TD><TABLE COLS=2 Border="0" bgcolor="#efefef"> <TR> <TH colspan=2 class="head">tkeyuser</TH> </TR> <TR> <TD>iduser</TD> <TD>sequence</TD> </TR> <TR> <TD>dtname</TD> <TD>char(40)</TD> </TR> <TR> <TD>dtmaxSize</TD> <TD>Integer</TD> </TR> </TABLE> <BR> Our datasource will be named "web" and access is granted to the user "christa" with no password. </TD> </TR> </table> </BODY> </HTML>
| ver. 1.6 |
Github
|
.
| PHP 8.2.30 | ??????????? ?????????: 0 |
proxy
|
phpinfo
|
???????????