Getting Null Values From A TEXT NOT NULL Field
I have created a table with a field labeled Description which is a TEXT field which does not allow null values. It's defined with
Description TEXT NOT NULL,
However, when empty strings are stored in the Description field, I get NULLs back when querying the database. I'm writing an ASP application which connects to the MySQL database through ADODB. The MySQL version is 5.0.45. The engine is InnoDB.
View Complete Forum Thread with Replies
Related Forum Messages:
Null Field Values
I created a new database for an application that i am working on , from the delphi application that connects to mysql 5 database i can pass records with no data to not null fields , i was suprise and i did the same thin from mysql query browser, it seems that mysql dosn't check tha property before it stores the reccord.
View Replies !
PK Field Not Null Is Being Null
I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT. 2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented. 3.Can I set user privilege to a particular row (on a particular PK); HOW? 4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK. 5. How can I make a one-to-one relationship in MySQL;
View Replies !
MySQL 4.0.17 Data Scrambles, Not-null Fields Become Null, Etc.
I'm having the most bizarre problem with PHP/MySQL that I've ever faced and it's an urgent matter (of course) to try to fix it ASAP. We have one record inserted into a table with 75 columns, whereby half of the columns are not-null, but for some bizarre reason, the fields are null. The rest of the columns that are null are completely scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url" is in "last_name", etc.) This just recently occurred and seems to have no history otherwise. It occurred sometime on Saturday when the record was inserted but logs indicate no unusual activity at that time. Has anyone seen this before and found a way to prevent it from occurring again?
View Replies !
Incorrect Integer Value: 'NULL' For Column Even If NULL Is Allowed
mysql> desc my_table; +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+-------+ | id | varchar(19) | NO | PRI | | | | value1 | int(11) | YES | | NULL | | | value2 | int(11) | YES | | NULL | | +------------+---------------------+------+-----+---------+-------+ I want to import data from a file that looks like this: 9bcd6f7b47a30fb8_01;NULL;NULL With following command: mysql> load data infile 'filename' into table my_table fields terminated by ';'; And get following error: ERROR 1366 (HY000): Incorrect integer value: 'NULL' for column value1'' at row 1 So the system denies me importing NULL values into a field where NULL is allowed.
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
NULL Values
I have 2 tables, one for links and other for categories. each link might be associated with some category BUT not necessarily. table 'category' has 'id' field with auto increment. table 'link' has field 'CategoryId' which points to some category id. In case some link is not associated with any category what value for 'categoryId' is more suitable: NULL or -1?
View Replies !
Sum And Null Values
Was reading another recent post (select daily item orders & totals for a given month ), and not to write on the same post I open an new one, reading it made me remember an small problem I experienced using below (part) of code. sum(importe) - sum(servicios) -sum(pagado) as total_duenos Sometimes when adding a new property and when I sum , the column importe has values but the others two do not have anything yet, and I get as result 0, which is not correct, so to fix it, I put 0.00 in the columns servicios and pagado. the columns in question are type:decimal(10,2) Is this due to that the columns have as default value null? The null and not null is something I ever understood.
View Replies !
Java Null Values
How can I set null on a TEXT or VARCHAR field ? I've tried using the PreparedStatement.setNull(int, int) method but seem to get no joy, it defaults to the empty string as soon as I re-read the data back.
View Replies !
Handling Null DB Values
if a DB value is null, how do i handle it when it comes back from the database. I am assuming i need to convert it to an empty string or something of that nature? How can i do this? For example, the following code has a lot of null values:Code: SELECT CONCAT("<img src='thumbs/",s.Thumbnail,"' alt='' class='teaserImage' /><h3><a href="javascript:NewWindow('free.aspx?vod=",s.StoredVideoID,"','mypopup','1024','768','center','front');"><u>",s.Title,"</u></a><h3><p>",LEFT(s.Description, 100),"...</p>") As Event from StoredVideo s;
View Replies !
Handling Null Values
I have a tab-delimited text file containing null values (back to back tabs). When I use mysqlimport to import the data I get the following error: mysqlimport: Error: Incorrect date value: '' for column 'field_name' at row 1, when using table: table_name I would like the import tool to ignore these values and insert nothing into the table for those particular fields. Is this possible?
View Replies !
Making Values Null
Is it possible and safe to make a value into null in my database? My table contains rows with data and some collumns should be null but instead are like the following below ID | Make 1 null 2 honda 3 4 null 5 Ford So 3 should be null. I'm not sure why it is not showing up as not being null. Is it easy to change this across many rows and collumns
View Replies !
Inserts Null Values
I'm the administrator of a project that will be using mysql and php to drive a web site. I've gotten a set of tables from the developer, created the database, and run the script he sent to create the tables. But now when I try to import the data tables (shell>mysqlimport -u mysql -p dbname datatable.sql) all that goes into the database table are null values. There are the correct number of columns and the row id is correct, but none of the data in the .sql files is acutally making it into the database. I'm pretty new at this and am hoping to sell my boss on the mySQL/php solution so any help would be appreciated.
View Replies !
Changing To Null Values
if i have a few rows in a table which currently have the number 0 in but i want to change them to NULL how do i do this i am trying it using the edit button in query browser but it keeps giving an error message.
View Replies !
Ignore Null Values
I am trying to perform a datediff function on two fields, the second of which has some null values. Is there any way I can ignore the null values, and return only those records that have data in both fields?
View Replies !
Adding Null Values
I have two tables from whic I am getting two int fileds and I want to add both the fields. I am able to add them but my problem is, one of the two fields may be null, then I am not getting the value at all, the total field is null. int field a from table ABC int field b from table XYZ creating new table TOTAL wit fields sum(a), sum(b), c (c = a + b)..(all int fields) now the output of c is right when both a & b r not null, and if anyone of them is null, the value for c is also null. How could I get the value to c even if one of the two values r null.
View Replies !
Null Datetime Values In Csv
I am trying to import a csv (generates from Sql Server) into mysql. It is failing on a datetime field with the message "mysqlimport: Error: Incorrect datetime value: '' for column 'DATEOFBIRTH'". column dateofbirth is defined as: Field:DATEOFBIRTH | Type:datetime | Null:YES | | Default:NULL I've tried values, '', "", Null, "Null", NULL, "NULL", ,, etc, but it doesn't like it. Any ideas?
View Replies !
Select NULL Values
I have a table in which logos are stored. The logos have a column 'buyTime' and a column 'accountID'. When a logo isn't bought the value is NULL, otherwise the value is a Date. What I want to do is select the accountID of logos where the buyTime is NULL. This is easy, but the hard part (for me) is when multiple logos are stored per accountID (thus multiple rows with the same accountID, but other logos) to select all accountID's where all buyTime values are NULL. So no accountID should be added to the result of which minimal one row has a Date value instead of NULL as a value. Example: accountID|buyTime 1 23-01-2007 1 NULL 1 NULL 2 NULL 2 NULL 2 NULL
View Replies !
Checking NULL Values
I would like to use a SQL statement to concatenate an area code, phone number, and if there is an extension, 'x'extension. So if area code is 555, number is 555-5555, and extension is NULL, it becomes 555-555-5555. But if extension is 5555, it becomes 555-555-5555x5555.
View Replies !
How To Deal With Null Values
iam reading a insert file into a mysql table using the '<' command insert into mysql.table values (11111,",",34343); insert into mysql.table values (11111,",",34343); insert into mysql.table values (11111,",",34343); insert into mysql.table values (11111,'2004-12-12','2004-12-12',34343); the 2nd and 3rd fields are supposed to have dates but there is not data in the oracle field for these records so are null values now when i try to run this file into mysql table i get the error you have an error in the sql syntax at line 1 insert into mysql.table values (11111,",",34343);
View Replies !
JOIN Gives Null Values ... Don't Know Why!
I'm running this query: SELECT gamescatalogue.Title As Title, gamescatalogue.Released As ReleaseDate, cataloguelinks.Game As GameID FROM cataloguelinks LEFT JOIN gamescatalogue ON gamescatalogue.ID = cataloguelinks.Game WHERE cataloguelinks.Category = {$catID} ORDER BY gamescatalogue.Released DESC LIMIT 0, 12; I get a Null for any value I attempt to get from the gamescatalogue table. Any ideas as to why that might be?
View Replies !
MySQL Null Values
I want to return all rows that have NULL in a certain column. I'm using this: "SELECT * FROM myTable WHERE myDateTimeField = NULL" I can use an INSERT or UPDATE queary with NULL to set the myDateTimeField to NULL, and I can use the "<>" operand to get all rows that are not NULL, like this: "SELECT * FROM myTable WHERE myDateTimeField <> NULL" But, that first statement always returns nothing, even though I know there are results for it being NULL...
View Replies !
Foreign Keys With Null Values
The scenario: I have a number of customers and suppliers, and often more than one contact at each (ie in the case of the suppliers there might be an account manager, technician, director etc - each with their own contact details but sharing the same address) I am wondering what is best practice - to have a CUSTOMER table and a SUPPLIER table and one CONTACT table, with two foreign keys in the CONTACT table referencing the CUSTOMER table and the SUPPLIER table (hence one or other of the foreign keys will be null), or combine the CUSTOMER table and the SUPPLIER table into one ORGANISATION table with a TYPE field to determine whether the organisation is a customer or a supplier. In the case of the latter, the CONTACT table will have just one foreign key referencing the ORGANISATION table, therefore no null values will exist in the Foreign Key field... Any thoughts? Does it matter if foreign keys contain a null value?
View Replies !
Dealing With Null Values In An Expression
I am trying to return age in Days as follows: select frmCode, (ageYears*365+ageMonths*30+ageWeeks*7+ageDays) as AgeInDays from sdata; i realised that if one of those columns has a null value, the whole expression evaluates to null. IS there a way to get around while creating a view, or do i need to write a StoredProcedure and have a logic to ignore the null values.
View Replies !
Null Values In LEFT JOIN
I got 3 tables (user, user_group, user_activation) Every row in user table will have a corresponding row in user_group table (based on userid) but not for user_activation table. So: SELECT * FROM user AS user LEFT JOIN user_group AS ugroup ON user.user_group_id = ugroup.user_group_id LEFT JOIN user_activation AS activate ON user.user_id = activate.user_id WHERE user_id = '1' LIMIT 1; will give me all the details about that user that has user_id = 1. However, assume user_id '1' does not exists in user_activation table. Using PHP, when I try to retrieve the field with 'user_id', it gives me null value because activate.user_id = null, but user.user_id = 1.
View Replies !
Group By To Include Null Values
I have a query that is half working, maybe I am going around it the wrong way. The idea is that it should list all types t1.type and show how many types there are in another database including where there are none. The result ot the query below is the subset, not the full range with the zeroes... I have had a good look around and added "where t2.type is not null" have found not null works a bit it shows only one additional zero results: select t1.id, t1.type, t1.description, count(t2.id) from specialist_type as t1 left join specialist_inclusion as t2 on t1.id = t2.type where t2.type is not null group by t2.type
View Replies !
Order Result So That Null Values Comes First
I want to do a query sort of like this: SELECT * FROM table1 NATURAL JOIN table2 ORDER BY table1.datetime The datetime column contains NULL values and datetime values. I want the rows where datetime is NULL to come first (I get that with the query above) and after that the rows where datetime contains the highest value (not happening with the query above, the lowest datetime values comes right after the NULL values). Is there a way to get the result I want?
View Replies !
GROUP_CONCAT Not Ignoring Null Values
having an issue where GROUP_CONCAT is including null values in the result set. I have made sure that both columns in question are NULL by default (they originally were not) - one is VARCHAR and one is TEXT. Version is 5.0.67. Does anybody have any suggestions? Is there another way to cure this besides embedding an IF clause in the GROUP_CONCAT statement?
View Replies !
Changing NULL Values To An Actual Value
I have a very large database and i ned to change a feild across the whole database, i.e. i have a feild called Fault that at the moment is NULL i need to change this to NoPic right across the database. I have tried to use UPDATE `tblAnalysis` SET Fault = replace(Fault,"NULL","NoPic")
View Replies !
Order By Desc On AVG(value) => NULL Values In First
My query is : SELECT AVG(sucre) AS sucre, AVG(qualite) AS qualite, AVG(gout) AS gout, parfum, marque, count(sucre) AS nb_eval, tabacs.id FROM tabacs LEFT OUTER JOIN tabacs_notes ON tabacs.id=tabacs_notes.tabac GROUP BY tabac, tabacs.id ORDER BY sucre DESC LIMIT 00, 10 According to mysql documentation, NULL values of "sucre", "qualite" and "gout" must be in last with this query : "When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL 4.0.2 through 4.0.10, NULL values sort first regardless of sort order. " However NULL values are in first.
View Replies !
Accepting Values As NULL When Not In Another Table
Is it possible to perform a SELECT query on multiple tables and simply accept one of the values as NULL if not in another table rather than the whole query failing? For instance, if "rec_id" exists in "table_1" for certain but it is uncertain if it exists in "table_2", is it possible to return the query with a variable assignment stating whether "rec_id" was or wasn't found in "table_2"? I am hoping I can do this without a LEFT OUTER JOIN as the query is fairly complex and involves five different tables. Whenever results are fetched I simply need to know if rec_id is or isn't in table_2 if it is found in table_1. As there are frequently over 10,000 result rows returned it wouldn't be sound to put this into a seperate loop that queries with each rec_id to see if it is in table_2. I have tried "SELECT table_1.rec_id, table_2.rec_id FROM table_1, table_2 WHERE table_2.rec_id=table_1.rec_id OR table_2.rec_id IS NULL" but this returns no records.
View Replies !
ORDER BY: Null Values At The Bottom
I'm ordering a table by a TIME field in ASC order. The time field can be NULL. So, when i order it, the NULL values appear at the top and i want them at the bottom, after the rest of the timed entries. Anybody knows how to do this? I tried with the COALESCE() function, but i think it didn't do it right.
View Replies !
Null And The Text Data Type
Why do we use null values? Why is it better than an empty field? What would be a real life situation were nulls are important to have? If I have a paragraph of text in a field of data type: TEXT. Can I search in this field? Maybe i mean, could i use SELECT on a table and get rows on the basis of a string or substring in the TEXT field?
View Replies !
LEFT JOIN Returning NULL Values
I am trying to perform a LEFT JOIN on a table which may or may not have matching rows. If there are no matching rows, it returns NULL's for all the missing fields. Is there anyway of returning the default values for that table instead of NULL's, in a portable way? My query currently looks like this: SELECT i_product.name, i_product.price, `i_tax-rate`.rate FROM i_product LEFT JOIN `i_tax-rate` ON (`i_tax-rate`.`tax-rate-id` = i_product.`tax-band-id`) WHERE i_product.id = 123';
View Replies !
How To Change Cell Values From NULL To BLANK?
In the result of a SELECT statement, how can you change cell values from NULL to BLANK? The following does NOT do it: SET fieldname = ' ' WHERE fieldname IS NULL Also, for colums with a DATE data type, I want to change 0000-00-00 to BLANK. For for colums with a numeric data type such as DOUBLE, I want to change 00.00 to BLANK. Again, the above doesn't work. How can I blank those fields?
View Replies !
Make GROUP_CONCAT Not Group Null Values
I have a complex SELECT statement that uses GROUP_CONCAT to group like values -- which has been drastically over simplified below. From what I can surmise, GROUP_CONCAT is treating NULL as a value -- which I don't want. Is there a way to make it ignore null values to produce the 'desired output' below? TABLE: test +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c | +------------+------------+ | 1 | d | +------------+------------+ Current Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a|b | +------------+------------+ | 1 | c|d | +------------+------------+ Desired Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c|d | +------------+------------+
View Replies !
NULL Values Are Excluded In Left Joins
I have 2 tables:Customer and products, for each customer record i have product1, product2,product3 which contain product codes taken from the products table, but any of the product1, product2, product3 fields can be empty i.e contain null values. I need to retrieve all customers with the product names for fields product 1,product2 and product3, if any one these is null, it should be pulled as NULL itself. I have used left join on products tables but still, those records which have any prodcut field as NULL just doesnt come out.
View Replies !
Primary VS Unique Keys & NULL Values
My table: ID: INT SET_ON: TIMESTAMP CLOSED: DATETIME OPTION1: ENUM OPTION2, etc... When a record is created, we populate all fields except CLOSED, which remains the default of NULL. This is the current record for this ID. We're tracking changes, so if a change is made to the options for an ID, we update the current record with NOW() to the CLOSED field and create another record. My question is about how best to key this (InnoDB) table. PRIMARY doens't allow columns with NULL values, so using ID and CLOSED won't work (unless I switch to using 0 filled value instead of NULL, which I'd rather not.) ID & SET_ON would work, but I'd like to restrict each ID to a single current record. I can add a UNIQUE key with ID & CLOSED, indexing ID again, but that seems wasteful. This also makes the table's PRIMARY key seem a bit depreciated... Is there a better way to get what I want?
View Replies !
Group Records? Only List Users With Only NULL Values
I have this result-table (running on 4.0.24), how do I collect all o_user_id which does not have NOT NULL value in reciept_id? (I do not want the 745-user in result, as it contains a value for reciept_id) +-----------+------------+-----------+-----------+-----------+---------+ | o_user_id | reciept_id | r_user_id | d_dist_id | r_dist_id | item_id | +-----------+------------+-----------+-----------+-----------+---------+ | 745 | 18 | 745 | 1 | 1 | 1 | | 745 | NULL | NULL | 2 | NULL | 1 | +-----------+------------+-----------+-----------+-----------+---------+
View Replies !
NULL Field Or NOT NULL Field?
I have created a DB and started using PHP to pull data and display it. However I am having trouble understanding the Null field. I've searched the web and read what I could but I am no more intelligent from that research .Can someone put into simple words what this field means ? What happens when it is set to "null" or "not null".
View Replies !
CREATE TABLE :: Null And Not Null
I recently created a new table in a database and wanted some fields to accept nulls (NULL) and others to not accept nulls (NOT NULL). The table was built correctly, however, when I went to edit a row in the Query Window of MySQL Browser it allowed me to enter in the row when the NOT NULL fields had no values attached to them - this should fail and stop me from entering a row. Here is the script for the table I created; CREATE TABLE SLG ( SLG_ID int(11) NOT NULL auto_increment, SLG_CODE CHAR(1) NOT NULL, SLG_DESCRIPTION VARCHAR(255) NOT NULL, SLG_DATE_CREATED DATETIME NOT NULL, SLG_DATE_MODIFIED DATETIME NOT NULL, PRIMARY KEY (SLG_ID) ); When I apply the changes in this window it just enters NULLS into all the other fields. Is this a Query Browser issue. I tried doing the same with an INSERT statement and received the same result. Also - related to creating a table - is it possible to create global Defaults on a database and then reference those in the DEFAULT statement of the CREATE TABLE command. MS SQL has a system function that can be executed after a table is created to bind a global default to a specific column.
View Replies !
Insert Null To Not Null Column
I have build a replication environment in my environment. the master is MySQL 5.0.24a-standard-log and the slave is 5.1.30-log. the configuration is not the same between them. I have found a replicaiton error in the slave today says: .....
View Replies !
Checking A Field For NULL
I have imported information into a database using a file. It is tab delimited and for NULL fields I have NULL. example file.txt 1<tab>Hello World<tab>NULL<tab>5.56 1) Is this a correct way to make sure that this field will be NULL 2) in C++ after I fetch the field can I say if(<field> != NULL)
View Replies !
NULL For Date Field
I have a date field in my database which is set to accept NULLs. If I enter a record from phpMyAdmin and leave the date field blank, the record is entered properly. However, if I do the same thing from a form, I get an error that the date cannot be blank. It seems that I need to enter a NULL in the date field from the form rather that nothing.
View Replies !
|
TRACKER
