Re: [SQL] Date Validation?
> Richard, > >> Out of curiosity Josh, why aren't you validating in PHP? - only takes >> a >> couple of lines there. > > We are. It's just that all other validation takes place inside the > PL/pgSQL functions in order to keep all the business logic in the > database. Having one form of validation in the interface (PHP) and the > rest in the function can cause the user to go through multiple > validation errors, which is especially frustrating if the second > validation error is fatal. Example: [snip] Hmm - fair enough - PITA really. Poked around and had a think and can't come up with anything better than Stephan Szabo's suggestion of hacking PG's built-in function. You'll want to_timestamp() which is called from to_date and it's in src/backend/utils/adt/formatting.c - AFAICT you'll just need to comment out the elog(...) calls and return a null value (or whatever). Looks like there are a lot of dependencies - my C is too rusty to figure that out just by skimming. I'm guessing the validation is fiddly though - PHP's date handling code is no shorter. Failing that, what about writing is_valid_date() in pl-tcl/perl? Don't know anything about tcl, but should be easy enough in perl (cut & paste from a suitable module) Or (and this is horrible) check the date in PHP and if it's not valid replace it with NULL. You can check for the null in the trigger fn, but unfortunately you no longer have the original value for your error message (no, I don't like it either). For the interested on the list: The central problem seems to be that the error logging function elog() never returns from an ERROR message and kills the whole transaction. This makes sense since any code can call elog(ERROR,...) and not have to worry about recovering from the error. If PostgreSQL had been written using Java, there'd probably be try...catch everywhere and it wouldn't be an issue (of course there might well be performance problems as well as someone having to invent java 10 years before Sun did ;-) Presumably, once we have nested transactions all this will be magically solved by wrapping the possibly dodgy statements with an inner transaction. Aside: I may have found the world's first "Y2K BC" bug - if we make enough noise over this it could turn the IT industry round again. richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date; ?column?| ?column? ---+--- 2001-01-01 BC | 0001-01-01 BC (1 row) - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Date Validation?
Richard, > Poked around and had a think and can't come up with anything better > than > Stephan Szabo's suggestion of hacking PG's built-in function. You'll > want > to_timestamp() which is called from to_date and it's in > src/backend/utils/adt/formatting.c - AFAICT you'll just need to > comment out > the elog(...) calls and return a null value (or whatever). Not too likely from me ... I don't do C at all! I'm one of those horrible people who came to SQL & PHP from VB and MS Access rather than from a C.S. degree. Heck, my college degree is in sculpture. > Failing that, what about writing is_valid_date() in pl-tcl/perl? > Don't know > anything about tcl, but should be easy enough in perl (cut & paste > from a > suitable module) Sounds good. Anybody on the list I can beg (or pay $50) to write it and post it to the list? I don't know from perl or tcl either (I do SQL, PHP, Java and VB). > Or (and this is horrible) check the date in PHP and if it's not valid > replace it with NULL. You can check for the null in the trigger fn, > but > unfortunately you no longer have the original value for your error > message > (no, I don't like it either). Hmmm ... but given the lack of alternatives, may be better than the 2-stage system ... > Presumably, once we have nested transactions all this will be > magically > solved by wrapping the possibly dodgy statements with an inner > transaction. Yeah. I'm also assuming that when Jan and co. get full cursor support and stored procedures worked out, error trapping will also become available. Then this whole issue goes away with a simple ON EXCEPTION. > Aside: I may have found the world's first "Y2K BC" bug - if we make > enough > noise over this it could turn the IT industry round again. > > richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date; >?column?| ?column? > ---+--- > 2001-01-01 BC | 0001-01-01 BC > (1 row) Quick! Call the presses! Stop the computers! I could also make a number of Christian theological jokes, but I don't want to offend anyone on the list -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date Validation?
On Sun, 15 Jul 2001 06:40, Josh Berkus wrote:
> Richard,
>
> > Poked around and had a think and can't come up with anything better
> > than
> > Stephan Szabo's suggestion of hacking PG's built-in function. You'll
> > want
> > to_timestamp() which is called from to_date and it's in
> > src/backend/utils/adt/formatting.c - AFAICT you'll just need to
> > comment out
> > the elog(...) calls and return a null value (or whatever).
>
> Not too likely from me ... I don't do C at all!
'Tis a shame. I could teach you C if you want. Get the lessons from the URL
in the .signature box below.
> I'm one of those
> horrible people who came to SQL & PHP from VB and MS Access rather than
> from a C.S. degree.
Not 'horrible', nobody who has 'seen the light' in any way can be described
as "horrible". That description is reserved for those folks who revel in
their intimacy with darkness. ( How you define "darkness" is over to you )
> Heck, my college degree is in sculpture.
No wonder the aesthetic is so important to you.
> > Failing that, what about writing is_valid_date() in pl-tcl/perl?
> > Don't know
> > anything about tcl, but should be easy enough in perl (cut & paste
> > from a
> > suitable module)
>
> Sounds good. Anybody on the list I can beg (or pay $50) to write it and
> post it to the list? I don't know from perl or tcl either (I do SQL,
> PHP, Java and VB).
Well here is how I deal with the problem.
First you have to turn off the error reporting flag in the php.ini
configuration file. That stops most of the geek-speak rubbish getting out to
the client.
Now, please find attached the .php files I am using in a project.
I feel that the level of geek-speak in the solution is accepable,
but if it isn't to you or your clients, then you could always replace the
error message goming out of PostgreSQL to something more understandable to
the "Common Man / Lay Person" by editing the PostgreSQL source and
re-compiling it. ( not difficult ) Note that I have the database here
configured to the European representation of dates and the wording of the
error messages in the php file reflects this.
Also note that I have forgotton to test for dates being in the past, and that
it would be better if the error message was delivered in a pop-up html page
or a JavaScript alert.
Off the top of my head another untested idea for a solution to the problem is
to do the data verification in JavaScript on the client machine.
There is a JavaScript date.parse() function.
Qualify for the $50 ?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-> Please refrain from using HTML or WORD attachments in e-mails to me <-
Title: Record Legitimate Absence
Record Legitimate Absence
Pupil Number:
First Name:
Last Name:
Date of Birth:
Leave Date:
Return Date:
Duration:
Days
Weeks
Months
Title: Record Legitimate Absence
You have selected more than one pupil.
Select the one to record by clicking the name.
If none are correct then click the Another Try link.
NumberName Date of Birth \n
END_4;
for ( $i=0; $i < $number_rows; $i++ )
{
$name=pg_Result( $post_absence_result, $i, 'name' );
$number=pg_Result( $post_absence_result, $i, 'number' );
$date_of_birth=pg_Result( $post_absence_result, $i, 'date_of_birth' );
$query="number=$number&leave_date=" . htmlentities(urlencode($leave_date)) .
"&return_date=" . htmlentities(urlencode($return_date));
echo "$number" .
"$name" .
"$date_of_birth\n" ;
}
echo "Another Try";
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
