Tags:
database2Add my vote for this tag import1Add my vote for this tag twiki_application1Add my vote for this tag web_application1Add my vote for this tag create new tag
, view all tags

DBIQueryPluginDev Discussion: Page for developer collaboration, enhancement requests, patches and improved versions on DBIQueryPlugin contributed by the TWikiCommunity.
• Please let us know what you think of this extension.
• For support, check the existing questions, or ask a new support question in the Support web!
• Please report bugs below

Feedback on DBIQueryPlugin

-- VadimBelman - 13 Oct 2005

I'm pretty sure that documentation is ugly. Would someone help me with it?

-- VadimBelman - 13 Oct 2005

Interesting plugin, but how is it different to the DatabasePlugin (that I never could manage to run properly by the way wink )?

-- FranzJosefSilli - 13 Oct 2005

Just read the documentation and you'll see. smile I was inspired by the idea but needed more flexibility. Yet, I read DatabasePlugin code - and was terrified.

-- VadimBelman - 13 Oct 2005

For me DatabasePlugin works quite well - after some hours of fiddling with the configuration. What is so terrifying? Your fine DBIQueryPlugin works as well. But you are right. The documentation is not ugly, but I could not yet figure out, how formatting control is managed by "output formatting filters" and what you mean with "Afterwards we let TWiki to mangle with the output". For instance: With

%DBI_QUERY{db1}% SELECT datum, az FROM bgh WHERE datum > '2005-07-01' .head humbdi .body %datum% %az% .footer dumbdi %DBI_QUERY%

I get the 5 matching rows formatted like

humdi2005-07-07 III ZR 422/04 2005-07-06 XII ZB 50/03 2005-07-06 XII ZR 293/02 2005-07-06 XII ZB 226/01 2005-07-06dumdi

What shall I do with this output? Perhaps a complete application example would make things clearer.

-- MichaelWagnerBerlin - 17 Oct 2005

Notice this part of the documentation:

Every newline within .head, .body, and .footer gets changed with space except for the last ones. They're removed. Whereas newline is needed \n escape sequence must be used. Consequently, \\n is translated into \n.

So, your example would look like this:

%DBI_QUERY{db1}% SELECT datum, az FROM bgh WHERE datum > '2005-07-01' .head humbdi\n .body %datum% %az%\n .footer dumbdi\n %DBI_QUERY%

-- VadimBelman - 17 Oct 2005

I added thrice the missing \n, but output remains the same. I use TWiki version 02 Sep 2004 $Rev: 1742 $, Plugin API version 1.025 on Debian. And it is true, I did not and do not understand the sentence Every newline ... gets changed with space. What means space? Thank you for your response.

-- MichaelWagnerBerlin - 17 Oct 2005

"Space" means "space character".

The escape sequence has to replaced with <br>. I use a regexp, which is pretty simple and must be working properly with any version of Perl since 5.0. I.e.:

.body %datum% %az%\n 

shall be translated into:

%datum% %az%<br> 

Would you email me with the query text, please?

-- VadimBelman - 17 Oct 2005

I am stupid... Well, headache doesn't help thinking anyway. smile

The point here is that \n inserts a newline character, not <br>. So, inserting the latter does the job.

\n is mostly used for proper building of tables.

-- VadimBelman - 17 Oct 2005

Thanks Vadim for sharing this Plugin with the TWikiCommunity! This Plugin is a perfect fit to the TWikiMission.

I made some changes to the Plugin topic. Please feel free to carry that over into the next release.

How about measuring and documenting the PluginBenchmarks?

Small note on XHTML: Better to use <br /> instead of <br>. Better yet, use the TWiki variable %BR%.

-- PeterThoeny - 02 Nov 2005

Thanks Peter for your comments, changes and support!

I have finally managed to get some spare time and finished developing the next version of my plugin. It could be now considered as a fully functional tool for developing database based applications within TWiki framework. In my case I managed to get rid of MS Access. Now this functionality is an organic part of intraweb.

Hopefuly, it will help others as well.

-- VadimBelman - 20 Feb 2006

I made a minor change on TOC.

-- PeterThoeny - 20 Feb 2006

Ok, accepted and updated the distribution archive.

-- VadimBelman - 21 Feb 2006

Vadim, thanks for updating the Plugin for for Dakar. Suggestion: Try to upgrading it so that it runs on Cairo and Dakar codebase. HandlingCairoDakarPluginDifferences has the details. If you prefer to drop support for Cairo, overwrite the DBIQueryPlugin.zip file with the Dakar version (better not to have two zips.)

I added you to the TWikiCommunityGroup so that you can remove attachments. Please review the notes on that group topic.

-- PeterThoeny - 04 Mar 2006

Dear All, Can You tell me is this plug-in works on Windows 2000?

  • I get error:
DBI connect('database=test','RomanMishchenko',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at /twiki/lib/TWiki/Plugins/DBIQueryPlugin.pm line 248
  • Is it possible to get comments from stored procedures using this plug-in?
  • Do You plan to support other databases?

This question not for this topic, but

  • Is there plug-in to get comments from source code?

-- RomanMishchenko - 07 Mar 2006

The PerlDocPlugin extracts Perl pod comments and can show it in a TWiki page.

-- PeterThoeny - 07 Mar 2006

Hi Vadim, nice plugin.

I have one problem though. No support for the SQLite database. To add support the configuration parsing needs to be amended.

  • database to become optional
  • dbname to be added
This is because the DSN for SQLite looks like "dbi:SQLite:dbname=dbfile"

-- PatrickDiamond - 08 Mar 2006

To PeterThoeny

The second version shall be working on both Cairo and Dakar. Unfortunately, I am not able to test on Cairo presently.

I'd take an action on this if anyone could test on Cairo and report to me.

BTW, is it possible to get notification not for a whole web but for a single topic? I visit this topic once in a two-four weeks and therefore react with rather big latency.


To RomanMishchenko

Unfortunately, I cannot help you with Win2000 problem. But my advise would to read DBI documentation carefuly. The problem you report is not related to the plugin but rather has something to do with DBD::mysql install and configuration.

Is it possible to get comments from stored procedures using this plug-in?

I didn't work with stored procedures for a long time now. Lot's of details are gone from my memory. So, if you show me what is it and how you do it normally, I'd could figure out a way to help you.

Do You plan to support other databases?

Other databases are supported by defining driver key in the configuration file. The only issue with support for SQL servers other than MySQL is locale support. The codepage key doesn't work for them. PostgreSQL was simply forgotten; as for the rest I just don't know how to do it.


To PatrickDiamond

May I ask you to contact me on email? Again, it is not a big deal to implement your request. But I would ask you to do testing for me.

-- VadimBelman - 11 Mar 2006

You can create a custom RSS feed based on a search, see WebRssBase. Fine-grained e-mail notification will be available once twiki.org is upgraded to Dakar.

-- PeterThoeny - 11 Mar 2006

Hi Vadim,

I have a consern/suggestion about the security of %DBI_DO%. The current default policy is to allow members of TWikiAdminGroup to run %DBI_DO% scripts stored in any topic. I think that is a security risk. It allows a malicious user to edit or create a topic with

%DBI_DO{"db"}%
doSomethingHarmful();
%DBI_DO%
and the next time a member of the TWikiAdminGroup happens to view the topic, the code is run.

In general, I think that any topic with DBI_DO scripts (and any INCLUDEd topics), must have ALLOWTOPICCHANGE and ALLOWTOPICRENAME set to only the users allowed to run the DBI_DO scripts or other trusted users.

Thus, my suggestion is to change the default policy of the DBIQueryPlugin to allow no-one to run DBI_DO scripts in any topic. Also, I suggest adding to the plugin documentation that topics with scripts should only be writable by trusted users.

-- BjornHelgeMevik - 22 Mar 2006

I have a consern/suggestion about the security of %DBI_DO%.

Good point. One more thing into my todo list.

OTOH, the plugin can intercept page editing and do some checks. Further behaviour may vary depending on policies choosen. For instance, saving may be disabled and oops displayed if any of %DBI_DO% has been changed or added.

I'll try to find out which way is better.

-- VadimBelman - 23 Mar 2006

The plugin works fine on TWiki Release 4.0.1, Windows 2000, cygwin.

Is it possible to get comments from stored procedures using this plug-in?

In mysql I can use command "SHOW CREATE PROCEDURE name_of_procedure" to see all text of stored procedure. Output will be one row with three columns.
In MSSQL I can use command "sp_helptext name_of_procedure" to see all text of stored procedure. Output will be several rows and one column

For mysql DBI_QUERY is

%DBI_QUERY{"test"}% SHOW CREATE PROCEDURE test .header PROCEDURE test .body %create procedure% .footer test %DBI_QUERY% 

Is it possible to include into output variable %create procedure% according to some rules.
For example to use something like this

%DBI_QUERY{"test"}% SHOW CREATE PROCEDURE test .header PROCEDURE test .body %INCLUDE{%create procedure%, pattern="...", ... }% .footer test %DBI_QUERY% 

-- RomanMishchenko - 31 Mar 2006

Vadim, DBIQueryPlugin-dakar.zip has a comment that it runs on Cairo as well. In this case I suggest to remove the DBIQueryPlugin-dakar.zip attachment, and to attach the latest version to DBIQueryPlugin.zip.

-- PeterThoeny - 18 May 2006

After installing the plugin, I pulled the email column from one of my MySQL tables, but twiki doesn't automatically recognize the emails displayed on the twiki page as links, like it normally does. Do you know a way around this?

Thanks.

-- JulietHabberstandish - 18 May 2006

Please ignore my previous email. I wound up formatting the email address links the same way one would in HTML:

< a href="mailto:%email%">%email%

-- JulietHabberstandish - 18 May 2006

Sorry if this is a silly question, but once I SELECT data using %DBI_QUERY% and display it, is there any way to manipulate the data. For instance, can I use a "if else" statement anywhere in the %DBI_QUERY% section, so that only certain data is displayed?

Thanks for your help.

-- JulietHabberstandish - 30 May 2006

I am getting the error... DBI connect('database=viewpoint','sa',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1)

My question then being, does this plugin work with ODBC? It is ms sql 2000 and I am pretty sure I have my dbiquery.conf right (user, pass, driver, database). I would be greatful of an email from anyone with a similar setup.

-- JeffPelton - 31 May 2006

I had to hack out the 'database=' in the DBI connect. Now it works fine. =)

-- JeffPelton - 01 Jun 2006

Sorry for not replying, but time is always against me.

I have just uploaded a new version which tries to fix some of the problems mentioned here. Have a look at Changelog in the documentation (NB: have not updated it on twiki.org, so get it from the ZIP).

Will try to get some time for replies on this Monday.

-- VadimBelman - 03 Jun 2006

To RomanMishchenko

Is it possible to include into output variable %Mcreate procedure% according to some rules.

Have a look at the new version. It now translates spaces into underscores for column names. So, you could refer to %create_procedure% now.

To PeterThoeny

In this case I suggest to remove the DBIQueryPlugin-dakar.zip

Done.

To JulietHabberstandish

For instance, can I use a "if else" statement anywhere in the %DBI_QUERY% section, so that only certain data is displayed?

There is no need for having such kind of control within %DBI_QUERY% because one can use SQL statements for filtering out data.

To JeffPelton

I had to hack out the 'database=' in the DBI connect. Now it works fine. =)

There is better solution for you problem in v1.2. Use dsn key in DBIQueryPlugin.cfg file.

-- VadimBelman - 05 Jun 2006

Thanks for your response Vadim. I guess I should've been more clear. I need to display an asterisk near the names of the employees who have a boolean column set to "1" for true. Somehow, I need to convert the "1" value to "*". I'm used to using scripts such as PHP to pull data from MySQL and then manipulate the data via scripting. I tried using perl scripts between the %DBI_DO% section, but it wasn't working. I would greatly, greatly appreciate your help. Thank you again.

-- JulietHabberstandish - 05 Jun 2006

To JulietHabberstandish

Somehow, I need to convert the "1" value to "*".

Still, it is possible to do it within SQL query. For instance, it could be done in the following way:

SELECT     first_name,     last_name,     IF (bool_field, '*', '') as mark_sign   FROM Employees 

Actually, at the design stage of development I was thinking about having some kind of filters within %DBI_QUERY%. But due to complexity and flexibility of modern SQL language it seemed to be unnecessary.

Yet, with regard to %DBI_DO% usage. What was wrong about it?

-- VadimBelman - 06 Jun 2006

Is it possible to get any output other than just fields?

It seems the .body only accepts %column_name%.

I want to do a SELECT COUNT(*) FROM table statement but I don't know what variable represents that output.

%DBI_QUERY{"test"}% SELECT COUNT(ID) FROM accesslog .body %output% %DBI_QUERY%

-- ScottRogers - 07 Jun 2006

Hi Vadim,

Thanks for your help. I really appreciate it. The SQL commands are enabling me to manipulate the data in the way that I wanted to using Perl.

In regards to the %DBI_DO%, I tried to use %DBI_DO% to just carry out regular PERL DBI code as a test, like this:

%DBI_DO{"db1"}%
            
my $DataBaseName    =   "database_name";
my $DataBaseHost    =   "localhost";
my $DataBaseUser    =   "root";
my $DataBasePass    =   "somepassword";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",

                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { !RaiseError => 1,
                                       !AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";
                                  
my $StatementHandle = $DataHandle->prepare("SELECT * FROM employees");
$StatementHandle->execute();

my $RecordData = $StatementHandle->fetchrow_hashref();

print "NAME:  $RecordData->{'name'}\n";

$StatementHandle->finish;

%DBI_DO%

and it didn't do anything. I guess I was unclear as to what type of Perl I commands I should use in between these tags (perl DBI as opposed to regular perl, etc.). I'm not really that familiar with perl to start with, so I'm sure that's not helping.

Thanks again for your help.

-- JulietHabberstandish - 07 Jun 2006

I may be of some help Juliet because I went through that today also.

First off, if you're using %DBI_DO% to access a database that's already identified in the config file, you don't need all the extra connection stuff. Only use it if you need to connect to a different database/host (you may already know this).

If you are using a database that already has an identifier (like db1 that you have in your parameters), you can just use the $dbh handler.

But I'm guessing your main problem is that the print statement isn't doing anything. I found out today that print just doesn't seem to work. If you want the data that you queried displayed in TWiki, use the $rc variable.

Whatever $rc contains will be displayed in the TWiki topic.

-- ScottRogers - 07 Jun 2006

To ScottRogers

I want to do a SELECT COUNT(*) FROM table statement but I don't know what variable represents that output.

Use AS keyword. Like:

SELECT COUNT(ID) as count_id 

and then refer to %count_id%.

To JulietHabberstandish

I was unclear as to what type of Perl I commands I should use in between these tags (perl DBI as opposed to regular perl, etc.). I'm not really that familiar with perl to start with, so I'm sure that's not helping.

Thanx to Scott, he is absolutely correct in hist note.

-- VadimBelman - 08 Jun 2006

I've got something interesting happening with my DBI_QUERY calls. I've noticed considerable slowness in the time it takes to make a few queries so I started looking at my SQL Profiler (MS SQL 2000) and watching the RPC:Completed. What I am seeing is an intense amount of duplicate calls. More specifically, calls from previously visited topics.

I can see what is happening now, but no idea why. The first clue was seeing the Deep Recurrsion Over 100 errors in my log.

What I did to test was restart my apache, clear my log and run my SQL Profiler. I have a topic with a simple query, SELECT 100 FROM "TheDB"."dbo"."TableName". I load the topic. Finally! Only 1 RPC:Completed. The call which looks like...

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, NULL, N'
SELECT 100 FROM "TheDB"."dbo"."TableName"'
select @P1
So I load the page a second time and get 2 RPC:Completed. One the same as above, the second as...
declare @P1 int
set @P1=2
exec sp_prepexec @P1 output, NULL, N'
SELECT 100 FROM "TheDB"."dbo"."TableName"'
select @P1
Each time I load the page it does all the previous RPC's and then 1 more.

Anyone have any clue what might be happening, or what I am doing wrong with my setup?

-Much appreaciated.

-- JeffPelton - 14 Jun 2006

Whew, figure it out!

mod_perl keeps global vars throughout the same process (each topic view included). So the counter for the QUERY array was not reseting to 0. Then when handleQueries ran, it would execute a whole lot of previous queries lines up in the %queries{$quid} . I am no perl expert, so please advise a better fix if there is one, but I moved $query_id = 0; into initPlugin and added undef %queries; in the same place. Now it works =)

-- JeffPelton - 23 Jun 2006

mod_perl was not a normal way of running TWiki. Neither it is now, AFAIK. Or am I wrong?

Whatever, the plugin was designed keeping this fact in mind and there could some more problmes...

With regard to your solution, undef %queries must be sufficient. $query_id could be left intact as it needed for nothing but unique query identification.

-- VadimBelman - 26 Jun 2006

Wow! Thanks Vadim. This is a great plug-in. Installed and pulling test data into a page in less than an hour, including building the sql database and table to test with. Nice.

-- PaulHenryDavis - 30 Jun 2006

Question, I would like to build a table from the output, and try:

%DBI_QUERY{test1}% 
SELECT company, price FROM stocks;
.body
| %company% | $%price% | %BR%
%DBI_QUERY%

but get an answer that is the data surrounded by |'s instead of a TWiki formatted table. Do I have to use

<tr>
etc.? -- PaulHenryDavis - 30 Jun 2006

Use \n sequence instead of %BR%.

-- VadimBelman - 30 Jun 2006

Again, thanks. NICE PLUG-IN!

-- PaulHenryDavis - 05 Jul 2006

I'm still trying to understand how I should use DBI_DO and DBI_CODE. I try to call

%DBI_DO{"my_db" topic="MyCodeCollection" script="test"}%
But I only get the error message:
No code defined for this %DBI_DO% variable
The Topic MyCodeCollection and DBI_CODE within do exist though. Is there somewhre some example how the whole magic shall work? How should the DBI_DO line look like? How the DBI_CODE? How do I transfer my parameters from the DBI_DO line into DBI_CODE script? Can I get more outputs/global variables modified within DBI_CODE?

-- AndyThiel - 15 Nov 2006

Could you, please, contact me on email? I'd like to see topics' source code before saying something useful.

And excuse me if my replies are to be delayed - I'm overloaded with work.

-- VadimBelman - 16 Nov 2006

Many thanks for a very useful Plugin. I had it installed and running with very little effort.

One little question: What is the best way to use twiki colours on fields from the database?

For example, in one of my MySQL tables I have a column called 'AssignedTo'. If this column is NULL I want to display the string '(nobody)' in RED.

I tried the expression:

IFNULL(AssignedTo, "%RED%(nobody)%ENDCOLOR%") as AssignedWho
but all I got displayed was
<font color="ff0000">(nobody)</font>
I tried using the unquoted parameter with both "AssignedTo" and "AssignedWho", but neither made any difference.

Any ideas?

-- DuncanKinnear - 07 Dec 2006

Also, is there anything special that I need to do to use these in templates?

I tried creating a template with the content section as follows:


---++ Details of Software Request %PARAM1%
%DBI_QUERY{"my_mccarthy"}%
SELECT
    IDNumber, CustomerID, CustomerPriority
  FROM
    SoftwareRequests
  WHERE
    IDNumber = '%PARAM1%'
.header
---+++ This is the Header\n
%BR%
.body
|  *Customer:* | %customerid% |\n
|  *Customer Priority:* | %customerpriority% |\n

.footer
\n---+++ This is the Footer

%DBI_QUERY%

[[%TOPIC%][Back to List of IDs]]

I use this template by calling the oops script with it and a parameter like this:

bin/oops/Developemnt/TestTopic?template=MyTemplate&param1=12712

The resulting page that is displayed has the "Details of Software Request 12712" corrrectly, and it also has the "This is the Header" and "This is the Footer" lines, but nothing in between.

When I deliberately change the WHERE clause so that it is not syntactically correct by adding an extra quote at the end, then DBIQuery produces a nice database error which shows the SELECT query with the WHERE clause equal to "IDNumber = '12712''".

So I'm not sure why DBIQuery isn't getting the record.

If I 'hard-wire' the WHERE clause to "IDNumber = '12712'", the page loads the query data fine.

Any ideas?

-- DuncanKinnear - 08 Dec 2006

Concerning your first problem - try putting DBIQueryPlugin first in PluginsOrder. Though I would prefer using %CALC{}% from SpreadSheetPlugin. It's very flexible and useful tool which helps to workaround lots of clashes between the plugin and the rest of TWiki environment.

The next problem is arising from usage of %PARAM1%, as of the first glance. There must be used %URLPARAM{"param1"}% instead.

-- VadimBelman - 08 Dec 2006

Excellent! Your answer to my second (and more urgent) problem has fixed it. Thank you very much.

Can you explain why this fixes the problem? What is the difference with using the URLPARAM syntax?

As for my first (and more trivial) problem, changing the PluginsOrder has not helped. I will try the CALC method next week and report back then.

Thanks again for your help.

-- DuncanKinnear - 08 Dec 2006

Sorry, but explanation on the difference would require some documentation digging of me which I have no much spare time for. Try asking around or read it yourself.

So far, what I remember of it - parameters are just not passed as variables into the TWiki environment. Try thinking of it as a way of illegal overriding of some system parameters and the conclusion would become obvious.

-- VadimBelman - 13 Dec 2006

I manage to tweak the code to support MS-SQL through unixODBC driver. But i have a question.

How do i pass the result from one query to DBI_DO environment. For instance, %abc% result retrieved from a sql query, i would like to pass on to DBI_DO area to further massage the result with perl code.

Please help. Cheers, Don

-- DonChai - 06 Feb 2007

You have probably missed mention of %DBI_SUBQUERY% statement. One can use it for referring to both %DBI_QUERY% and %DBI_DO%.

BTW, DBI_SUBQUERY has alias DBI_EXEC for readability.

Next, if %DBI_DO% subroutine is called from within a %DBI_QUERY% then $dbRecord variable is not empty and it's a hash of last fetched database record.

It must be what you're looking for.

-- VadimBelman - 07 Feb 2007

How do you pass variables from a from POST into a DBI_DO ? I can do this with DBI_QUERY by just inserting something along the lines of %URLPARAM{"Rating"}% to get the variable named Rating for example.

I have tried to make sense of the bit about %httpParams and multivalued="Param1 Param2" but cannot get to the bottom of it. Apologies if the answer is obvious, I am new to perl and DBI... wink

-- EdMcDonagh - 19 Mar 2007

Surely, one cannot use Twiki variables within perl code directly.This is why you have no success with %URLPARAM%.

%httpParams is a perl hash. If you have a field named some_field in your HTML form you can refer it as $httpParams{some_field} in your code. Read perldata and perldsc sections of Perl documentation.

multivalued means that corresponding parameters ( Param1, Param2) may contain more than one value (as for checkboxes in HTML forms, for example). Since there is no way to distinguish them from common parameters in case they hold one value only, this option has been introduced. The sole purpose of it is to simplify Perl code as any parameter, specified with this option, will be presented as an array reference.

-- VadimBelman - 20 Mar 2007

Hi, Great Plugin. I'm attempting to use the plugin with DBD::Oracle . I know that i have DBD::Oracle is working as a have written a few perl scripts that can pull data from our Oracle Server. When running these queries using the plugin i recieve an error . It's the same error that you get if you dont have the LD_LIBRARY_PATH env variable set . Anyone know how you would go about setting this variable within apache/twiki ?

-- IanClancy - 26 Apr 2007

Try using SetEnv parameter within the section where your TWiki is configured. In my case it's <VirtualHost>. It may be Directory or Location as well.

Eventually, the configuration may look like the following example:

<VirtualHost *>     [... other parameters ...]     SetEnv ENV_VARIABLE value </VirtualHost> 

-- VadimBelman - 27 Apr 2007

Thanks Vadim, I eventually ended up just adding the oracle librarys to the ld.so.conf file and linking them. This seems to work so i'm happy to report that the plugin is working with Oracle smile

-- IanClancy - 01 May 2007

Thanx! smile

-- VadimBelman - 03 May 2007

Is there a simple example of the CFG file and a sample select? I tried using some of the examples above but the page just shows the code rather that is suppost to be produced by the code. Here is what I have:


DBIQueryPlugin.cfg
%dbi_connections = (
    connection1 => {
        usermap => {
            TWikiAdminGroup => {
                user => 'guest',
                password => 'password',
            },
            SpecialGroup => {
                user => 'guest',
                password => 'password',
            },
        },
        user => 'guest',
        password => 'password',
        driver => 'mysql',
        database => 'ues',
        codepage => 'koi8r',
        host => 'ktazd35.crdc.kp.org',
    }
);


DBIQueryPluginTest
%DBI_QUERY{connection1}% 
select host_name, system_model from server_detail
.body
| %host_name% | %system_model% | \n
%DBI_QUERY%

-- DonavonLerman - 22 Aug 2007

Is plugin enabled in your TWiki configuration? The code is shown when the plugin is not being run.

PS. Sorry for late reply - I was on vacation.

-- VadimBelman - 09 Sep 2007

I've patched version 1.2 so that the header is only displayed when there is at least one row, so my pages don't get filled with headers with no content.

The patch is online at http://dparrish.com/code/DBIQueryPlugin-conditionalheader.patch

From your TWiki root directory, you can run:

patch -p1 < DBIQueryPlugin-conditionalheader.patch

Remeber to restart Apache if you are using mod_perl.

-- DavidParrish - 20 Sep 2007

Thanks for the Plugin, nice work. I'm trying to pass a parameter to use in a WHERE clause. I can't seem to get the syntax correct in the call. My DBI_DO is: %DBI_DO{"aceconn" topic="ProcessEngineering.ProcWatch" script="pwtest" lotno="7H2199")}%

In the topic for the DBI_CODE, the SQL works if hard coded as:

$sqlStatement = "SELECT product_name FROM lot WHERE lotno LIKE '7H2199'"; But.., I'd like to pass in the lotno condition.

I try to access $varParams{lotno} in the topic and just return that value in $rc, but I get an error saying that $varParams is not defined. Thanks.

-- NealVerzwyvelt - 12 Oct 2007

Hi, I'm getting the following error when retrieving a field of type LONG:

DBD::Oracle::st fetchrow_hashref failed: ORA-24345: A Truncation or null fetch error occurred (DBD ERROR: ORA-01406 error on field 16 of 22, ora_type 8, LongReadLen too small and/or LongTruncOk not set)

Where should I be defining values for the variables, LongReadLen and LongTruncOk ?

Thanks

-- TonyWong - 18 Oct 2007

I would like to connect to the Bugzilla database (MySQL) using the DBquery plugin. how do i build the connection string for this?

-- DeepakKS - 16 Jan 2008

I am trying to bring DBIQuery plugin working on TWiki 4.2.0. It seem to me that some methods inside the TWiki have been changed or removed. How to get this plugin working under latest TWiki 4.2.0 version?

-- KirillMessel - 07 Feb 2008

I would like to apologize for not answering questions here. Unfortunately, I'm not able to provide this plugin with full-level support anymore. So, I'd like to propose some one to take over the development. Any volunteers?

-- VadimBelman - 11 Mar 2008

Kirill,

I have looked through the Release Notes for version 4.2 and there does not seem to be anything in the changes to the standard TWiki functions that could affect this Plugin. Of course, that does not exclude the possibility that the Plugin is using non-standard API function calls.

What is not working in version 4.2? What symptoms are you experiencing with DBIQueryPlugin in this version?

We have a 4.2 test server, and I've just tried DBIQueryPlugin on it. Works fine here. Was it working for you in a previous version?

-- DuncanKinnear - 12 Mar 2008

Dunkann,

As previous version I had twiki 4.0.0 and DBIQueryPlugin it seems to work fine. I have develop a few twiki pages with the SQL statement.Twiki has been upgraded to 4.2.0 on the new server with new Apache version and Perl version as well. All data have been copied to new instance. Now all twiki pages which have any SQL statement seems to be broken. Here is the output Can't locate object method "findUser" via package "TWiki::Users" at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 201. at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 201 TWiki::Plugins::DBIQueryPlugin::userIsInGroup('Main.KirillMessel', 'EverybodyGroup') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 231 TWiki::Plugins::DBIQueryPlugin::db_connect('Remedy_LDN') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 647 TWiki::Plugins::DBIQueryPlugin::getQueryResult('DBI_CONTENT1', 'HASH(0x8f27eec)') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 598 TWiki::Plugins::DBIQueryPlugin::executeQueryByType('DBI_CONTENT1') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 761 TWiki::Plugins::DBIQueryPlugin::__ANON__() called at /export/apps/twiki2/bin/perl/lib/site_perl/5.8.8/Error.pm line 415 eval {...} called at /export/apps/twiki2/bin/perl/lib/site_perl/5.8.8/Error.pm line 407 Error::subs::try('CODE(0x8f27bc8)', 'HASH(0x8f2818c)') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 787 TWiki::Plugins::DBIQueryPlugin::handleQueries() called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 820 TWiki::Plugins::DBIQueryPlugin::processPage('\x{a}\x{a} \x{a}\x{a} \x{a}\x{a} \x{a}\x{a} \x{a}\x{a} \x{a}\x{a} \x{a}\x{a} *

-- KirillMessel - 17 Mar 2008

Old Twiki instance is running on Linux, but new one on Solaris for x86

-- KirillMessel - 17 Mar 2008

Actually, it's 'Duncan' not 'Dunkann'.

What version of the Plugin have you got installed on each system? The version that works on our 4.2 TWiki is the one that was last changed on 3 Jun 2006 (in other words, the latest version at TWiki.org).

Is the new server running with 'mod_perl'? Vadim stated above that this Plugin is not designed to work with mod_perl.

Did anything else change with the move to the new server? Perl version? Database? Web server?

Are all other aspects of your 4.2 installation working correctly?

-- DuncanKinnear - 18 Mar 2008

Oops! I just realised that I was still pointing at our 4.1.2 server when I did the test, so I cannot say whether it works on 4.2 or not.

I quick glance at the 4.2 'Users.pm' file in the lib/TWiki subdirectory shows that the error is correct, in that there is no longer a 'findUser' method on the 'Users' object. There are a couple of other method that will probably do the trick.

If you change line 201 in the DBIQueryPlugin.pm file to use the find by wiki name method, then that might cure it.

Hopefully that's the only API method that has changed.

-- DuncanKinnear - 18 Mar 2008

Duncan,

Sorry for the typo in your name.

I have just changed the version of Apache to 1.3.41, Perl version to 5.8.8. All other aspects of my installation work fine. The latest version of plugin installed. I have tried to change line 201 to use findUserByTwikiName it does not help. It seems to me that more than one API method has changed

See the output Can't call method "isInList" on unblessed reference at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 204. at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 204 TWiki::Plugins::DBIQueryPlugin::userIsInGroup('Main.TWikiGuest', 'EverybodyGroup') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 232 TWiki::Plugins::DBIQueryPlugin::db_connect('Remedy_LDN') called at /export/apps/twiki2/apache/twiki4.2.0/lib/TWiki/Plugins/DBIQueryPlugin.pm line 648 TWiki::Plugins::DBIQueryPlu

-- KirillMessel - 18 Mar 2008

OK, I've done a bit more analysis of the DBIQueryPlugin source code, and the only problem seems to be with the userIsInGroup sub-routine for testing if a specified user is in a specified group. Here's the code snippet:

my $userObj = $TWiki::Plugins::SESSION->{users}->findUser($user);
return 0 unless defined $userObj;
return $userObj->isInList($group);

Where $user and $group are passed into the sub-routine.

I've asked a support question about this here and hopefully someone can tell us how to do this with standard API calls.

In the meantime, it is only triggering this sub-routine because you have a usermap defined in your database connections definition file, so if you can remove that then the Plugin should continue to work.

-- DuncanKinnear - 18 Mar 2008

I'm a TWiki newbee, but I patched mine with this code, and it works now. I don't know if the first search is really necessary or not, and I haven't validated the security at all.

--- lib/TWiki/Plugins/DBIQueryPlugin.pm 2006-06-03 08:56:03.000000000 -0500
+++ ../lib/TWiki/Plugins/DBIQueryPlugin.pm      2008-04-15 08:44:07.000000000 -0500
@@ -198,9 +198,9 @@
     if ($TWiki::Plugins::VERSION < 1.1) {
        return TWiki::Access::userIsInGroup($user, $group);
     } else {
-       my $userObj = $TWiki::Plugins::SESSION->{users}->findUser($user);
-       return 0 unless defined $userObj;
-       return $userObj->isInList($group);
+       my $loginName = TWiki::Func::wikiToUserName($user);
+       return 0 unless defined $loginName;
+       return TWiki::Func::isGroupMember($group, $loginName);
     }
 }
 # }}}

-- LukeSuchocki - 15 Apr 2008

Luke,

Thank you for your patch.

When I first read the code I wondered how on earth I had missed the isGroupMember function from TWikiFuncDotPm. I went back to the list of functions in that topic and, sure enough, there it was.

But then I noticed when the list of functions was last updated: 5 Apr 2008.

The previous version of the TWikiFuncDotPm topic (dated 1 Jan 2008) did not mention the function (and many others in fact).

So, yes, your code looks like the best way to achieve what was being done before.

Now the big issue is that this new code will break the Plugin in TWiki versions 4.0.x and 4.1.x, so we best wait a bit until we include the patch in the distribution.

Alternatively we could include the code with another test of the $TWiki::Plugins::VERSION.

Thanks again.

-- DuncanKinnear - 16 Apr 2008

I recommend to use conditional code that tests on Plugin API version. That way your plugin works on older TWiki releases as well. Here is a code snippet taken from the CalendarPlugin that gives you an idea:

    if( $TWiki::Plugins::VERSION >= 1.010 ) {
        $text = &TWiki::Func::readTopicText( $theWeb, $theTopic, '', 1 );
    } else {
        $text = &TWiki::Func::readTopic( $theWeb, $theTopic );
    }

Each TWikiFuncDotPm function lists the API version it was introduced.

-- PeterThoeny - 19 Apr 2008

Luke,

Thanks a lot for your patch. I have tried to patch my DBIQueryPlugin.pm file. It looks like that for me it still does not work. For some reason I got the following error in apache error.log file: Premature end of script headers: /../../twiki/bin/view

-- KirillMessel - 05 May 2008

I just installed the database plugin and dbiquery plugin on my twiki server. I'm trying to connect to another sever running mysql. I can run queries fine from the mysql server, but when I use twiki, I get no results back. Plugin diagnostics show no errors. I think I have my configuration set correctly. I'm new to this and looking for a little help to get started.

-- JenniferWoss - 09 May 2008

Have you looked at your webserver logs? How about the logs in the TWiki data subdirectory (warn*.txt and log*.txt)

Try switching plugin debug on and looking in the debug*.txt file as well.

Do you have a database on the TWiki server that you can test the plugins with? Just to make sure that the SQL you are using is correct.

-- DuncanKinnear - 11 May 2008

Duncan! I have tried to enable debug for plugin with Set DEBUG = 1, but it seems to me that it does not work. I do not see anything related to DBIQueryPlugin. There is only information about Ploticus plugin in log*.txt.

-- KirillMessel - 12 May 2008

Duncan, I don't see anything at all in my httpd logs. I searched through and did a grep through the warn and log files for Twiki. Only saw errors relating to AccessStatsPlugin, which I don't think we use. Maybe the query is wrong. I tried a DBI_QUERY and got "This DBI connection is not defined" and I tried a DATABASE_SQL query: %DATABASE_SQL{description="MonthlyReport",sql="SELECT * FROM Status"}%

My DBI*.config looks like this: %dbi_connections = ( MonthlyReport=>{ usermap => TWikiAdminGroup=>{ user = > 'twiki'. password => 'twiki', }; user => 'twiki', password => 'twiki', driver => 'mysql', database => 'MonthlyReport', host => 'computername', }; };

How do I turn debug on?

-- JenniferWoss - 12 May 2008

I took out the usermap information in the config file. I changed the query to say %DBI_QUERY{MonthlyReport}% select * from Status %DBI_QUERY% and I don't get any errors anymore. But nothing gets returned. If I change Status to Statsu or something that isn't a correct table, I get load of output from Perl and other things saying it isn't a valid table, etc. So I'm guessing the syntax is correct. If I do a tcpdump on the sql server and look for src and dst messages from the twiki server, I see a lot of http messages when I save/refresh the twiki query page. However if I limit the messages to the sql port (3306) I don't see anything. Maybe I have the port set wrong somewhere?

-- JenniferWoss - 12 May 2008

@Jennifer

OK, let's check a few things.

Here is my DBIQueryPlugin.cfg file:

%dbi_connections = (
    mccarthy => {
        user => 'duncan',
        password => '*******',
        dsn => 'DBI:Pg:dbname=mccarthy',
    },
);

Feel free to copy the above and edit it for your case.

Comparing mine with yours I noticed a few things:

  • Firstly, you have closed the last bracket as a curly bracket, but this should be a round ')' bracket.
  • Also, you have a semi-colon between the last two brackets, and I believe this should be a comma.

Now, I am using the DSN format for defining the DBI connection. You could try using that instead.

To test a Perl DBI connection to your database, here is a simple script that you could try:

#!/usr/bin/perl
#
#

use DBI;

my $dbh = DBI->connect('DBI:Pg:dbname=mccarthy', 'user', 'password')
        or die "Couldn't connect to Database.";

print "Connected Successfully!!\n";

$dbh->disconnect;

The first parameter of the DBI->connect is the DSN for the database and 'user' and 'password' are the ones you are using.

Copy and paste the above into a file and run it with perl. If it doesn't work, then there is something wrong with the connection to the database or the format of the DSN.

@Kirill

Yes, you are right. I just tried the DEBUG option myself and it does not seem to produce anything in the debug.txt. One more thing to look at! smile

-- DuncanKinnear - 12 May 2008

Duncan!

I have resolved my problem with DBIQueryPlugin. As I use the Oracle DB, I have to use dsn to setup proper connection string to db.

Thanks a lot for your help.

-- KirillMessel - 13 May 2008

@Jennifer

Just looked at your example above and realised that it would not output anything anyway. Just doing the SELECT does not actually output anything. Here's an example of something that would output something:

%DBI_QUERY{MonthlyReport}%
   select id, name from Status 

.header
\n| *ID* | *NAME* |

.body
\n| %id% | %name% |

%DBI_QUERY%

The SELECT only loads the records in 'memory' and then creates the variables corresponding to the column names. You actually need to output those variables in either the .header, .body or .footer sections to see something on the screen

So your database connection may actually be working OK.

-- DuncanKinnear - 13 May 2008

FYI - installed the DBIQueryPlugin today on TWiki-4.2.0, Tue, 22 Jan 2008, build 16278 and successfully connected to MS SQL 2000 server and extracted data. Server is running Ubuntu hardy server, Apache 2.2 with mod_perl and twiki is hand-installed ie a non-packaged install. I had to install libdbd-sybase-perl to add the mssql driver for Perl DBI.

My DBIQueryPlugin.cfg is of the form...

%dbi_connections = (         mydata => {                 user => 'user',                 password => 'password',                 dsn => 'DBI:Sybase:server=my.server.fqdn;port=1433;database=dbname',         }, ); 

It took some fiddling to arrive at the DSN above but it works. This uses the version of DBIQuery Plugin, unmodified, from the plugin home page.

-- ColinFee - 25 Jul 2008

Hi all,

I am TWiki newbie... I installed the DBIQuery Plugin and I am going through the dev page to clarify my doubts.. that was helpful to a great extent. I have a few clarifications yet dsn => 'DBI:Pg:dbname=mccarthy', dsn => 'DBI:Sybase:server=my.server.fqdn;port=1433; database=dbname',

I could not make out what these statements signify. Thank you

-- SashankhDonkena - 16 Aug 2008

Any help would be appreciated... Thanks again

-- SashankhDonkena - 16 Aug 2008

@Sashankh

The 'dsn' defines the Data Source Name of the database you want to connect to. In our case we are connecting to a Postgres (Pg) database whose name (dbname) is 'mccarthy'.

Here is a simple perl script that you can execute on your server to discover the Data Sources on that machine:

#!/usr/bin/perl
#
# Script to discover DBI Data Sources

use DBI;

my @drivers = DBI->available_drivers();

foreach my $driver (@drivers) {
    print "Driver: $driver\n";
    my @dataSources = DBI->data_sources( $driver );
    foreach my $dataSource ( @dataSources ) {
        print "\tData Source is $dataSource\n";
    }
    print "\n";
}

when you run this script you should get something like the following:

Driver: DBM
        Data Source is DBI:DBM:f_dir=.

Driver: ExampleP
        Data Source is dbi:ExampleP:dir=.

Driver: File
        Data Source is DBI:File:f_dir=.

Driver: Pg
        Data Source is dbi:Pg:dbname=mccarthy
        Data Source is dbi:Pg:dbname=postgres
        Data Source is dbi:Pg:dbname=template0
        Data Source is dbi:Pg:dbname=template1
        Data Source is dbi:Pg:dbname=test

-- DuncanKinnear - 17 Aug 2008

Thanks a lot Duncan

Should we edit the DBIQueryPlugin.cfg manually or is there any interface to do that like we had for the Database Plugin?? Thank you.

-- SashankhDonkena - 18 Aug 2008

Yes, the cfg file needs to be manually created in the 'lib' sub-directory under you main twiki install directory.

-- DuncanKinnear - 18 Aug 2008

Is there a template/description of what the cfg file should look like?

-- DipuDeshmukh - 18 Aug 2008

Yes, it is in the main topic for this plugin.

-- DuncanKinnear - 19 Aug 2008

Thanks a lot Duncan..

-- SashankhDonkena - 19 Aug 2008

I am trying to build a drop-down list, populated dynamically from a column in a DB table. Has anybody tried to do this? Would anybody be wlling to share a code snippet here?

-- DipuDeshmukh - 21 Aug 2008

I have done this on a form on our TWiki.

I did this with a Sub-Query similar to this one:

%DBI_QUERY{"mydatabase" subquery="SelectModuleNames"}%
SELECT Module, ModuleName FROM Module
.header
<OPTION SELECTED DISABLED VALUE="">(select module)</OPTION>
.body
<option value="%module%">%modulename%</option>
%DBI_QUERY%

and the HTML form has a table with the following:

|  *Module* | <select name="Module"> %DBI_CALL{"SelectModuleNames"}% </select> |

Hope this helps.

-- DuncanKinnear - 24 Aug 2008

By the way, I could have put the <select .... and </select> into the query as well, but that does not allow you to use the same list of options on different forms where the field name is different.

-- DuncanKinnear - 24 Aug 2008

Hi Duncant, To continue with this discussion I have some more doubt regarding this. As you specified, subquery call will fill drop-down. But how can I get selected value on form submit button click. Where I want selected value to filter the query result. How can it be possible? Is their any event for submit button click so I can get selected value and then I can prepare filter query? If such event is their how i have to implement it? Any pointer or any code snippet will be a great help.

-- RahulWagh - 25 Aug 2008

This is actually basic web-form coding which is probably described better in other topics on this web site. But I will tell you how I do these things.

Consider you have a database that has a table of Countries and a table of Cities each of which have a reference to the City's Country.

There are two ways to list the Cities for a selected Country.

The easiest way to do it is to have two topics. The first one has the form with the droplist of Countries and the second topic has the DBI_QUERY code in it to select Cities by Country. The 'action' of the form in the first topic is to 'view' the second topic. The value of the Country droplist from the first topic is passed to the second topic as a URLPARAM.

Here's the text of the first topic:

%DBI_QUERY{"mydatabase" subquery="SelectCountries"}%
SELECT code, name FROM Country
.header
<OPTION SELECTED DISABLED VALUE="">(select Country)</OPTION>
.body
<option value="%code%">%name%</option>
%DBI_QUERY%

<form action='%SCRIPTURLPATH{"view"}%/%WEB%/CountryCities'>
| *Country:* | <select name="CountryCode"> %DBI_CALL{"SelectCountries"}% </select> |
|<input type="submit" class="twikiSubmit" value="List Cities for this Country" /> ||
</form>

And here's the text of the second topic (CountryCities):

---+ List of Cities in %URLPARAM{CountryCode}%

%DBI_QUERY{"mydatabase"}%
SELECT c.name FROM City as c
WHERE c.country = '%URLPARAM{CountryCode}%'
.header
\n| *City Name* |
.body
\n| %name% | 
%DBI_QUERY%

The harder way is to have both the form and the DBI_QUERY code on the same topic. I'm not 100% sure how you would do that as I have not tried it myself. I have an idea how you would do it, but unfortunately I don't have time right now to try it out. You would almost certainly have to use the IfDefinedPlugin to help you out.

-- DuncanKinnear - 26 Aug 2008

OK, curiosity got the better of me and I tried the one topic approach.

Here's the text of a single topic for listing the Cities for a Country:

%DBI_QUERY{"mydatabase" subquery="SelectCountries"}%
SELECT code, name FROM Country
.header
<OPTION SELECTED DISABLED VALUE="">(select Country)</OPTION>
.body
<option value="%code%">%name%</option>
%DBI_QUERY%

<form action='%SCRIPTURLPATH{"view"}%/%WEB%/%TOPIC%'>
| *Country:* | <select name="CountryCode"> %DBI_CALL{"SelectCountries"}% </select> |
|<input type="submit" class="twikiSubmit" value="List Cities for this Country" /> ||
</form>

---+ List of Cities in %URLPARAM{CountryCode}%

%DBI_QUERY{"mydatabase"}%
SELECT c.name FROM City as c
WHERE c.country = '%URLPARAM{"CountryCode" default="BLAH!"}%'
.header
\n| *City Name* |
.body
\n| %name% | 
%DBI_QUERY%

The "BLAH!" bit is to stop the DBI code from producing an error (try it without the default clause)

Note: this is not perfect as the heading will display "List of Cities in " (without a Country) before you select your first Country from the drop-list.

-- DuncanKinnear - 26 Aug 2008

Yet another approach is to use a 'drill-down' approach with your heirachical data.

The first topic could contain a DBI_QUERY that lists the Country names in a bullet list, with each name being a clickable link to the second topic with the appropriate URL paramter appended to the link. The second topic would be just the same as the CountryCities topic I had above.

Let me know if you want an example of this too, as I have used this a lot.

-- DuncanKinnear - 27 Aug 2008

I am also planning to use drill-down approach. But my problem is creating where clause. For first time their will be no parameter passing in url but when any link clicked by user i am passing that parameter through url. But this approach is good if only we require one level drill-down. For ex. Table contains column Country, State, City, Area. User will drill down as follows country-->state -->city-->area. Then each time where clause of SQL query changing. When page loaded first time no where clause, when click on country i.e. country='Country1' and so on. Also user can click on any column so it should filter according to that. Drill-down order should not fix. For ex. user clicked directly on state then it should display entries which has state='state1'.

Also can we have OR condition. Drill-down only support AND condition in where clause.

Let me know any pointer to archive this or any code snippet.

-- RahulWagh - 28 Aug 2008

How to execute Stored procedure or function?

-- RahulWagh - 19 Sep 2008

I would like my twiki page using this plugin to access different databases. I tried using a twiki variable to toggle my dbi_connection variable. I can't seem to get it to work. Anybody tried this? Is there a code snippet that somebody could share as an example?

-- DipuDeshmukh - 16 Oct 2008

What is wrong when I use a "group by" function then just the first and last row will be displayed?

-- PeterDobler - 24 Oct 2008

Can you post your DBI_QUERY code, or a cut-down version that exhibits the same behaviour? Can you cut-and-paste the SQL code into an interactive query tool for your database and get the correct result?

-- DuncanKinnear - 26 Oct 2008

Is there any way I can get a twiki variable (MYCONNECTION in the example below) be expanded, when I use it inside the plugin. i.e.

%DBI_QUERY{"%MYCONNECTION%" subquery="SelectedRRTP"}%
Select testplan_id From RRTP Where RRTP_ID='%<nop>URLPARAM{"rrtp_id" default="BLAH!"}%'
.body
<option value="%rrtp_id%">%testplan_id%</option>
%DBI_QUERY%

-- DipuDeshmukh - 27 Oct 2008

I updated this plugin to TWiki 4.2.4 and also corrected the example application.

-- ThomasWeigert - 19 May 2009

Thank you Thomas for updating this plugin!

I checkmarked also 4.3 since there have been no API changes between 4.2 and 4.3.

-- PeterThoeny - 2009-05-19

To enable debug, you need to change the following line in the plugin code, inside the initPlugin routine

$debug = TWiki::Func::getPluginPreferencesFlag("DBIQUERYPLUGIN_DEBUG");

to

$debug = TWiki::Func::getPreferencesFlag("DBIQUERYPLUGIN_DEBUG");

-- DipuDeshmukh - 2010-08-25

Thanks Dipu. The plugin should be updated accordingly.

What TWiki version do you use? Have you tested it on TWiki-5.0?

-- PeterThoeny - 2010-08-26

The proposed changes to DBIQueryPlugin are shown below. Basically it is just add a new tag of DBIQUERY (the existing tag is DBI_QUERY) that is serviced via registerTagHandler() and then a new routine to handle this new tag.

In initPlugin() add:
                TWiki::Func::registerTagHandler('DBIQUERY', \&_DBIQUERY);

Add the following routine above processPage()

sub _DBIQUERY {
    my ($session, $params, $theTopic, $theWeb) = @_;
    my $p = new TWiki::Attrs($params->{_RAW}, 1);
    my %p = %$p;
 
    $level++;
 
    my $conname = $p{name};
    my $sql = $p{sql};
    my $header = $p{header};
    my $body = $p{body};
    my $footer = $p{footer};
    my $cmd = "$sql\n";
    $cmd .= ".header\n$header" if (defined($header));
    $cmd .= ".body\n$body\n";
    $cmd .= ".footer\n$footer" if (defined($footer));
    my $ret = storeQuery($conname, $cmd);
    handleQueries;
    $ret =~ s/%(DBI_CONTENT\d+)%/$queries{$1}{result}/ges;
 
    $level--;
 
    db_disconnect if $level < 1;
    return $ret;
}

-- TaitCyrus - 2011-05-17

Hi Tait,

Why not make this handle the standard DBI_QUERY? Are you concerned that it might break existing code?

We have used DBI_QUERY extensively. I would hate to have to modify every instance, simply to take advantage of the new behavior. Also, what about DBI_CALL and DBI_SUBQUERY? What specific problem are you trying to address?

-- DipuDeshmukh - 2011-05-17

My only concern with making the tag handler use DBI_QUERY verses DBIQUERY (or some other tag name) is that the usage is totally different. The current behavior is:

%DBI_QUERY{“db_identifier”}%
<SQL>
.header
<head>
.body
<body>
.footer
<foot>
%DBI_QUERY%

While the proposed new usage would parameterize all of this to look like:

%DBIQUERY{
name=”db_identifier”,
sql=”<SQL>”,
header=”<head>”,
body=”<body>”,
footer=”<foot>”
}%

This is the main reason a different tag name was proposed. That and I don’t know what side-effects this might have that breaks how other people are using this plugin. According to DBIQueryPlugin#Drawback_and_problems there was an obvious decision to not use registerTagHandler() because doing so would cause problems. So I was thinking of keeping the existing behavior so-as to not break existing behavior, but then also provide improved behavior using registerTagHandler() and this is why I used a different tag name.

As for DBI_CALL and DBI_SUBQUERY, I didn’t look at them as to what would be needed to support calling them via registerTagHandler(). A quick look at the DBI_DO code says that there would be some issues since it assumes/requires the page contents be passed in.

I'd earlier suggested a new plugin (DBIQuery2Plugin). I could see this new plugin retaining the tag names (DBI_QUERY, DBI_CALL, and DBI_SUBQUERY) but using registerTagHandler(). So if a user wanted the current behavior, they would install DBIQueryPlugin. If they wanted the new behavior, they would install DBIQuery2Plugin (or whatever plugin name might be better), though the they would need to know that the API for DBI_QUERY changes.

The specific problem I was trying to address is that I've numerous pages with passed in parameters. Those parameters are then used inside of a %SET{}% (actually a conditional %SET%) and I need a %GET{}% inside of the %DBIQUERY{}% tag on that same page to get the %SET% value and then from the resulting table a %CHART% is generated. The existing API (as documented in the above URL) would not expand the %GET{}%.

-- TaitCyrus - 2011-05-17

Good point, changing the variable name imposes also an incompatibility.

Here is a new idea: How about adding a LEGACY2009 plugin preferences setting that, if enabled, does the old behavior, else the new registerTagHandler based behavior?

Later... Actually, with the current spec of:

 %DBI_QUERY{"db_identifier" ...}%
 SELECT ...
 %DBI_QUERY%
it is not possible to use the registerTagHandler because the registerTagHandler-based callback only gets the parameters within the curly braces, not text between two TWiki variables. The new feature PassTextAndMetaDataToRegisterTagHandlerCallback addresses that point, but it will be available only from upcoming TWiki-5.1 on.

In other words, the syntax has to be changed in order to take advantage of the proper expansion order registerTagHandler offers. So, it looks like best approach based on circumstances is to add a new %DBIQUERY{}% variable where all parameters are handled within the curly braces.

-- PeterThoeny - 2011-05-17

I believe I've come up with a way this plugin can use a registerTagHandler() handler while at the same time keeping the current usage for this plugin of:

       %DBI_QUERY{}%
       ...args...
       %DBI_QUERY%
Along with a new and improved usage.

What I'm thinking is that commonTagsHandler() would convert the current DBI_QUERY format into the new format (using parameters inside of %DBI_QUERY{}% allowing the registerTagHandler() handler to process the query.

For this to work, both

  • registerTagHandler
  • commonTagsHandler
Would need to be supported simultaneously. Will that work?

This would allow this plugin to be updated to use registerTagHandler() while continuing to support the old/original DBIQuery API.

Would this work?

This could also be used for %DBI_DO{"db_identifier" ...}% and %DBI_CODE{"script_name"}%

-- TaitCyrus - 2011-05-23

Good idea, but I think converting the syntax in commonTagsHandler for the callback of registered tag handler will not work because registered tag handlers and other TWiki internal variables execute before commonTagsHandler is called. I have not studied the code, but this is the behavior I think. You could look into using the beforeCommonTagsHandler.

BTW, PassTextAndMetaDataToRegisterTagHandlerCallback is now implemented, scheduled for TWiki-5.1.

-- PeterThoeny - 2011-05-24

I added e-mail discussion in chronological order above for reference.

This plugin uses the beforeCommonTagsHandler which causes issues as described in OverridePluginsOrder.

IMHO a pragmatic approach that keeps things compatible is to add a new %DBIQUERY{}% variable (in addition to %DBI_QUERY{}% that has an underscore) where all parameters are handled within the curly braces. Someone needs to take ownership to work on these enhancements.

-- PeterThoeny - 2011-06-14

Just a note about including pages which use DBI_QUERY. It appears that any reference to a subquery, that is not on the same page as the invoking query, cannot be resolved. This behavior is inconsistent with the principle of page inclusion, where it is expected that items in the included pages can be referenced by entities in the including page.

-- DipuDeshmukh - 2011-06-16

Edit | Attach | Watch | Print version | History: r112 < r111 < r110 < r109 < r108 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r112 - 2011-06-16 - DipuDeshmukh
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2016 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.