Message-Id: <3.0.32.19990429121306.009afbf0@ren.cncware.com>
Date: Thu, 29 Apr 1999 12:13:08 -0700
To: pgsql-interfaces@postgreSQL.org
From: "Ken J. Wright" <ken@ori-ind.com>
Subject: Re: [INTERFACES] pgaccess & null dates
Cc: Constantin Teodorescu <teo@flex.ro>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

Ok, yes I agree. Absolute user control this way. I would however, make one
more quickie change to remove case sensitivity:

>+       if {[string toupper $sqlfldval] == "NULL"} {

Ken


At 01:20 PM 4/29/99 -0500, Ross J. Reedstrom wrote:
>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----------------
-----------------
>--- 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--- 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} {
>