Inserting empty string for a not null field

[tswone]

If a field is varchar (not null), and it is an empty string, Datamgr
allows the empty string to be inserted.

Is this correct, why wouldn't it see that the field is not null, and
the value has a length of zero, then change it to a null so the record
would not get inserted?

It will do the insert, but will fail, but will then error saying:

"Data Manager: A unique record could not be identified from the given
data."


Maybe I dont understand the error message.

Thanks,

Wonderful code you made.

[Steve Bryant]

It looks like you found a bug.

Let's try this:

1) Find:
<cfset arguments.null = "yes">
and comment it out (should exist only once in DataMgr)

2) Find:
<cfif Len(Trim(fieldval))>
Replace with:
<cfif Len(Trim(fieldval)) OR NOT sField.AllowNULLs>

Let me know if that works or not.

If it doesn't, could you send me the following:
1) The result of getDatabase()
2) The result of getVersion()
3) The result of getXml("bob") (replacing bob with the name of the
table you are using)
4) The code you are using when you get the error

I look forward to hearing how you get on.

Thanks!

Steve

On Fri, Oct 23, 2009 at 12:58 PM, tswone <tsw...@gmail.com> wrote:
> If a field is varchar (not null), and it is an empty string, Datamgr
> allows the empty string to be inserted.
>
> Is this correct, why wouldn't it see that the field is not null, and
> the value has a length of zero, then change it to a null so the record
> would not get inserted?

That is correct. Some people store empty strings in their database so
DataMgr does nothing to prevent this.

> It will do the insert, but will fail, but will then error saying:
>
> "Data Manager: A unique record could not be identified from the given
> data."

That looks like a bug (see steps outlined in my reply above).

[Tad Wilkinson]

Steve, thx for replying:

I found and replaced the lines you asked me to. Now I don't get the
error ("Data Manager: A unique record could not be identified from the
given data."), the operation has no error, BUT the row is still
inserted in my table with the NOT NULL field (Column 'Title') being an
empty string. So it still is allowing an empty string to be inserted
into a NOT NULL field.


He is the debugging info you asked for:

MS SQL
2.5 Alpha 2 Dev 1
<tables> <table name="tblLandingPages"> <field ColumnName="PageID"
CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Length="255"
AllowNulls="false" /> <field ColumnName="FriendlyURL"
CF_DataType="CF_SQL_VARCHAR" Length="250" Precision="12" Scale="2"
AllowNulls="true" /> <field ColumnName="Title"
CF_DataType="CF_SQL_VARCHAR" Length="250" Precision="12" Scale="2"
AllowNulls="false" /> <field ColumnName="Description"
CF_DataType="CF_SQL_VARCHAR" Length="500" Precision="12" Scale="2"
AllowNulls="true" /> <field ColumnName="Keywords"
CF_DataType="CF_SQL_VARCHAR" Length="250" Precision="12" Scale="2"
AllowNulls="true" /> <field ColumnName="ReferrerCode"
CF_DataType="CF_SQL_VARCHAR" Length="20" AllowNulls="true" /> <field
ColumnName="HTML" CF_DataType="CF_SQL_VARCHAR" Length="2500"
Precision="12" Scale="2" AllowNulls="true" /> <field ColumnName="CSS"
CF_DataType="CF_SQL_VARCHAR" Length="2500" Precision="12" Scale="2"
AllowNulls="true" /> <field ColumnName="Created"
CF_DataType="CF_SQL_DATE" Length="255" Default="getDate()"
AllowNulls="false" Special="CreationDate" /> <field
ColumnName="Updated" CF_DataType="CF_SQL_DATE" Length="255"
Default="getDate()" AllowNulls="false" Special="LastUpdatedDate" />
<field ColumnName="Deleted" CF_DataType="CF_SQL_DATE" Length="255"
Default="0" AllowNulls="false" Special="DeletionMark" /> <field
ColumnName="Sort" CF_DataType="CF_SQL_INTEGER" Length="255"
Default="0" AllowNulls="true" Special="Sorter" /> </table> </tables>

and the code:

It is just a regular form (that has the 'Title' field) posting to a
script that has save record:

<cfset pageID = Application.DM.saveRecord('tblLandingPages', FORM)>


Thanks again,

Thomas

[Steve Bryant]

Thomas,

Thanks for the information. I will make that fix part of the next
build I release.

The insertion is actually just how DataMgr works, sorry. Since some
people insert empty strings into NOT NULL fields, DataMgr allows this.
Part of the philosophy to allow people to work however they want.

Let me know if you need anything else,

Steve

[T W]

Ok, thanks for clarifying!

-thomas