MySQL Null Field

5 Message(s) by 3 Author(s) originally posted in cfml database access

From: MK_Ultra

Date: Tuesday, October 23, 2007

We are changing the database

1. <database> One or more large structured sets of persistent
	data, usually associated with software to update and {query}
	the data.  A simple database might be a single file containing
	many {records}, each of which contains the same set of
	{fields} where each field is a certain fixed width.

	A database is one component of a {database management system}.

	See also {ANSI/SPARC Architecture}, {atomic}, {blob}, {data
	definition language}, {deductive database}, {distributed
	database}, {fourth generation language}, {functional
	database}, {object-oriented database}, {relational database}.

	{Carol E. Brown's tutorial
	(http://accounting.rutgers.edu/raw/aies/www.bus.orst.edu/faculty/brownc/lectures/db_tutor/db_tutor.htm)}.

	2. <hypertext> A collection of {nodes} managed and stored in
	one place and all accessible via the same {server}.  {Links}
	outside this are "external", and those inside are "internal".

	On the {World-Wide Web} this is called a {website}.

	3. All the facts and rules comprising a {logic programming}
	program.

	(2005-11-17)

our website

<World-Wide Web> (Or "website") Any computer on the
	{Internet} running a {World-Wide Web} {server} process.  A
	particular website is usually identified by the {hostname}
	part of a {URL}.  Multiple hostnames may actually map to the
	same computer in which case they are known as "{virtual
	servers}".

	(2005-07-12)

uses from MS-SQL to MySQL and in the
course of testing

<testing> The process of exercising a product to identify
	differences between expected and actual behaviour.  Typically
	testing is bottom-up: {unit testing} and {integration testing}
	by developers, {system testing} by testers, and {user
	acceptance testing} by users.

	{Test coverage} attempts to assess how complete a test has
	been.

	2. The second stage in a {generate and test} search
	{algorithm}.

	[{Jargon File}]

	(2003-09-24)

it I have found that the following piece of code

<software> Instructions for a computer in some programming
	language, often {machine language}.  The word "code" is often
	used to distinguish instructions from {data} (e.g. "The code
	is marked 'read-only'") whereas "{software}" is used in
	contrast with "{hardware}" and may consist of more than just
	code.

	(2000-04-08)

will give an
error "Incorrect integer

<mathematics> (Or "whole number") One of the {finite} numbers
	in the infinite set

		..., -3, -2, -1, 0, 1, 2, 3, ...

	An {inductive definition} of an integer is a number that is
	either zero or an integer plus or minus one.  An integer is a
	number with no fractional part.  If written as a {fixed-point}
	number, the part after the decimal (or other base) point will
	be zero.

	A {natural number} is a non-negative integer.

	(2002-04-07)

value: for column GraftYear" if the user doesn't type

<theory, programming> (Or "data type") A set of values from
	which a {variable}, {constant}, {function}, or other
	{expression} may take its value.  A type is a classification
	of data that tells the {compiler} or {interpreter} how the
	programmer intends to use it.  For example, the process and
	result of adding two variables differs greatly according to
	whether they are integers, floating point numbers, or strings.

	Types supported by most programming languages include
	{integers} (usually limited to some range so they will fit in
	one {word} of storage), {Booleans}, {floating point numbers},
	and characters.  {Strings} are also common, and are
	represented as {lists} of characters in some languages.

	If s and t are types, then so is s -> t, the type of
	{functions} from s to t; that is, give them a term of type s,
	functions of type s -> t will return a term of type t.

	Some types are {primitive} - built-in to the language, with no
	visible internal structure - e.g. Boolean; others are
	composite - constructed from one or more other types (of
	either kind) - e.g. {lists}, {arrays}, {structures}, {unions}.
	{Object-oriented programming} extends this with {classes}
	which encapsulate both the structure of a type and the
	operations that can be performed on it.

	Some languages provide {strong typing}, others allow {implicit
	type conversion} and/or {explicit type conversion}.

	(2003-12-22)

anything for the graft year value, which is optional.

<CFQUERY name="insertblocks" >
INSERT T_Block (Season,KPIN,BlockName,GraftYear,CreateDtTm)
VALUES (
<CFQUERYPARAM value= "#request.season#" cfsqltype="cf_sql_varchar" maxLength
= "4">,
<CFQUERYPARAM value= "#attributes.kpin#" cfsqltype="cf_sql_integer">,
<CFQUERYPARAM value="#UCase(Trim(blockdetails[i][1]))#"
cfsqltype="cf_sql_varchar" maxLength = "30">,
<CFQUERYPARAM value="#blockdetails[i][5]#" cfsqltype="cf_sql_varchar"
maxLength = "4">,
#CreateODBCDateTime(Now())#)
</CFQUERY>

It works fine with MS-SQL, which inserts the value 0 when passed an empty
string

<programming> A sequence of {data} values, usually {bytes},
	which usually stand for {characters} (a "character string").
	The {mapping} between values and characters is determined by
	the {character set} which is itself specified implcitly or
	explicitly by the environment in which the string is being
	interpreted.

	The most common character set is {ASCII} but, since the late
	1990s, there has been increased interest in larger character
	sets such as {Unicode} where each character is represented by
	more than eight {bits}.

	Most programming languages consider strings (e.g.
	"124:shabooya:\n", "hello world") basically distinct from
	numbers which are typically stored in fixed-length {binary} or
	{floating-point} representation.

	A {bit string} is a sequence of {bits}.

	(1999-12-21)

, which is what I think is happening here in this case. When I do a
manual insertion on the table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

with an empty string then MySQL stores a zero the
same as MS. I know that I'll have similar problems with other parts of the
site so I am wondering if there is a reason why this is happening. The table is
set to allow null

<programming> A special value used in several languages to
	represent the thing referred to by an uninitialised pointer.

	<database> A special value that may be stored in some database
	columns to represent an unknown, missing, not applicable, or
	undefined value.  Nulls are treated completely differently
	from ordinary values when evaluating SQL expressions and there
	are several SQL constructs for dealing with nulls.

	(2003-06-17)

values in this field, and the default value is NULL. Should I
set the default value to zero maybe?

Any help much appreciated...

From: cf_dev2

Date: Wednesday, October 24, 2007

Incorrect integer value

For one thing, if GraftYear is an "integer" the cfsqltype should not be
cf_sql_integer not "cf_sql_varchar". Similar comment

<programming> (Or "remark") Explanatory text embedded in
	program {source} (or less often data) intended to help human
	readers understand it.

	Code completely without comments is often hard to read, but
	too heavily commented code isn't much better, especially if
	the comments are not kept up-to-date with changes to the code.
	Too much commenting may mean that the code is
	over-complicated.  A good rule is to comment everything that
	needs it but write code that doesn't need much of it.

	A particularly irksome form of over-commenting explains
	exactly what each statement does, even when it is obvious to
	any reasonably competant programmer, e.g.

		/* Open the input file */
		infd = open(input_file, O_RDONLY);

	(1998-04-28)

about the CreateDtTm
column cfsqltype. Should not it be cf_sql_date, cf_sql_timestamp, etc?

If you want to insert a 0 when the variable

<programming> (Sometimes "var" /veir/ or /var/) A named memory
	location in which a program can store intermediate results and
	from which it can read it them.  Each {programming language}
	has different rules about how variables can be named, typed,
	and used.  Typically, a value is "assigned" to a variable in
	an {assignment} statement.  The value is obtained by
	evaluating an expression and then stored in the variable.  For
	example, the assignment

		x = y + 1

	means "add one to y and store the result in x".  This may look
	like a mathematical equation but the mathematical equality is
	only true in the program until the value of x or y changes.
	Furthermore, statements like

		x = x + 1

	are common.  This means "add one to x", which only makes sense
	as a state changing operation, not as a mathematical equality.

	The simplest form of variable corresponds to a single-{word}
	of {memory} or a {CPU} {register} and an assignment to a
	{load} or {store} {machine code} operation.

	A variable is usually defined to have a {type}, which never
	changes, and which defines the set of values the variable can
	hold.  A type may specify a single ("atomic") value or a
	collection ("aggregate") of values of the same or different
	types.  A common aggregate type is the {array} - a set of
	values, one of which can be selected by supplying a numerical
	{index}.

	Languages may be {untyped}, {weakly typed}, {strongly typed},
	or some combination.  {Object-oriented programming} languages
	extend this to {object} types or {classes}.

	A variable's {scope} is the region of the program source
	within which it represents a certain thing.  Scoping rules are
	also highly language dependent but most serious languages
	support both {local variables} and {global variables}.
	{Subroutine} and {function} {formal arguments} are special
	variables which are set automatically by the language runtime
	on entry to the subroutine.

	In a {functional programming} language, a variable's value
	never changes and change of state is handled as recursion over
	lists of values.

	(2004-11-16)

is an empty string, you can use
the ColdFusion

<World-Wide Web, database, tool> {Allaire Corporation}'s
	commercial {database} application development tool that allows
	{databases} to have a {World-Wide Web} {interface}, so a
	database can be queried and updated using a {web browser}.

	The ColdFusion Server application runs on the {web server} and
	has access to a {database}.  ColdFusion files on the web
	server are {HTML} pages with additional ColdFusion commands to
	{query} or {update} the database, written in {CFML}.  When the
	page is requested by the user, the {web server} passes the
	page to the Cold Fusion application, which executes the {CFML}
	commands, places the results of the {CFML} commands in the
	{HTML} file, and returns the page to the {web server}.  The
	page returned to the {web server} is now an ordinary {HTML}
	file, and it is sent to the user.

	Examples of ColdFusion applications include order entry, event
	registration, catalogue search, directories, calendars, and
	interactive training.  ColdFusion applications are robust
	because all database interactions are encapsulated in a single
	industrial-strength {CGI} script.  The formatting and
	presentation can be modified and revised at any time (as
	opposed to having to edit and recompile {source code}).

	ColdFusion Server can connect with any database that supports
	{ODBC} or {OLE DB} or one that has a native database driver.
	Native database drivers are available for {Oracle} and
	{Sybase} databases.

	ColdFusion is available for {Windows}, {Solaris}, and {HP-UX}.
	A {development environment} for creating ColdFusion files,
	called ColdFusion Studio, is also available for {Windows}.

	Current version: MX(6), as of 2003-07-11.

	The {filename extension} for ColdFusion files is .cfm

	{Home (http://www.coldfusion.com/)}.

	(2003-07-27)

val() function. It converts non-numeric values to 0. If you want to
insert a NULL when the value entered isn't numeric try using a conditional
test within the "null" attribute.

<CFQUERYPARAM value="#theVariable#" cfsqltype="cf_sql_integer" null="#not
IsNumeric(theVariable)#">

<CFQUERYPARAM

From: cf_dev2

Date: Wednesday, October 24, 2007

should not be cf_sql_integer

Argh. The CFSQLTYPE [i]should[/i] be cf_sql_integer.

From: MK_Ultra

Date: Wednesday, October 24, 2007

I changed it to the following:

<CFQUERYPARAM value="#Val(blockdetails[i][5])#" cfsqltype="cf_sql_integer"
maxLength = "4">

and it works the same as with MS-SQL, which didn't mind that CFSQLTYPE was
incorrectly set to varchar. I had to use Val, otherwise it still gave an error.

Thanks!

From: GArlington

Date: Wednesday, October 24, 2007

wrote in message:

I changed it to the following:
<CFQUERYPARAM value="#Val(blockdetails[i][5])#" cfsqltype="cf_sql_integer"
maxLength = "4">
and it works the same as with MS-SQL, which didn't mind that CFSQLTYPE was
incorrectly set to varchar. I had to use Val, otherwise it still gave an error.
Thanks!

Please, read ColdFusion docs on <CFQUERYPARAM ...> nd pay attention to "null"
attribute.

Next Message: Problem accessing MS Access database

Blogs related to MySQL Null Field

How to cite a web site - Transaction essentials } finally ...
... Session session1 = null; Session session2 = null; try { utx.begin(); session1 = auctionDatabase.openSession(); session2 = billingDatabase.openSession(); concludeAuction(session1); billAuction(session2); session1.flush(); ...

CHAPTER 10 Transactions and concurrency utx.rollback(); } catch
With these two settings enabled the code can be simplified to the following: UserTransaction utx = (UserTransaction) new InitialContext() .lookup(”java:comp/UserTransaction”); Session session1 = null; Session session2 = null; ...

Web site management - Transaction essentials 10.1.3 Transactions ...
Here it is again with exception handling: EntityManager em = null; EntityTransaction tx = null; try { em = emf.createEntityManager(); tx = em.getTransaction(); tx.begin(); concludeAuction(em); tx.commit(); } catch (RuntimeException ex) ...

Web hosting domains - CHAPTER 10 Transactions and concurrency ...
... you need to call the JTA UserTransaction interface to control transaction boundaries programmatically: UserTransaction utx = (UserTransaction) new InitialContext() .lookup(”java:comp/UserTransaction”); EntityManager em = null; ...

mysql web applications
... mysql where datetime mysql where field mysql where id mysql where id in mysql where in mysql where in array mysql where in clause mysql where in select mysql where in syntax mysql where is not mysql where is not null mysql where ...

Re: Trouble storing monetary values in MySQL 5
Here's a column def I use with MySQL 5: > > hourlyRate decimal(10,2) not null > > That says use 2 fractional digits and 8 (10 - 2) integral digits. > > cheers, > barneyb > > On 9/30/07, Rick Faircloth wrote: > > Hi, all. ...