Re: SOT: PostgreSQL problem and cfqueryparam
Rick Root wrote: I'm having a strange issue with postgresql... I have a database table containing a field defined as follows: ABSTRACT text not null default '' What is the function of DEFAULT ''? When I insert data, I use cfqueryparam as follows: cfqueryparam cfsqltype=cf_sql_longvarchar value=#arguments.details.abstract# And I'm seeing this error: [Macromedia][SequeLink JDBC Driver][ODBC Socket]ERROR: ExecInsert: Fail to add null value in not null attribute abstract Don't use ODBC, use JDBC: http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_18338 I would think that cfqueryparam would be sending an empty string, not a null... I would think the PostgreSQL ODBC driver doesn't know the difference. The simpler solution is just to allow null values in fields that are not required... but I tend to avoid null values as a general rule (I read somewhere that indices on not null fields are more efficient) You probably read that in the Oracle documentation (Oracle can't index NULL fields and doesn't know the difference between an empty string and a NULL). Index efficiency is governed by index selectivity, so NULLs can be both good and bad depending on how large the fraction of NULLs is in the table. In PostgreSQL you just use a partial index if there are too many NULLs. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192567 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOT: PostgreSQL problem and cfqueryparam
Jochem van Dieten wrote: ABSTRACT text not null default '' What is the function of DEFAULT ''? It's a simple and common option that assigns a default value upon INSERT if the field is not included. Don't use ODBC, use JDBC: http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_18338 I would think that cfqueryparam would be sending an empty string, not a null... I would think the PostgreSQL ODBC driver doesn't know the difference. I can't force all users of my application to use JDBC. I can't imagine that many hosting providers would say Sure, I'll install the postgresql JDBC driver for you. I'm just going to go with allowing nulls for my varchar and text fields in postgres... doesn't really affect my code, and the form validation prevents inserting of empty strings for things like title and content anyway. - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192588 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOT: PostgreSQL problem and cfqueryparam
Rick Root wrote: Jochem van Dieten wrote: ABSTRACT text not null default '' What is the function of DEFAULT ''? It's a simple and common option that assigns a default value upon INSERT if the field is not included. But why is that better then just inserting a NULL? I can't force all users of my application to use JDBC. I can't imagine that many hosting providers would say Sure, I'll install the postgresql JDBC driver for you. I can't imagine any hosting provider offering PostgreSQL not offering the JDBC driver. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192596 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOT: PostgreSQL problem and cfqueryparam
Jochem van Dieten wrote: But why is that better then just inserting a NULL? It guarantees that I don't ever have to worry about null values, I guess. Though I never realized that oracle didn't know the difference between null and an empty string. I can't imagine any hosting provider offering PostgreSQL not offering the JDBC driver. I can, because the hosting provider I'm using for www.blogcfm.org doesn't. - Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192600 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
