Derby's Handling of Null Values

Derby's handling of NULL values

If you use the VALUES clause on your INSERT, you don't have to cast the NULL values:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

This will work like you expect (i.e. the database can determine that the NULLs correspond to an integer and varchar(500). This works in both DB2 and Derby (and should work in pretty much any other database engine, as well).

You can use VALUES with parameter markers as well, without having to CAST them.

The reason that you have to cast when issuing an insert into ... select from statement is because the SELECT portion takes precedence -- the select statement returns certain data types, regardless of whether they are compatible with the table you're trying to insert them in to. If they aren't compatible, you will either get an error (with strongly typed database engines like DB2 <= 9.5) or the engine will do implicit type conversion (when possible).

Is NULL not allowed is SQL delete statement (for Derby)?

I believe what you want is:

DELETE FROM `name_of_table` WHERE `name_of_column` IS NULL

SQL has this really weird property where you can use =value with most values, except NULL.

How to return a NULL value for Date type column in SQL in Derby?

It should use cast(null as DATE) AS START_DATE expression. As in the link.

I just add here for others to find it out easily. One thing interesting is even the column START_DATE is NOT NULL, I can still fake a NULL return value for this column. Actually it should be , not a surprise at all here,right?

SpringBoot JPA Derby - Null Pointer Exception while making CRUD requests

I think there is a bean issue, you missed autowire for repository.
Try @Autowired
private EmpRepository repo; in controller.
Creating a constructor is not required in controller.

Why does using a prepared statement fail with nulls and succed with GStrings?

You'll get better responses if you include the actual exception that you've encountered. You can find the exception in your Derby server's derby.log file, or you can also modify your application to print the entire exception chain, not just the client-side outermost exception: http://wiki.apache.org/db-derby/UnwindExceptionChain

Without seeing the actual exception, it's quite hard to help you, but I'll venture a guess that you're seeing https://issues.apache.org/jira/browse/DERBY-1938

Do foreign keys in apache derby automatically populate a column?

Foreign key constraints simply constrain the values in a column without populating that column.

Your application is expected to set the value of the foreign key column in the referring table to the value of the referenced column in the referenced table.

Typically, the rows are related, and your application knows that it is working with a particular row that is in a particular relationship to the referenced row in the other table.

For example, in a simple workforce management application, you might have tables EMPLOYEE, and DEPARTMENT, and your EMPLOYEE table might have foreign key columns MANAGER_NAME and DEPARTMENT_NAME. When inserting a new row for a new employee, your data entry form has already asked the operator to pick the employee's department from a list of departments, and the employee's Manager from a list of managers, and so your application then sets the MANAGER_NAME and DEPARTMENT_NAME with that data at the time you perform your INSERT

Very long time execute queries with NOT NULL expression in Derby DB

You are killing yourself on this query primarily due to your distinct of not nulls... You are blowing through ALL ATTACHMENTS TWICE for original and compressed respectively, yet you are only interested in a single patient. I've restructured the query to START with the WHO you want... The patientPersonID. From that, join to the message attachments. You only care about anything that is attached to this ONE PERSON. This should result in a very small set of records. Of THOSE records, only THOSE do you care to look at the attachment table itself and see if any qualify for your DPTYPE, like condition and IS NULL.

I would ensure you have an index on your messagecontent table on (PersonIDPatient) at a minimum, and if any other columns AFTER the first position, no problem. The joins to the other tables appear to be on their respective primary ID column and would assume that you have indexes on those.

SELECT 
      atch.ID, 
      atch.SIZE, 
      atch.AUTHOR, 
      atch.FILENAME, 
      atch.FILETIME, 
      atch.FILEDATE, 
      atch.CREATIONDATE, 
      atch.CREATIONTIME, 
      atch.FILETYPE, 
      atch.COMPRESSEDPICTUREID, 
      atch.ORIGINALPICTUREID, 
      atch.FIRSTUSE 
   FROM 
      MESSAGECONTENT msgCont
         JOIN MESSAGECONTENT_ATTACHMENT msgAtt
            ON msgCont.ID = msgAtt.MESSAGECONTENT_ID
            JOIN ATTACHMENT atch
               ON msgAtt.ATTACHMENTS_ID = atch.ID 
              AND atch.DTYPE = 'P' 
              AND atch.ORIGINALPICTUREID IS NOT NULL
              AND atch.CompressedPictureID IS NOT NULL
              AND (   atch.FILENAME LIKE '%jpeg' 
                   OR atch.FILENAME LIKE '%jpg' 
                   OR atch.FILENAME LIKE '%tiff' 
                   OR atch.FILENAME LIKE '%tif' 
                   OR atch.FILENAME LIKE '%bmp' 
                   OR atch.FILENAME LIKE '%gif' 
                   OR atch.FILENAME LIKE '%png' 
                   OR atch.FILENAME LIKE '%ser')
   WHERE 
      msgCont.PersonIDPatient = '0584393a-0955-4c9b-98f7-d31c991d22a3' 

---

Related Topics