WDBI doesn't work at all. All I get is this : 500 Server Error. The server encountered an internal error or misconfiguration and was unable to complete you request.
Yes. HTTP error messages are not very helpful. Try to look in the HTTP servers error log instead. It should be in logs/error_log under your HTTP installation. I try to send the MIME type from WDBI as early as possible, so normally error messages should be passed to the client. So you would see the WDBI generated error message instead of the little helpful server error message. This could indicate that it is a very basic thing that doesn't work. - Perhaps a missing ; or " in the setup section ? or a missing include file ? - Try to simply run WDBI from the command line. If it works you should see an error message like this :
Content-type: text/html <html><head><title>No action specified. WDBI aborting. </title></head> <body><h1>No action specified. WDBI aborting. </h1> </body></html>
I have tried repeatedly to get perl to compile but with no luck. I am running Solaris 2.3.
Read the Perl FAQ : "1.13: How do I get Perl to compile on Solaris?" You can find the Perl FAQ and other useful information at archives such as the Nexor archive (UK) http://web.nexor.co.uk/perl/perl.html, there are a few archives in the US as well, they are mentioned in the Nexor home-page.
Do the database server, DBD/DBI and httpd have to be on the same host?
No. The database server does not have to be on the same host. As WDBI is called
by the http daemon, they have to be on the same host. - And as WDBI was written
calling DBD/DBI perl interfaces, they have to be on the same host too. Then
WDBI will be able to access any database server it supports anywhere in the
net, just like any other database client. The architecture is as illustrated
in the following figure:
+------------+ +-------------------+ +----------------+
| WWW Client |----| HTTP Server | | |
| (Mosaic) | | (Apache's http) | | |
| | | | | | |
| | | WDBI | | |
| | | | | | |
| | | DBD/DBI ---------|----|----Database |
| | | | | Server |
| | | | | |
+------------+ +-------------------+ +----------------+
Each machine can be of a different type : PC, NT, SUN, VAX, HP etc. but you need the DBD/DBI compiled for the machine type where you plan to use WDBI. Any WWW client will do, as long as it supports HTTP and HTML.
Can authentication be used, so as to support privileged access and data protection?
The normal HTTP access restrictions can be applied to the system as a whole: either only allow access from specific hosts, or by specific users. This can be done without changing the WDBI system. For more on the Authentication mechanisms in the HTTP protocol - check :
But if you want to support privileged access to specific rows in the database it gets a little more complicated.
For example if all but a specific user should be denied access to a specific FDF you could add
PERL = if ( $ENV{'REMOTE_USER'} ne "arnaldo" ) {
print "Bug off !!!"; # - or something more polite ! :-)
exit 1;
}
Please note that the environment variable 'REMOTE_USER' is only set if you use the NCSA httpd's access authorization to allow only specific users to access the WDBI script.
Enum attribute does not work on PC-Mosaic. They are perfectly ok with X-Mosaic.
This is a bug in PC-Mosaic. The selectable options in the 'enum' field are
translated into the following HTML code:
<option value="xxx"> YYYYYY
Where "YYYYYY" is the label displayed to the user, and "xxx" it the database value to search for. - the problem with PC-Mosaic is that it returns the label instead, ignoring the "value" attribute.
What about using WDBI for querying terms containing an apostrophe like O'Neil, Dell'Antone, Dona' etc?
WDBI should escape single quotes, but Informix escapes them by putting two of them together, and mSQL escapes them by putting a slash in front of them. So it was solved using a dbi_ function call, ie a specific management of ASCII 39 (the apostrophe) for each interface supported.
It seems that the default value for a query is the operator 'like', not the 'equal to'. How can an *exact match* query be done?
The operator like is the default one. In the WDBI help file, it tells you to put a double equal '==' in front of your query parameters, like so:
==O'Neill
The same thing, in a more elegant way, can be done by creating an FDF where the field(s) to be searched for can use operators from a drop-down menu, like this:
... FIELD = title label = Title column = title type = char length = 50 no_tab operators = ~!contains, ==! equal to ...
It appears like this on the query form:
How 'sub_query' and 'SUB_QUERY_HEADER' attributes work?
sub_query lets you execute a SQL query at any point in an FDF and display results using another FDF. SUB_QUERY_HEADER is only used when you put a sub_query attribute in another FDF.
Here's an example:
How the path to RECORD_COUNT_FILE, VISITOR, VISITOR_COUNT_FILE etc. should be referenced?
The path used is the real path and all the subdirectories must exist. Moreover, httpd process must have read and write privs on the target directory and file. The file must exist because WDBI won't create it. There are several examples in the sample_fdf directory.
The "Form Definition File Syntax" documentation says that all characters after a '#' are ignored in a FDF. But URL and and colors definition use that symbol. Any comments?
Looking at the ParseFile subroutine it is posible to see what WDBI does. WDBI chops off anything in a line that comes after a '#' character on a non-comment line. However, if the entire line is enclosed in double-quotes, WDBI slurps up the entire line, quotes, #-signs, and all. This only works for attributes that are eval-ed (see the WDBI docs for each attribute to see which are evaluated) because otherwise the quotes become part of the field value.
I have problems setting up mysql privileges. In my httpd.conf I have:
User nobody Group #-1
When I try to access wdbi I have permission denied. What can I do?
You have to add 'nobody' to the list of users who can access the database.
Your server runs as 'nobody' so you add the rights like:
shell> mysql mysql
mysql> insert into user values('%','nobody','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); >\g shell> mysqladmin reload
See also: How does MySQL privileges work
I created a distributed database where some people all around the world can insert/modify/delete ONLY records they input. Suppose that John inserts records #1, #2, #3, while Paul inserts records #4, #5, #6. I want Paul can modify/delete only records #4,#5,#6, not #1, #2,#3. How can I do?
Just make the user insert some information known only to him/her!!! - like a password. So when (s)he makes an insert you return it to him/her by browser and/or email. When (s)he reenters (s)he must enter this info in the field you specify and even without checking you call mysql with:
SELECT ... WHERE passwd = 'his/her passwd'
If it's wrong -- it will fail!
You may want to make a query before to check by your code the correctness of the info (s)he provided!
Thats All!
WDBI manual says that a 'type' attribute for a field could be only 'int', 'float', 'char', 'datetime'. What about other 'type' of fields like 'blob', 'varchar' , 'real' etc...?
Perl has two data types: character and number. You define all others in terms
of those and use printf to format the output. A blob is just a big
char. A varchar is just a char. Int and real are the same thing with
printf truncating the decimals on output of an int. The problem
with a blob is you have no way of getting the size of the blob from the database.
Perl can often figure out size, but only if the source is null-terminated.
Not all databases do that.
For this purpose WDBI has an 'image' type where the source of the
field is a CGI program that spits out a binary image.
In the WDBI manual it is reported that it is not possible to feed directly from a database an image into WDBI. It is stated that a CGI is needed to get the image back. How can I do?
Here is the inline comments from the WDBI code:
#------------
# This is a kludge to handle images in a database. It would be easy to
# just select the data from the database and spit it out at the browser
# to display, except the browser has to be told the data is a binary
# image and the only way to tell it that is with the <IMG> tag.
# So, image_src can be the URL to a picture, but it can also be the URL
# to an external cgi program that returns an image as the output of the
# program. Either can be used in an <IMG> tag.
# For example, image_src could be something like this:
# image_src = "SRC=http://somewhere/cgi-bin/get_image.cgi/$val{'key'}"
# The get_image.cgi program would select the image(s) based on the key
# value, and spit out a Content-type header and the binary data for the
# image.
#------------
So you must create a program that needs to return "Content-type: image/jpeg\n\n" or whatever is appropriate for your image type, and then the image in binary format. A possibility is to store the images in the database uuencoded using Perl's pack routine to uuencode it. Then it is possible to unpack them after selecting one from the database just using a print statement to print the binary images to STDOUT. Any programming language can be used to achieve this goal.
How can I set the JOIN attribute when I have more than a join?
In the FDF file put:
JOIN = table1.field1 = table2.field2 AND table1.field1=table3.field3
and so on for as many as you need.
What about the possibility to use 'GROUP BY' in WDBI?
Not all databases support GROUP BY and those that do could very well have vastly different syntax. So, to support everything possible, use the EVAL_SQL attribute. Remember to quote it, because it gets run through perl's eval.
How can I refine queries at 'WHERE' level?
Use the attribute CONSTRAINTS
Suppose you want to create a query like this:
SELECT * FROM foo WHERE unit_price * quantity > 1000
You can create an FDF with:
CONSTRAINTS = unit_price * quantity > 1000
Everything in the JOIN and CONSTRAINTS attributes are just 'and-ed' onto the 'where' clause. It's up to you to make sure the constraints you add are supported by the database :-)
How can I use WDBI to create a query form in order to search
for substrings inside a field?
I want to something like TITLE = 'foo' AND 'bar' where 'foo' and 'bar' are
both part of the content of the field 'title' ie possibility of searching
with an 'AND' inside a field with WDBI?
You want x contains 'foo' and x contains 'bar' so WDBI is searching for substrings. It is possible to do this with WDBI by using two different input boxes for the same field. An example of how to do this is in the sample_fdf directory in the ir_query.fdf file. Look for Cal Due Date, which is the last two fields in the FDF.
An important trick is however this. If you use 2 different FDF, one for the query and one for the results then they MUST be almost identical for what the field attributes are concerned. So, if you want to have some fields in the query and others in the results, then the attribute 'hidden' can be used.
Suppose you have a query.fdf like this:
.... #First field FIELD=title1 column=title ..... #Second field (ie first duplicated) FIELD=title2 column=title .... FIELD=city column=city ....
Then the results.fdf must contain:
.... #First field FIELD=title1 column=title ..... #Second field (ie first duplicated) FIELD=title2 column=title hidden # --> I want only one title field in the results, of course!!! .... FIELD=city column=city ....
This way, it works.
Comment: WDBI was originally designed so the FDF could be used for *both* form generation and results display. This is part of the elegance of Rasmussen's design. Using two FDF files, one for "query" and one for "results" seems redundant and doubles the maintenance. So the other possibility is to use one FDF like this:
#First field (query only) FIELD=title1 column=title no_tab no_full ..... #Second field (query only) FIELD=title2 column=title no_tab no_full .... #Third field (results only) FIELD=title3 column=title no_query forcetab ....
Is it mandatory or not to inizialize numeric fields in WDBI?
There is no perfect answer yet. All that can be said is that if you use WDBI to initialize the fields, they will be treated consistently.
I use mySQL and WDBI. When I create a new record I see that WDBI fills the numeric field with a default value of 0.000. Can I change this value?
It is possible to set this value to be whatever you want in the mysql_dbi.pl file.
I want to create a WDBI query form without padding dots between the label and input box. Is it possible to do this by using "no_dots" attribute in the FDF file?
It is now. In past versions of WDBI the no_dots attribute only worked on the output form. It now works on the query form as well.
I have two FDFs, one for query and another for results and I want to search in a numeric field. If I put searching criteria inside the input box everything is ok, while if I create a dropdown list with all the possible criteria, the query performed is always 'equal to' whichever operator I choose. Why?
You use two separate FDF files:
QUERY_FORM = foo_query RESULTS_FORM = foo_results
then you MUST have the operators attribute in BOTH FDFs. If you have it in the query FDF and not in the results FDF, you get exactly the behavior you are seeing.
I turned on the debugging and figured out that mySQL returns a 0 rows affected if there are NULLs in the WHERE field. I fixed this by making sure that any blank field has actually a NULL content, rather than a ''. However I noticed that if a record was created with WDBI, WDBI could update it, but couldn't if it was created from within mySQL.
MySQL uses NULL as a NULL field indicator, and so does WDBI. The value '' is not the same as a NULL, but the limitations of Perl datatypes make it impossible (AFAIK) to tell the difference. If you insert data using WDBI, you will be able to update and delete it (assuming it's not floating point data). If you insert values by hand that include '' values, WDBI won't work due to Perl's inability to tell the difference between '' and NULL.
How WDBI uses the 'key' attribute and key values passed in via the URL?
Each key that is in the URL (you can use more than one by separating them with '::', as in: key1::key2::key3) is matched IN ORDER to fields in the FDF listed as key fields. The first key in the URL is matched to the first field in the FDF listed as a key field, and so on. If you get things out of order, it will create a query where a key value gets associated with the wrong field.
I cannot seem to get the $val command to work right
url = "$WDBI/$form{'DATABASE'}/xxx/query/$val{'foo'}"
Here is my test.fdf:
#---------------------- NAME = test TABLE = test DATABASE = BAR TITLE = Prints DEF_HEADER = Test Default Form Q_HEADER = Test Database Query Form R_HEADER = Test Query Result Table BODY_FORMAT = BGCOLOR="FFFFFF" TEXT="000000" BACKGROUND="/images/strip.jpg" #DOCURL = # URL to documentation. # JOIN # CONSTRAINTS #ORDER = # ORDER BY columns ... #RECTOP = # Record title .... #PERL = # Extra perl commands .... COMMENTS_TO = xxx@aaa.net TABLE_RESULTS ALLOW_INSERT = TRUE #PRE_RESULTS = TRUE # Put query results in <pre> </pre> tags. #MAIN_MENU = # Override variable for this FDF only. #------------------------------------ FIELD = id label = Id column =id type = int length = 6 key FIELD = title label = Title column =title type = char length = 40 FIELD = subject label = Subject column =subject type = char length = 60 no_tab
Ahhhh. The field foo is not in your FDF. How can WDBI know anything about the value of a field when you aren't giving it any information? The $val variable only contains the value of fields that are specified in your FDF and are present in the query that comes back from the database. Add the foo field to your FDF and set it up so the field isn't displayed but it is part of the database query. I think (off the top of my head) that means DON'T use 'hidden', but use no_insert, no_update, no_query, etc. until it does what you want.
I am trying to set up a field called Teams which will allow an employee to be assigned to one or more team names. I would like a button bar or list that allows to select one or more team names. I tried using combinations of ENUM, LIST_FORMAT, LIST_MULTI, MULTIPLE, and MAKELIST with no useful results. I can't have WDBI pull the information from the database as it sends back such things as "HelpDesk,ITS", "HelpDesk", and "ITS". I want the choices to be separate. The end result should be an ASCII string of all selected teams, each separated by a comma. Any thoughts on this?
Try this:
Here's the details on how to do that:
I have been using WDBI with mysql and have been having trouble doing updates into mysql tables that contain NULL values, when the data selection (on the insert form) is from an enum field, while it is OK when entered directly. It seems that there is some problem with using enum to input data to null fields.
When you create an enum, do:
enum = = Choose here A = First opt B = Sec opt ...
etc.
See the first field, its value is empty!!! Since you have an empty default option - it's a null value in case if wasn't selected something else!
I have a simple table in a mySQL database which has its auto_incrmementing primary key specified as a 'serial_field' in the .fdf file. Insert and search work ok, but I just noticed that when I update a record in a table the primary key is set to zero (0). The problem occurs when a serial_field is also made a hidden field.
Well, hidden is hidden, period. If you don't want it to show up on update forms, that's what no_update is for, but with no_update the serial field value is kept instead of ignored.
When I put an image as background, how can I arrange the path?
In your FDF put:
BODY_FORMAT = BACKGROUND="/path_to_image/image.gif"
I have a table in mySQL which has two fields which together
form the primary key. The manual seems to say WDBI supports only one primary
key but is unclear if that key can be made up of multiple fields. Looking
at wdbi.cgi, it appears to handle multiple fields being declared
primary_key.
However when I use query to find a record and then select update, although
the two fields are passed to update as KEY_ARGS, ParseFile does not appear
to load the primary key fields into the @keys array and hence the KEY_ARGS
do not get recognised and update does not find the record.
Is WDBI able to handle a multi-field primary key? If not, how do I get update
to parse the KEY_ARGS correctly as it does not recognise the primary_key
parameter which is passed to it, only the 'key' field?
Did you mark the fields as *both* primary_key and key? The key attribute is what determines what gets put into the @keys array. This is original WDBI functionality. If you have a multi-field key, all fields must be marked with the key attribute and the Update and Delete fields in the FDF must contain all of the key fields in the URL, separated with '::' characters.
As an example:
$WDBI/$form{'DATABASE'}/$form{'NAME'}/update_form/$val{'field1'}::$val{'field2'}
The 'key' attribute is only used to generate the @keys array, which is only used when key values are passed via the URL, such as in More, Update, or Delete hyperlinks. It handles multiple key fields. The 'primary_key' field is used to perform inserts and updates across mutiple tables with primary and foreign key fields, but only when using a single field as a primary key. Now, assuming you have a multi-field primary key, how on earth is WDBI supposed to be able to figure out which primary key field should be mapped to which foreign key field? Only one primary_key field is supported.