From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-interfaces@postgresql.org
Cc: Constantin Teodorescu <teo@flex.ro>
Subject: Re: [INTERFACES] pgaccess & null dates
Date: Thu, 29 Apr 1999 13:20:19 -0500
Message-ID: <3728A2E3.D1C9AC49@rice.edu> (raw)
References: <3.0.32.19990429101049.0085a740@ren.cncware.com>
Ken J. Wright wrote:
>
> At 08:59 AM 4/29/99 -0700, you wrote:
> >PgAccess 0.96, RH5.2
> >
> >When editing a table and clearing a datetime field in the grid, the update
> >fails because the update sql is not declaring the new value as null, but as
> >an empty string.
> >
>
> This fixes it, although it also makes an empty string a null. The lessor of
> evils I think, without changing to do some field type checking before the
> update.
>
Two things - your patch wrapped, so be careful - unified diff is more
readable BTW.
Second: No! don't do it this way! "" vs null is an important
distinction. Here's my take on a patch to handle this: it's a quick
'have something work now' hack. It uses the sql standard string
representation of a null, i.e. the word null :-)
Pgaccess has a few quoting problems still. I think the right solution is
to come up with an sqlquote proc that takes a string and returns the sql
quoting required to us it as a SQL value. This is seperate from an
sqlpquote, which would take a string and return the minimum quoting to
use it as a parameter id (field or table name). Then, go through
pgaccess and wrap all variables that derive from end user input (either
from the UI or from the pg database). The only problem is to remember to
_unwrap_ these in the right places (I've had this problem turn into
exponentially growing single quotes ').
Here's the patch: (also in a file attachment)
---------------------------------------------------cut-here---------------------------------
inline patch 1: pgaccess.tcl Thu Apr 29 13:08:31 1999 (+0/-1 lines) download inline-1.patch--- pgaccess.tcl.orig Thu Apr 29 12:54:35 1999+++ pgaccess.tcl Thu Apr 29 13:08:31 1999@@ -1422,7 +1422,19 @@
set mw($wn,msg) "Updating record ..."
after 1000 "set mw($wn,msg) {}"
regsub -all ' $fldval \\' sqlfldval
- set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" set
\"$fld\"
='$sqlfldval' where oid=$oid"]
+
+#FIXME rjr 4/29/1999 special case null so it can be entered into tables
+#really need to write a tcl sqlquote proc which quotes the string only
+#if necessary, so it can be used all over pgaccess, instead of explicit
's
+
+ if {$sqlfldval == "null"} {
+ set retval [sql_exec noquiet "update \"$mw($wn,tablename)\"
\
+ set \"$fld\"= null where oid=$oid"]
+ } else {
+ set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \
+ set \"$fld\"='$sqlfldval' where oid=$oid"]
+ }
+
}
cursor_normal
if {!$retval} {
---------------------------------------------------cut-here---------------------------------
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Patches and attachments:download all (2 files, .tar.gz)pgaccess_nulls.diff (text/plain, 889B) download--- pgaccess.tcl.orig Thu Apr 29 12:54:35 1999+++ pgaccess.tcl Thu Apr 29 13:08:31 1999@@ -1422,7 +1422,19 @@
set mw($wn,msg) "Updating record ..."
after 1000 "set mw($wn,msg) {}"
regsub -all ' $fldval \\' sqlfldval
- set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" set \"$fld\"='$sqlfldval' where oid=$oid"]++#FIXME rjr 4/29/1999 special case null so it can be entered into tables+#really need to write a tcl sqlquote proc which quotes the string only+#if necessary, so it can be used all over pgaccess, instead of explicit 's++ if {$sqlfldval == "null"} {+ set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \+ set \"$fld\"= null where oid=$oid"]+ } else {+ set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \+ set \"$fld\"='$sqlfldval' where oid=$oid"]+ }+
}
cursor_normal
if {!$retval} {
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: reedstrm@rice.edu, teo@flex.ro
Subject: Re: [INTERFACES] pgaccess & null dates
In-Reply-To: <3728A2E3.D1C9AC49@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox