date:20060315

Re: insert my_ulonglong data with C API

2006-03-15 Thread 古雷

Thanks a lot!
But my test is not successful. Please help me.
This is my test code:

#ifdef WIN32
#include windows.h
#endif

#include mysql.h
#include stdio.h
main()
{
 union ull {
  unsigned char a[8];
  my_ulonglong id;
 } ull;
 for(int i=0;i8;i++) ull.a[i]=(unsigned char)255;
 
 char s[200];
 sprintf(s,%llu\n,ull.id);
 printf(%s\n,s);

 return 0;
}

On Windows its output is:
4294967295
It's still a 4bytes integer.

Regards,
Gu Lei
- Original Message - 
From: Dan Nelson [EMAIL PROTECTED]
To:  [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 15, 2006 3:34 PM
Subject: Re: insert my_ulonglong data with C API


 In the last episode (Mar 15),  said:
 Hello,everyone!
 
 My table has a bigint column which needs store 8bytes integer.
 
 I looked up in Mysql Menual. It seems that , if I use C API I can
 only use preapared statement functions to insert bigint values.

 I can not use mysql_real_query or mysql_query to insert bigint values
 because I do not know how to convert a my_ulonglong to a string or
 character array.
 
 sprintf(buf, insert into mytable values ( %llu ), myulonglongint);
 
 -- 
 Dan Nelson
 [EMAIL PROTECTED]
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert my_ulonglong data with C API

2006-03-15 Thread Pooly

Hi,


2006/3/15, 古雷 [EMAIL PROTECTED]:
 Thanks a lot!
 But my test is not successful. Please help me.
 This is my test code:

 #ifdef WIN32
 #include windows.h
 #endif

 #include mysql.h
 #include stdio.h
 main()
 {
  union ull {
   unsigned char a[8];
   my_ulonglong id;
  } ull;
  for(int i=0;i8;i++) ull.a[i]=(unsigned char)255;

  char s[200];
  sprintf(s,%llu\n,ull.id);
  printf(%s\n,s);

  return 0;
 }

 On Windows its output is:
 4294967295
 It's still a 4bytes integer.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_crt_format_specification_fields_.2d_.printf_and_wprintf_functions.asp
try :
sprintf(s,%I64d\n,ull.id);


--
Pooly
Webzine Rock : http://www.w-fenec.org/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: migrating 4.0 to 4.1 character set problems

2006-03-15 Thread Dusan Pavlica


Hi Sean,
I used to have similar problems with different character sets and my 
solution is:

1) dump all data using mysql dump utility
2) check if data are OK - view your data with viewer which suports correct 
character set

3) if data are not OK try to dump data in different character set
4) you must be sure that data are dumped OK and you know their character set
5) you can convert your dumped data manualy using some conversion program or 
you can let MySQL server do it
6) if you did conversion of dumped file you must also change SET NAMES 'new 
character set' command
7) setup correct default character set for your MySQL server or change or 
add character sets to your definitions of tables and databases in dumped 
file

8) load data

HTH

Dusan

- Original Message - 
From: Sean O'Hara [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, March 14, 2006 5:09 PM
Subject: migrating 4.0 to 4.1 character set problems



Hi,

I am having character set problems while trying to migrate my data  from a 
server running 4.0.25-standard to a server running 4.1.16. I  believe that 
the orginal database was using the latin1 character set  (not sure, is 
there any way to tell? show full column doesn't seem to  be supported in 
4.0.x) and the default character set for the 4.1  server is latin1 as 
well. However when I try to import my data from a  mysqldump from the 
older server all accents are turned into question  marks.


I've searched the forums and mailing lists extensively for clues as  to 
how to resolve this issue. The most helpful post I found was here: 
http://lists.mysql.com/mysql/186657


However, even following the instructions on this post I ran into 
problems. Rather than turniing accents into question marks, any data  with 
accent was truncated at the first instance of such a character  (e.g. 
López becomes L). So that is not working either.


Incidentally, I would be happy to convert the data to utf8 while 
upgrading to 4.1 but mostly I just want to get rid of the question  mark 
characters. Any ideas?


Thanks,
Sean



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very large from

2006-03-15 Thread Gabriel PREDA

Hmmm...
Let me say some thoughts...
First *fbsd_user* said that he has *100 input fields plus 40 different drop
downs.*
And everybody is arguing that they prefer *one single insert*.

Is it just me... or are you thinking at a table with 140 columns ?
Thinking at such a monster... all the above discussion is ok !

But who does a table with 140 columns...
It's not good practice... it's no good at all...

If we're not working with a monster like that... all discusion falls down...
On the other hand if I have to insert all that info... in let's say... I
don't know... 10 tables... what's the point of using *one single insert*
how ca one use a one single insert to put data in 10 tables ?

So... the design of the application follows in at least 50% of the cases the
design of the DATABASE !

Give us a little more details about your database !

--
Gabriel PREDA
Senior Web Developer

Mail Delivery System

2006-03-15 Thread beadandsilver

The message cannot be represented in 7-bit ASCII encoding and has been sent as 
a binary attachment.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql.sock gone

2006-03-15 Thread Косов Евгений

Look at mysql's error log ($MYSQL_DATA_DIR/$HOSTNAME.err). Do you see 
anything strange there?



Anton Krall wrote:

No crons that would delete the tmp directory.. In fact, all the other files
stay there... Just mysql.sock goes away...  


|-Original Message-
|From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
|Sent: Tuesday, March 14, 2006 3:15 PM

|To: Anton Krall; mysql@lists.mysql.com
|Subject: RE: mysql.sock gone
|
|Do you have any cron jobs that clear the /tmp directory during the day?
|
|Regards
|
|---
|** _/ **  David Logan 
|***   _/ ***  ITO Delivery Specialist - Database

|*_/*  Hewlett-Packard Australia Ltd
|_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
|   _/  _/  _/  _/     Desk:   +618 8408 4273
|  _/  _/  _/_/_/  Mobile: 0417 268 665
|*_/   **
|**  _/    Postal: 148 Frome Street,

|   _/ **  Adelaide SA 5001
|  Australia 
|invent   
|---

|
|-Original Message-
|From: Anton Krall [mailto:[EMAIL PROTECTED]
|Sent: Wednesday, 15 March 2006 7:38 AM
|To: mysql@lists.mysql.com
|Subject: mysql.sock gone
|
|Guys.
|
|I just started having a problem, Im running
|mysql-standard-4.1.12-pc-linux-gnu-i686 binaries under Fedora 
|Core 3 and Ive been having problem where during some parts of 
|the day, /tmp/mysql.oskc goes away, I can still see mysql 
|running when I do a ps ax but when I try to connect to it, it 
|says it cant connect thru mysql.sock

|
|What could be the cause that would make mysql.sock dissapear?
|
|Thx for any help you can provide
|
|
|--
|MySQL General Mailing List
|For list archives: http://lists.mysql.com/mysql
|To unsubscribe:
|http://lists.mysql.com/[EMAIL PROTECTED]

|
|
|-- 
|MySQL General Mailing List

|For list archives: http://lists.mysql.com/mysql
|To unsubscribe:
|http://lists.mysql.com/[EMAIL PROTECTED]

|
|
|




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very large from

2006-03-15 Thread Dominik Klein

You could also use a temp table, put data into it page-by-page and 
insert the complete row after a last check into the real table.


This temp table might have an additional timestamp field according to 
which evth. older than 1h(or some other time period) could easily be 
deleted by a cronjob.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to build a client?

2006-03-15 Thread Michael Peters

I get various errors from my php based applications and php building that 
all refer to the need to upgrade my mysqlclient. I have not found 
instructions on where to get the client software to build or upgrade a 
client.

This system is a Solaris 10 SPARC system. 

Best regards,

Michael

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain CONFIDENTIAL material.  If you receive this 
material/information in error, please contact the sender and delete or destroy 
the material/information.

Re: How to build a client?

2006-03-15 Thread Dominik Klein

I get various errors from my php based applications and php building that 
all refer to the need to upgrade my mysqlclient. 


Did you try old-passwords in /etc/my.cnf ?
Old clients use an old authentication protocol, which is not the default 
on modern mysql servers. For compatibility, old-passwords uses the old 
algorithm.


I have not found 
instructions on where to get the client software to build or upgrade a 
client.


For php:
./configure options --with-mysql=/path/to/newmysql/

Regards,
Dominik

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to build a client?

2006-03-15 Thread Dominik Klein


[addon to my previous mail]

 I have not found 
instructions on where to get the client software to build or upgrade a 
client.


just use an up-to-date binary mysql distribution for your OS.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to build a client?

2006-03-15 Thread Michael Peters

I have mysql-max-5.0.19-solaris10-sparc-64bit installed and running fine. 
I have at times used the OLD PASSWORD ro resolve some issues but during my 
build of php-5.1.2, no luck.

Here is the last part of the config.log with the errors.

config.log 639 lines, 31250 characters
ld: warning: file /usr/local/lib/mysql/libmysqlclient.a(client.o): wrong 
ELF class: ELFCLASS64
ld: warning: file /usr/local/lib/mysql/libz.a(crc32.o): wrong ELF class: 
ELFCLASS64
Undefined   first referenced
 symbol in file
mysql_error /var/tmp//ccO3Lkgh.o
ld: fatal: Symbol referencing errors. No output written to conftest
collect2: ld returned 1 exit status
configure: failed program was:
#line 59430 configure
#include confdefs.h
/* Override any gcc2 internal prototype to avoid an error.  */
/* We use char because int might match the return type of a gcc2
builtin and then its argument prototype would still apply.  */
char mysql_error();

int main() {
mysql_error()
; return 0; }


Best regards,

Michael 




Dominik Klein [EMAIL PROTECTED] 
03/15/2006 09:34 AM
Please respond to
[EMAIL PROTECTED]


To
mysql@lists.mysql.com
cc

Subject
Re: How to build a client?






[addon to my previous mail]

  I have not found 
 instructions on where to get the client software to build or upgrade a 
 client.

just use an up-to-date binary mysql distribution for your OS.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




The information transmitted is intended only for the person or entity to which 
it is addressed and may contain CONFIDENTIAL material.  If you receive this 
material/information in error, please contact the sender and delete or destroy 
the material/information.

Re: How to build a client?

2006-03-15 Thread Michael Peters

My /etc/my.cnf does not have this option? Is this something I can add? 
What is the correct syntax?

Best regards,

Michael 




Dominik Klein [EMAIL PROTECTED] 
03/15/2006 09:33 AM
Please respond to
[EMAIL PROTECTED]


To
mysql@lists.mysql.com
cc

Subject
Re: How to build a client?






 I get various errors from my php based applications and php building 
that 
 all refer to the need to upgrade my mysqlclient. 

Did you try old-passwords in /etc/my.cnf ?
Old clients use an old authentication protocol, which is not the default 
on modern mysql servers. For compatibility, old-passwords uses the old 
algorithm.

 I have not found 
 instructions on where to get the client software to build or upgrade a 
 client.

For php:
./configure options --with-mysql=/path/to/newmysql/

Regards,
Dominik

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




The information transmitted is intended only for the person or entity to which 
it is addressed and may contain CONFIDENTIAL material.  If you receive this 
material/information in error, please contact the sender and delete or destroy 
the material/information.

Re: Accountability with MySQL

2006-03-15 Thread Rhino

- Original Message - 
From: Martijn Tonies [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Wednesday, March 15, 2006 2:53 AM
Subject: Re: Accountability with MySQL

  Basics of database design: store what you know.

  Given that NULLs basically means the absence of a value
  (eg: unknown), you shouldn't be storing NULLs.

 Nonsense!!

 That's a bold statement ...

 That's simply wrong. A null means unknown or not applicable and is a

 Ah, so now things become clear, a NULL actually can mean
 two things? So much for clarity then...

Come on; that's not fair. Unknown and not applicable are more like
different senses of the same thing, not two opposite things.

Read the literature on how to design databases. What you do
is storing true propositions. That is, each attribute defines a
certain true proposition. For example:

Employee Martijn has Employee# 14.

You should be able to derive these sentences from every row.
Putting a NULL or N/A in there fails to meet this requirement.

Perhaps my theory is a bit rusty but I have never heard of this true 
propositions business before. Do you have a citation where I can read about 
this?

Frankly, I'm skeptical about your interpretation of this idea. While you 
clearly don't want false information in a database, it isn't false to say 
that student such-and-such's exam mark or such-and-such an employee's 
termination date is unknown or not applicable. It _would_ be false to store 
a grade of 0 for a student who didn't take the exam because it implies that 
he got every question wrong when in fact he didn't write the test at all; 
that would be an example of 0 having two contradicatory meanings. Given a 
phobia about nulls, it is a reasonable design to put students who don't take 
the test in a separate table but it's not the ONLY way to handle the 
situation: a null to indicate a student who didn't take the test is 
perfectly reasonable too.

 perfectly valid value to use in many, many situations. Two classic
 examples:

 Let me first state that there's a difference between storing NULLs
 and handling NULLs in your result.

Yes, of  course there is: so what? You said NULLs should never be

_stored_;
that's what I'm responding to. Using NULLs is not very _difficult_ 
anyway,

just a bit tedious because it is another case to handle.

There's no point in storing what you don't know.

But you _do_ know something: that the student didn't take the exam. That is 
a piece of true knowledge all by itself.

 So, let's debunk these two classic examples...

Please note that I did not say that you MUST use NULLs, just that they

were

perfectly valid to use in a design.

Depends on who you're asking ...

A few years ago, I was working at a company that had developed
an application with an Oracle database with about 400 tables, nothing
too large.

Plenty of tables had NULLs, not because of missing business data,
but rather used internally in the system.

I'm not sure what you mean by that phrase used internally in the system.

The number of problems we had with them, I cannot count on my
two hands, nor can I count them on the hands of all other developers
(5) for that project.

How about enumerating a few of them here? I've dealt with relational 
databases in several capacities for over 20 years and I've never found nulls 
to be a big problem.

Just last week, I spoke to a guy who used to be my collegue there
and we discussed the design a bit (he still does database design and
development for a large company, on Sybase with over 4000
deployed systems) and we both agreed that using NULLs in most
cases as part of the initial design was causing us more problems
then helping us.

Again, how about listing some of these problems?

 1. You have a table containing test scores. Some students get every
 question
 on a given test wrong. Some students don't write the test at all,

perhaps

 because they were sick. In this scenario, I think the correct thing to
 store
 is 0 for the students who got every question wrong and NULL for the
 students
 who didn't write the test.

 IMO, the better thing to do in this particular case is to NOT store a

test

 result for the students that did not make the test.

Okay, that might be acceptable, if it doesn't cause you to lose track of

the

student altogether. But if this table was the only one that even recorded
the _existence_ of the student, you'd have a problem;

Indeed, then I would have a problem, cause you cannot derive from
the test results table that a student exists. This is a rather silly
statement.
If the test results table should ALSO store the existence of a student,
you're design is wrong :-)

if someone tried to
verify that the student had attended this school, you wouldn't know that
they had.

In your own words: nonsense.

Each table should store what is was designed to store. If I would
have to know if a student was enlisted in a certain course or would
be attending this school, I would not be using

Re: insert my_ulonglong data with C API

2006-03-15 Thread Dan Nelson

In the last episode (Mar 15), ?? said:
 Thanks a lot! But my test is not successful. Please help me. This is
 my test code:
 
 #ifdef WIN32
 #include windows.h
 #endif
 
 #include mysql.h
 #include stdio.h
 main()
 {
  union ull {
   unsigned char a[8];
   my_ulonglong id;
  } ull;
  for(int i=0;i8;i++) ull.a[i]=(unsigned char)255;
  
  char s[200];
  sprintf(s,%llu\n,ull.id);
  printf(%s\n,s);
 
  return 0;
 }
 
 On Windows its output is:
 4294967295
 It's still a 4bytes integer.

Maybe your compiler doesn't understand the %llu syntax.  If it's a
posix-compatible compiler, try this (although if it was posix, %llu
would have worked, so this probably won't either):

  #include inttypes.h

  ...

  sprintf(s,%PRIu64\n,ull.id);

Or read your compiler documentation to verify that can print 64-bit
integers at all.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Accountability with MySQL

2006-03-15 Thread Martijn Tonies

Hello,

(please read all before replying)

Basics of database design: store what you know.

Given that NULLs basically means the absence of a value
(eg: unknown), you shouldn't be storing NULLs.

Nonsense!!

That's a bold statement ...

That's simply wrong. A null means unknown or not applicable and is
a

Ah, so now things become clear, a NULL actually can mean
two things? So much for clarity then...

Come on; that's not fair. Unknown and not applicable are more like
different senses of the same thing, not two opposite things.

Read the literature on how to design databases. What you do
is storing true propositions. That is, each attribute defines a
certain true proposition. For example:

Employee Martijn has Employee# 14.

You should be able to derive these sentences from every row.
Putting a NULL or N/A in there fails to meet this requirement.

Perhaps my theory is a bit rusty but I have never heard of this true
propositions business before. Do you have a citation where I can read
about
this?

I would certainly recommend this one:
http://www.amazon.com/exec/obidos/ASIN/0321197844/databasede095-20/103-0880452-0854264?creative=327641camp=14573adid=0VQ1KDVJ56MV7389RPEQlink_code=as1

Frankly, I'm skeptical about your interpretation of this idea. While you
clearly don't want false information in a database, it isn't false to say
that student such-and-such's exam mark or such-and-such an employee's
termination date is unknown or not applicable. It _would_ be false to
store
a grade of 0 for a student who didn't take the exam because it implies
that
he got every question wrong when in fact he didn't write the test at all;

Agreed.

that would be an example of 0 having two contradicatory meanings. Given a
phobia about nulls, it is a reasonable design to put students who don't
take
the test in a separate table but it's not the ONLY way to handle the
situation: a null to indicate a student who didn't take the test is
perfectly reasonable too.

Disagreed. Let me explain further:
In this case, a null means (according to you and in your design)
that the student did not take the test.

It could also mean that we lots it's grade for now, and that we
want to fill it in later, as in: unknown, which is what NULL also
means (your own words).

In another case, null would be the employee isn't terminated yet,
but could be in the future.

That's the problems with nulls - there's no actual defined
meaning. You can never say:

This table holds test results, a row will mean:
Student with StudentID 123 made test #12 and scored a 4

Cause you can have rows that can mean this:
Student with StudentID 123 made test #12 and scored a
yet unknown score

Student with StudentID 123 made test #12 and did not make
the test

or
Student with StudentID 123 and test #12 doesn't apply to him

In short: NULL is ambigious.

perfectly valid value to use in many, many situations. Two classic
examples:

Let me first state that there's a difference between storing NULLs
and handling NULLs in your result.

Yes, of course there is: so what? You said NULLs should never be
_stored_;
that's what I'm responding to. Using NULLs is not very _difficult_
anyway,
just a bit tedious because it is another case to handle.

There's no point in storing what you don't know.

But you _do_ know something: that the student didn't take the exam. That
is
a piece of true knowledge all by itself.

But that's not what the rows in the table would mean - see above.
In the relational model, the idea is to store true propositions - that is,
each row means the same thing, but with different attributes. See above.

So, let's debunk these two classic examples...

Please note that I did not say that you MUST use NULLs, just that they
were
perfectly valid to use in a design.

Depends on who you're asking ...

A few years ago, I was working at a company that had developed
an application with an Oracle database with about 400 tables, nothing
too large.

Plenty of tables had NULLs, not because of missing business data,
but rather used internally in the system.

I'm not sure what you mean by that phrase used internally in the system.

Missing business data, eg: no middle name. Some people would
put in a NULL in there as well, while NULL can also mean: I do
not KNOW this persons middle name. Ambiguity, see above.

The number of problems we had with them, I cannot count on my
two hands, nor can I count them on the hands of all other developers
(5) for that project.

How about enumerating a few of them here? I've dealt with relational
databases in several capacities for over 20 years and I've never found
nulls
to be a big problem.

Not big, but a problem... :-)

Do you often go around looking for (little) problems while you
can easily avoid them?

Just last week, I spoke to a guy who used to be my collegue there
and we discussed the design

Re: How to build a client?

2006-03-15 Thread Pure Web Solution

you can add --old-password when starting MySQL manually or
try adding the following to the my.cnf file

[mysqld]
old-password

regards

Pure Web Solution
http://www.purewebsolution.co.uk
PHP, MYSQL, Web Design  Web Services

My /etc/my.cnf does not have this option? Is this something I can add? 
What is the correct syntax?

Best regards,

Michael 




Dominik Klein [EMAIL PROTECTED] 
03/15/2006 09:33 AM
Please respond to
[EMAIL PROTECTED]


To
mysql@lists.mysql.com
cc

Subject
Re: How to build a client?







 I get various errors from my php based applications and php building 

that 

 all refer to the need to upgrade my mysqlclient. 


Did you try old-passwords in /etc/my.cnf ?
Old clients use an old authentication protocol, which is not the default 
on modern mysql servers. For compatibility, old-passwords uses the old 
algorithm.


 I have not found 
 instructions on where to get the client software to build or upgrade a 
 client.


For php:
./configure options --with-mysql=/path/to/newmysql/

Regards,
Dominik

-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The
information transmitted is intended only for the person or entity to which it
is addressed and may contain CONFIDENTIAL material. If you receive this
material/information in error, please contact the sender and delete or destroy
the material/information. 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very large from

2006-03-15 Thread fbsd_user

Thank you Gabriel, more background information may result in a
better concept, so here it is.

The web based application I am writing deals with people who have an
a home for rent. They can sign up for small fee and enter their
rental property into the application so its searchable and viewable
by the internet public. I have a membership DB with single members
table containing 20 columns which holds the users signup info.
Members can list more than one rental property. Members has
opportunity to enter some portion of the 140 columns of data to
describe the rental property. Currently the real_estate DB contains
a single property table of 140 columns.

I have grouped the info in the table into 3 groups, mandatory info
deals with the business of renting, second group deals with
location,  third group deals with house characteristics. All the 140
columns of data will be shown on the detail property screen. Fields
that don't pertain to that individual property will show up as blank
because the user did not enter data. All fields are 'varchar' to
conserve on unused space.

One single record per listed property has all the info retrievable
by one read, but has undesirable effects entering all of it from
single form.  Making 3 tables would mean duplication of some common
key fields to allow retrieving all the associated rows to combine
the data for displaying.

I would think since most of the activity will be people on the
internet searching for rental property, performance would be better
to retrieve all the info with a single read. This makes the 3 table
idea a bad performer.

It has been suggested to use session control as a staging function
where the forms store the entered data so a single insert of all the
data can be made to the table. Sessions store their data in standard
flat files in /tmp. Flat files are not known for their access speed
and performance, with 2 or 3 fields this is ok but 100+ fields and
this may become a major performance bottleneck.

My current thinking is to break the entering of the property info
into 3 separate forms, the first with the mandatory info does a
insert to create the row with the remaining 100 columns being seeded
with $t=''. Group 2  3 will them be a update to the row using the
'set' keyword just touching the columns under their separate
control.


Does this sound like a workable solution or is there some other
approach that is better suited to my application?

Thanks to all the people who have replied all ready.



-Original Message-
From: Gabriel PREDA [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 15, 2006 5:01 AM
To: Mysql
Subject: Re: Very large from


Hmmm...
Let me say some thoughts...
First *fbsd_user* said that he has *100 input fields plus 40
different drop
downs.*
And everybody is arguing that they prefer *one single insert*.

Is it just me... or are you thinking at a table with 140 columns ?
Thinking at such a monster... all the above discussion is ok !

But who does a table with 140 columns...
It's not good practice... it's no good at all...

If we're not working with a monster like that... all discusion falls
down...
On the other hand if I have to insert all that info... in let's
say... I
don't know... 10 tables... what's the point of using *one single
insert*
how ca one use a one single insert to put data in 10 tables ?

So... the design of the application follows in at least 50% of the
cases the
design of the DATABASE !

Give us a little more details about your database !

--
Gabriel PREDA
Senior Web Developer


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very large from

2006-03-15 Thread fbsd_user

A power bar is what they call the bar at the bottom and on the right
side of the browsers window when the content being showed in the
window is larger than the window. By sliding the bars around you
bring the content into view.

-Original Message-
From: James Harvard [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 14, 2006 6:39 PM
To: Mysql
Subject: Re: Very large from

At 4:24 pm -0500 14/3/06, fbsd_user wrote:
user has to use the power bar to move deeper into the form

Out of curiosity, what's the power bar?

From a db perspective one single insert is preferable, IMO. Firstly
you don't have to declare as NULL required fields that will be
entered in the second, third or Nth form page. Secondly you won't
get incomplete entries, as has already been mentioned.

If it was a sign-up form, say, and you have a unique index on the
user e-mail address, then you would run into problems if a user
completed the first page of the signup and created a new row in the
table, then for some reason started from scratch (e.g. their 'puter
crashed). They wouldn't be able to start again because their e-mail
address would already be in the unique field in the table.

I would do one of two things:

a) use a session management system to store the submitted details
until you get to the final form page and can insert the whole lot
into the table

b) Have the whole form on one page and use Javascript to only show
one part of the form at any one time (but in such a way that it
degrades gracefully for non-JS browsers - i.e. they get the whole
form on one page)

James Harvard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Question about interactive timeout

2006-03-15 Thread Mechain Marc

I have a Mysql Server (4.1.8) where some sessions stay connected for a
value greater than Interactive timeout value.

 

Here is an abstract of the show processlist command:

 

| 129996 | fret | mtt04.back:33598   | fret | Sleep   |   61756 |

| 129998 | fret | mtt04.back:33599   | fret | Sleep   |   61759 |

| 12 | ets | mtt04.back:33600   | ets | Sleep   |   61759 |

| 13 | ets | mtt04.back:33601   | ets | Sleep   |   61759 |

| 130001 | tls  | mtt04.back:33602   | tls  | Sleep   |   61755 |

 

The show variables command gives me:

Interactive_timeout 28800

Wait_timeout 28800

 

Why those connections do still remains on the server with a value of
61700s while in a Sleep Command?

It is rather strange for me; they normally should have disappeared after
28800s of inactivity.

 

Could you give me a clue?

 

Marc.

Re: Very large from

2006-03-15 Thread Pure Web Solution


under the particular confinements you have set yourself, i would use the
session id to create a temporary table that you can update when each stage of
the form is complete.  At the end you can present the user with all the data
they have entered so far and give them the option of editing or confirming
their input.  

Then, obviously you can copy the contents of the temporary sessionid table
into your main one and drop the temporary.

Still do not think i would use a table with 140 columns though, think about
future use/how your project may develop before committing yourself.

Good luck

Pure Web Solution
http://www.purewebsolution.co.uk
PHP, MYSQL, Web Design  Web Services

fbsd_user [EMAIL PROTECTED] wrote:

 Thank you Gabriel, more background information may result in a
 better concept, so here it is.
 
 The web based application I am writing deals with people who have an
 a home for rent. They can sign up for small fee and enter their
 rental property into the application so its searchable and viewable
 by the internet public. I have a membership DB with single members
 table containing 20 columns which holds the users signup info.
 Members can list more than one rental property. Members has
 opportunity to enter some portion of the 140 columns of data to
 describe the rental property. Currently the real_estate DB contains
 a single property table of 140 columns.
 
 I have grouped the info in the table into 3 groups, mandatory info
 deals with the business of renting, second group deals with
 location,  third group deals with house characteristics. All the 140
 columns of data will be shown on the detail property screen. Fields
 that don't pertain to that individual property will show up as blank
 because the user did not enter data. All fields are 'varchar' to
 conserve on unused space.
 
 One single record per listed property has all the info retrievable
 by one read, but has undesirable effects entering all of it from
 single form.  Making 3 tables would mean duplication of some common
 key fields to allow retrieving all the associated rows to combine
 the data for displaying.
 
 I would think since most of the activity will be people on the
 internet searching for rental property, performance would be better
 to retrieve all the info with a single read. This makes the 3 table
 idea a bad performer.
 
 It has been suggested to use session control as a staging function
 where the forms store the entered data so a single insert of all the
 data can be made to the table. Sessions store their data in standard
 flat files in /tmp. Flat files are not known for their access speed
 and performance, with 2 or 3 fields this is ok but 100+ fields and
 this may become a major performance bottleneck.
 
 My current thinking is to break the entering of the property info
 into 3 separate forms, the first with the mandatory info does a
 insert to create the row with the remaining 100 columns being seeded
 with $t=''. Group 2  3 will them be a update to the row using the
 'set' keyword just touching the columns under their separate
 control.
 
 
 Does this sound like a workable solution or is there some other
 approach that is better suited to my application?
 
 Thanks to all the people who have replied all ready.
 
 
 
 -Original Message-
 From: Gabriel PREDA [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, March 15, 2006 5:01 AM
 To: Mysql
 Subject: Re: Very large from
 
 
 Hmmm...
 Let me say some thoughts...
 First *fbsd_user* said that he has *100 input fields plus 40
 different drop
 downs.*
 And everybody is arguing that they prefer *one single insert*.
 
 Is it just me... or are you thinking at a table with 140 columns ?
 Thinking at such a monster... all the above discussion is ok !
 
 But who does a table with 140 columns...
 It's not good practice... it's no good at all...
 
 If we're not working with a monster like that... all discusion falls
 down...
 On the other hand if I have to insert all that info... in let's
 say... I
 don't know... 10 tables... what's the point of using *one single
 insert*
 how ca one use a one single insert to put data in 10 tables ?
 
 So... the design of the application follows in at least 50% of the
 cases the
 design of the DATABASE !
 
 Give us a little more details about your database !
 
 --
 Gabriel PREDA
 Senior Web Developer
 






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Very large from

2006-03-15 Thread Ryan Stille

 My current thinking is to break the entering of the property
 info into 3 separate forms, the first with the mandatory info
 does a insert to create the row with the remaining 100
 columns being seeded with $t=''. Group 2  3 will them be a
 update to the row using the 'set' keyword just touching the
 columns under their separate control.
 
 
 Does this sound like a workable solution or is there some
 other approach that is better suited to my application?
 

If you go this route I would set some kind of 'finalized' flag when the
user completes the third group (even if they left everything in this
group blank).  This way you can easily clear out abandoned
registrations, where the user completed step 1 but then bailed out for
some reason.

But I doubt you'll have a ton of users entering in property at one time,
I think you even said most of your traffic would be browsing and reading
the listings.  So I don't think it would be a problem to build up all
the data in a session and then write it all at once at the end.

-Ryan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Very large from

2006-03-15 Thread gerald_clark


fbsd_user wrote:


Thank you Gabriel, more background information may result in a
better concept, so here it is.

The web based application I am writing deals with people who have an
a home for rent. They can sign up for small fee and enter their
rental property into the application so its searchable and viewable
by the internet public. I have a membership DB with single members
table containing 20 columns which holds the users signup info.
Members can list more than one rental property. Members has
opportunity to enter some portion of the 140 columns of data to
describe the rental property. Currently the real_estate DB contains
a single property table of 140 columns.

I have grouped the info in the table into 3 groups, mandatory info
deals with the business of renting, second group deals with
location,  third group deals with house characteristics. All the 140
columns of data will be shown on the detail property screen. Fields
that don't pertain to that individual property will show up as blank
because the user did not enter data. All fields are 'varchar' to
conserve on unused space.

One single record per listed property has all the info retrievable
by one read, but has undesirable effects entering all of it from
single form.  Making 3 tables would mean duplication of some common
key fields to allow retrieving all the associated rows to combine
the data for displaying.

I would think since most of the activity will be people on the
internet searching for rental property, performance would be better
to retrieve all the info with a single read. This makes the 3 table
idea a bad performer.
 


Why?


It has been suggested to use session control as a staging function
where the forms store the entered data so a single insert of all the
data can be made to the table. Sessions store their data in standard
flat files in /tmp.


Not in my system.
I have a session table that has records that consist of:
session id
variable name
value


Flat files are not known for their access speed
and performance, with 2 or 3 fields this is ok but 100+ fields and
this may become a major performance bottleneck.

My current thinking is to break the entering of the property info
into 3 separate forms, the first with the mandatory info does a
insert to create the row with the remaining 100 columns being seeded
with $t=''. Group 2  3 will them be a update to the row using the
'set' keyword just touching the columns under their separate
control.


Does this sound like a workable solution or is there some other
approach that is better suited to my application?

Thanks to all the people who have replied all ready.


 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Accountability with MySQL

2006-03-15 Thread Rhino

- Original Message - 
From: Martijn Tonies [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Wednesday, March 15, 2006 10:47 AM
Subject: Re: Accountability with MySQL

Hello,

(please read all before replying)

   Basics of database design: store what you know.

   Given that NULLs basically means the absence of a value
   (eg: unknown), you shouldn't be storing NULLs.

  Nonsense!!

  That's a bold statement ...

  That's simply wrong. A null means unknown or not applicable and 
  is

a

  Ah, so now things become clear, a NULL actually can mean
  two things? So much for clarity then...

 Come on; that's not fair. Unknown and not applicable are more like
 different senses of the same thing, not two opposite things.

 Read the literature on how to design databases. What you do
 is storing true propositions. That is, each attribute defines a
 certain true proposition. For example:

 Employee Martijn has Employee# 14.

 You should be able to derive these sentences from every row.
 Putting a NULL or N/A in there fails to meet this requirement.

Perhaps my theory is a bit rusty but I have never heard of this true
propositions business before. Do you have a citation where I can read

about

this?

I would certainly recommend this one:
http://www.amazon.com/exec/obidos/ASIN/0321197844/databasede095-20/103-0880452-0854264?creative=327641camp=14573adid=0VQ1KDVJ56MV7389RPEQlink_code=as1

Hmm, I had hoped for a citation to a free online document that I could read. 
I don't fancy the thought of spending $100+ US on a book just to argue the 
fine points of database design

Then again, I didn't ask for a link to a free source of this information so 
I can't fault you for your link, it (presumably) answers the question.

Frankly, I'm skeptical about your interpretation of this idea. While you
clearly don't want false information in a database, it isn't false to say
that student such-and-such's exam mark or such-and-such an employee's
termination date is unknown or not applicable. It _would_ be false to

store

a grade of 0 for a student who didn't take the exam because it implies

that

he got every question wrong when in fact he didn't write the test at all;

Agreed.

that would be an example of 0 having two contradicatory meanings. Given a
phobia about nulls, it is a reasonable design to put students who don't

take

the test in a separate table but it's not the ONLY way to handle the
situation: a null to indicate a student who didn't take the test is
perfectly reasonable too.

Disagreed. Let me explain further:
In this case, a null means (according to you and in your design)
that the student did not take the test.

Yes.

It could also mean that we lots it's grade for now, and that we
want to fill it in later, as in: unknown, which is what NULL also
means (your own words).

In another case, null would be the employee isn't terminated yet,
but could be in the future.

That's the problems with nulls - there's no actual defined
meaning.

I agree that the meaning is not a single, very narrow thing like 'zero' or 
'unknown' in every single case. You have to put a null into some kind of 
context. But that context is usually fairly obvious, at least in my opinion.

You can never say:

This table holds test results, a row will mean:
Student with StudentID 123 made test #12 and scored a 4

Cause you can have rows that can mean this:
Student with StudentID 123 made test #12 and scored a
yet unknown score

or

Student with StudentID 123 made test #12 and did not make
the test

or
Student with StudentID 123 and test #12 doesn't apply to him

If the exact reason that a null is in a particular row is particularly 
important, you can always define an extra column that describes the reason 
for the null. A could mean the score hadn't been entered yet because the 
exam hadn't taken place yet, B could mean the test has already taken place 
but the student didn't write the exam, C could mean that student has no 
score because he was excused from the test for some reason. I would agree 
that this is cumbersome but I think you'd have a very similar problem with 
your design: you would have put some people in a separate table if they 
didn't write the test but you'd have to identify the reason that the test 
wasn't written too, if you cared about that, or have separate tables for 
students who failed to attend the test and students who were excused from 
the test. So I don't see that you're a lot farther ahead with your design.

In fact, your design could be _more_ complicated. In your design a program 
couldn't simply look up the student by his ID in a single table and then 
react to a null; the program would have to look in the main Grades table 
first, then, if it didn't find the student there, would have to look in the 
table that records people who didn't write the test because they were sick. 
If the row wasn't found there, it would have to look in the table that 
records people who were

Re: Question about interactive timeout

2006-03-15 Thread Pete Harlan

On Wed, Mar 15, 2006 at 05:42:40PM +0100, Mechain Marc wrote:
 I have a Mysql Server (4.1.8) where some sessions stay connected for a
 value greater than Interactive timeout value.
 
 Here is an abstract of the show processlist command:
 
 | 129996 | fret | mtt04.back:33598   | fret | Sleep   |   61756 |
 | 129998 | fret | mtt04.back:33599   | fret | Sleep   |   61759 |
 | 12 | ets | mtt04.back:33600   | ets | Sleep   |   61759 |
 | 13 | ets | mtt04.back:33601   | ets | Sleep   |   61759 |
 | 130001 | tls  | mtt04.back:33602   | tls  | Sleep   |   61755 |
 
 The show variables command gives me:
 
 Interactive_timeout 28800
 Wait_timeout 28800
 
 Why those connections do still remains on the server with a value of
 61700s while in a Sleep Command?
 
 It is rather strange for me; they normally should have disappeared after
 28800s of inactivity.
 
 Could you give me a clue?
 
 Marc.

Perhaps it's this:

http://bugs.mysql.com/bug.php?id=16995

That's when using NPTL (Native Posix Thread Library) under the 2.6
Linux kernel.  The bug report says killing one of the threads releases
them all.

--Pete

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

What is the proper (least expensive) way to do this

2006-03-15 Thread Logg, Connie A.

I have a database for ASN information in which I save asn information for 
reference by other scripts (asn lookup can be expensive).

CREATE TABLE `ASNINFO` ( 
`asnInfoID` int(11) NOT NULL auto_increment, 
`asNumber` int(11) NOT NULL default '0', 
`description` varchar(255) default NULL, 
`comment` varchar(255) default NULL, 
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP, 
PRIMARY KEY (`asnInfoID`), 
UNIQUE KEY `asNumber` (`asNumber`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Multiple Ips can share the same ASN value.

Is it better to do an 'insert ignore' into ASNINFO when I have to look one up, 
or is it better to try to fetch the ASN information to see if it exists, and 
then if it does not exist, do the insert. I figure the fetch is one sql 
command, and the insert is one sql command, so if an asn does not exist, it is 
two data base accesses, where if I do an 'insert ignore' it is only one command 
and if the value is in the table, the insert is ignored.

Thanks, Connie
Connie Logg, Network Analyst
Stanford Linear Accelerator Center
ph: 650-926-2879 
Happiness is found along the way, not at the end of the road, and 'IF' is the 
middle word in life.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: What is the proper (least expensive) way to do this

2006-03-15 Thread Martijn Tonies

Connie,


 I have a database for ASN information in which I save asn information for
reference by other scripts (asn lookup can be expensive).

 CREATE TABLE `ASNINFO` (
 `asnInfoID` int(11) NOT NULL auto_increment,
 `asNumber` int(11) NOT NULL default '0',
 `description` varchar(255) default NULL,
 `comment` varchar(255) default NULL,
 `f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
 PRIMARY KEY (`asnInfoID`),
 UNIQUE KEY `asNumber` (`asNumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

 Multiple Ips can share the same ASN value.

 Is it better to do an 'insert ignore' into ASNINFO when I have to look one
up, or is it better to try to fetch the ASN information to see if it exists,
and then if it does not exist, do the insert. I figure the fetch is one sql
command, and the insert is one sql command, so if an asn does not exist, it
is two data base accesses, where if I do an 'insert ignore' it is only one
command and if the value is in the table, the insert is ignored.


The insert ignore sounds a bit hackish to me.

Given that asNumber is indexed via the unique key, it will be fast anyway.

That being said, it is entirely possible that someone will insert the
row you are looking for between your first select and the insert
in case you didn't find it. So you would have to handle that case
anyway.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: What is the proper (least expensive) way to do this

2006-03-15 Thread RedRed!com IT Department


Martijn Tonies wrote:

Connie,




I have a database for ASN information in which I save asn information for


reference by other scripts (asn lookup can be expensive).


CREATE TABLE `ASNINFO` (
`asnInfoID` int(11) NOT NULL auto_increment,
`asNumber` int(11) NOT NULL default '0',
`description` varchar(255) default NULL,
`comment` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update


CURRENT_TIMESTAMP,


PRIMARY KEY (`asnInfoID`),
UNIQUE KEY `asNumber` (`asNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Multiple Ips can share the same ASN value.

Is it better to do an 'insert ignore' into ASNINFO when I have to look one


up, or is it better to try to fetch the ASN information to see if it exists,
and then if it does not exist, do the insert. I figure the fetch is one sql
command, and the insert is one sql command, so if an asn does not exist, it
is two data base accesses, where if I do an 'insert ignore' it is only one
command and if the value is in the table, the insert is ignored.


The insert ignore sounds a bit hackish to me.

Given that asNumber is indexed via the unique key, it will be fast anyway.

That being said, it is entirely possible that someone will insert the
row you are looking for between your first select and the insert
in case you didn't find it. So you would have to handle that case
anyway.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com





So, for my peace of mind, I would need to follow these procedures to 
ensure Martijn's issue doesn't happen:


1. lock the table
2. execute my select
3. insert if it does not already exist
4. unlock the table

On a high level, is this a correct list of procedures?

Sean

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL Query Browser 1.1.20 problem

2006-03-15 Thread Mojtaba Faridzad


Hi,

I installed this version of browser but it's not working properly. For 
example, I don't see the command window on the top of the browser to type my 
command. Do you have the same problem?


Also I noticed that when I go to mysql.mysql.com to download the commercial 
version of MySQL, I am only able to download the old versions of MySQL (4.1) 
or other tools. Do you have the same problem?


Thanks 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

OFAC SDN lists

2006-03-15 Thread Ing. Edwin Cruz

Hi folks! I'm asking in a web form for clients, those clients I have to
search them into OFAC SDN lists(
http://www.ustreas.gov/offices/enforcement/ofac/sdn/delimit/index.shtml), if
they are into that list they wont be able to do transactions in my system.

My problem is to perform a good search of the names into that list, I had
thought in fulltext, but it appears to not work.
Fulltext works with short names.


does anyone have any suggestion for this?

Regards!

Edwin.

Re: Accountability with MySQL

2006-03-15 Thread Stephen Cook

A commonly used example (at least at my last 3 jobs) would be a table of
demographics for people (whether they be employees, clients, whatever).

You can have one table and allow NULLs for some of the fields (id,
LastName, FirstName, MiddleInitial, Title, NameSuffix, AddressLine1,
AddressLine2, City, State, Zip, Phone1, Phone2, Fax, Pager), or you can
have a separate table for each of the nullable fields (I count 8 in
addition to the base demographics table).

I am not a master DBA but it seems kind of brain damaged to insist on
using 9 tables left joined together every time I need a line of
demographics.

Martijn Tonies wrote:

Hello,

(please read all before replying)

Basics of database design: store what you know.

Given that NULLs basically means the absence of a value
(eg: unknown), you shouldn't be storing NULLs.

Nonsense!!

That's a bold statement ...

That's simply wrong. A null means unknown or not applicable and is

Ah, so now things become clear, a NULL actually can mean
two things? So much for clarity then...

Come on; that's not fair. Unknown and not applicable are more like
different senses of the same thing, not two opposite things.

Read the literature on how to design databases. What you do
is storing true propositions. That is, each attribute defines a
certain true proposition. For example:

Employee Martijn has Employee# 14.

You should be able to derive these sentences from every row.
Putting a NULL or N/A in there fails to meet this requirement.

Perhaps my theory is a bit rusty but I have never heard of this true
propositions business before. Do you have a citation where I can read

about

this?

I would certainly recommend this one:
http://www.amazon.com/exec/obidos/ASIN/0321197844/databasede095-20/103-0880452-0854264?creative=327641camp=14573adid=0VQ1KDVJ56MV7389RPEQlink_code=as1

store

a grade of 0 for a student who didn't take the exam because it implies

that

he got every question wrong when in fact he didn't write the test at all;

Agreed.

that would be an example of 0 having two contradicatory meanings. Given a
phobia about nulls, it is a reasonable design to put students who don't

take

the test in a separate table but it's not the ONLY way to handle the
situation: a null to indicate a student who didn't take the test is
perfectly reasonable too.