I have an entity Recipe, which besides all attributes also holds an attribute of java.sql.Timestamp corresponding to column date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP in my database. I understand that jpa is providing the database with a null value of Timestamp, but I cannot think of a fix. Ideally, when inserting a new recipe, jpa should not provide a non-null value for Timestamp, but rather let database give it the default value. Or atleast this is my idea. Any idea how to help me overcome with this error? Thank you
17970
For a certain Hibernate entity we have a requirement to store its creation time and the last time it was updated.  How would you design this?   What data types would you use in the database (assuming MySQL, possibly in a different timezone that the JVM)?  Will the data types be timezone-aware? What data types would you use in Java (Date, Calendar, long, ...)? Whom would you make responsible for setting the timestamps&mdash;the database, the ORM framework (Hibernate), or the application programmer? What annotations would you use for the mapping (e.g. @Temporal)?   I'm not only looking for a working solution, but for a safe and well-designed solution.
119160
This Trigger is on lead (Before Insert, Before Update) and it errors (System.NullPointerException: Attempt to de-reference a null object). I'm getting this error when i'm posting "Web to Lead". However i do have the right ORG iD and Record type id included on the xml file:   &lt;input type=hidden name="oid" value="xxxxxxxxxxxxx"&gt; &lt;input type=hidden name="recordType" id="recordType" value="zzzzzzzzzzzzzzzzzz"&gt;    trigger IAAILeadTrigger on Lead (before insert, before update){ //BEGIN: S-444340 T-561808 - Process only IAA Remarketing Recordtype data     Map&lt;ID,Schema.RecordTypeInfo&gt; rt_Map = Lead.sObjectType.getDescribe().getRecordTypeInfosById();      //Looks up the branches based on the Lead PostalCode     if(trigger.isBefore){          for(Lead ld : trigger.new){                      if (rt_map.get(ld.recordTypeID).getName().containsIgnoreCase('IAA Remarketing') )                if( trigger.isInsert ||                    (trigger.isUpdate &amp;&amp; ld.Branch__c == null) ||                   (trigger.isUpdate &amp;&amp; ld.PostalCode != trigger.oldMap.get(ld.id).PostalCode)               ){                   leads.add(ld);               }           }           }                 //Only check the leads that are in the lead list and NOT the full trigger.new list         if(!leads.isEmpty()){             LeadController.GetLeadBranches(leads);           }     }  }
77368
Some issues with timezones in PHP have been in the back of my mind for a while now, and I was wondering if there are better ways to handle it than what I'm currently doing.  All of the issues revolve around reformating database stored dates:  When dealing with a site that has to support multiple timezones (for users), to normalize the timezone offest of stored timestamps I always store it with the server timezone using the CURRENT_TIMESTAMP attribute or the NOW() function.   This way I don't have to consider what timezone was set for PHP when the timestamp was entered (since PHP time functions are timezone aware). For each user, according to his preference I set the timezone somewhere in my bootstrap file using:  date_default_timezone_set($timezone);   When I'm looking to format dates with the php date() function, some form of conversion has to take place since MySQL currently stores timestamp in the format Y-m-d H:i:s. With no regard to timezone, you could simply run:  $date = date($format,strtotime($dbTimestamp));   The problem with this is that date() and strtotime() are both timezone aware functions, meaning that if the PHP timezone is set differently from the server timezone, the timezone offset will apply twice (instead of once as we would like).  To deal with this, I usually retrieve MySQL timestamps using the UNIX_TIMESTAMP() function which is not timezone aware, allowing my to apply date() directly on it - thereby applying the timezone offset only once.  I don't really like this 'hack' as I can no longer retrieve those columns as I normally would, or use * to fetch all columns (sometimes it simplifies queries greatly). Also, sometimes it's simply not an option to use UNIX_TIMESTAMP() (especially when using with open-source packages without much abstraction for query composition).  Another issue is when storing the timestamp, when usage of CURRENT_TIMESTAMP or NOW() is not an option - storing a PHP generated timestamp will store it with the timezone offset which I would like to avoid.  I'm probably missing something really basic here, but so far I haven't been able to come up with a generic solution to handle those issues so I'm forced to treat them case-by-case. Your thoughts are very welcome
117943
I have a login table into which all inserts are done by a single stored procedure.  CREATE TABLE dbo.LogTable(     LogRefnr int IDENTITY(1, 1) NOT NULL,     LogQuery varchar(255) NOT NULL,     LogTime datetime NOT NULL,     logQueryDuration int NULL,     LogSessionID int NULL,  CONSTRAINT PK_Log PRIMARY KEY CLUSTERED  (LogRefnr) ) go   Create procedure DBO.LogInsert ( @Query varchar(255), @time datetime, @duration int, @SessinID int) as begin     Insert into LogTable ( LogRefnr, LogQuery, logQueryDuration, LogSessionID)     Values  (@Query, @time, @duration, @SessinID); end; GO   Currently there are about 45500000 rows in that table and I want to direct logging to a different table.  My idea is to use the following script  begin Transaction  exec sp_rename LogTable, LogTableOld;  CREATE TABLE dbo.LogTable(     LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld     LogQuery varchar(255) NOT NULL,     LogTime datetime NOT NULL,     logQueryDuration int NULL,     LogSessionID int NULL,  CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr); ) go  sp_recompile LogTable; go  Commit;   Does this work and has minimal impact to other procedures calling LogInsert?
101615
I have a table like the following:  create table my_table (     id   int8 not null,     id_A int8 not null,     id_B int8 not null,     id_C int8 null,     constraint pk_my_table primary key (id),     constraint u_constrainte unique (id_A, id_B, id_C) );   And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:  INSERT INTO my_table VALUES (1, 1, 2, NULL); INSERT INTO my_table VALUES (2, 1, 2, NULL);   But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.  How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?
118520
@Column(name="DateOfBirth") private Date dateOfBirth;   I specifically need the above code to create a column named "DateOfBirth," instead Hibernate gives me a column named date_of_birth.  How can I change this?  Is there a web.xml property?  I came across DefaultNamingStrategy and ImprovedNamingStrategy, but not sure how to specify one or the other.
93683
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read , but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).  This is what I have, but it allows me to insert a new record for a store with a different effective date.  Table Definition:  /****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) DROP TABLE [Z_STORE_TEAM] GO  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) BEGIN CREATE TABLE [Z_STORE_TEAM](     [STORENUM] [int] NOT NULL,     [TEAM] [varchar](10) NULL,     [EFFECTIVE] [date] NOT NULL,     [FINISHED] [date] NULL, PRIMARY KEY CLUSTERED  (     [STORENUM] ASC,     [EFFECTIVE] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] END GO   Sample Data:  INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)   Instead of Update Trigger:  CREATE TRIGGER [tr_ZStoreTeam_update]     ON  [Z_STORE_TEAM]    INSTEAD OF UPDATE AS  BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      -- Insert statements for trigger here     INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)     SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE     FROM inserted I     INNER JOIN PCR.Z_STORE_TEAM ST         ON I.STORENUM = ST.STORENUM      UPDATE ST     SET FINISHED = GETDATE()     FROM PCR.Z_STORE_TEAM ST     INNER JOIN inserted I         ON ST.STORENUM = I.STORENUM         AND ST.EFFECTIVE = I.EFFECTIVE END  GO
65259
Goal:  Add a database index to a 'created' field.  Given:  There is a custom entity with a base field created. The field is stored the entity table.  Field source code of the created field:  $fields['created'] = BaseFieldDefinition::create('created')   -&gt;setLabel(t('Created'))   -&gt;setDescription(t('The time that the entity was created.'))   -&gt;setDisplayOptions('view', [     'label' =&gt; 'hidden',     'type' =&gt; 'timestamp',     'weight' =&gt; 3,     'settings' =&gt; [       'date_format' =&gt; 'medium',       'custom_date_format' =&gt; '',       'timezone' =&gt; '',     ],   ])   -&gt;setDisplayConfigurable('view', TRUE);   What I researched:  I have not found a way to add index using the \Drupal\Core\Field\BaseFieldDefinition().  As I understand indexes are defined in a @FieldType plugin. The created field is implemented via the \Drupal\Core\Field\Plugin\Field\FieldType\TimestampItem() plugin and obviously does not have have indexes there.  P.S. I know, that I can write an updatescript to add an index to the table manually.
42591
I have a table like this:  create table foo (foo_label text, foo_price int, foo_date date);  insert into foo (           values           ('aaa', 100,  '2017-01-01'),           ('aaa', NULL, '2017-02-01'),           ('aaa', NULL, '2017-03-01'),           ('aaa', NULL, '2017-04-01'),           ('aaa', 140,  '2017-05-01'),           ('aaa', NULL, '2017-06-01'),           ('aaa', 180,  '2017-07-01')         );   As you can see a few values on the foo_price column are missing.  What I need is that missing values get filled up with the "previous" available value in this way:   foo_label | fixed_foo_price | foo_date -----------+-----------------+------------  aaa       | 100             | 2017-01-01  aaa       | 100             | 2017-02-01  aaa       | 100             | 2017-03-01  aaa       | 100             | 2017-04-01  aaa       | 140             | 2017-05-01  aaa       | 140             | 2017-06-01  aaa       | 180             | 2017-07-01   My attempt:  select      foo_label,      (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,     foo_date from (   select        foo_label,        lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price,        foo_price,       foo_date       from foo ) T;   As you can see from here:    It doesn't fill completely the '100' series.  Any idea how can I get the wanted result?
87942
I've been searching a lot but couldn't find a solution. How do you deal with a DateTime that should be able to contain an uninitialized value (equivalent to null)? I have a class which might have a DateTime property value set or not. I was thinking of initializing the property holder to DateTime.MinValue, which then could easily be checked. I guess this is a quite common question, how do you do that?
10468
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL.  I don't remember why this was the case though.  All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).  But what do you do in the case of dates, datetime, and time (SQL Server 2008)?  You'd have to use some historic or bottomed-out date.  Any ideas on this?
99276
How can i insert missing dates from a query i created.  The result below:  Date          Frequency 2014-05-18    5 2014-05-20    7 2014-05-25    7 2014-05-27    6   I want the result to have missing dates with 0 value as shown below:  Date          Frequency 2014-05-18    5 2014-05-19    0 2014-05-20    7 2014-05-21    0 2014-05-22    0 2014-05-23    0 2014-05-24    0 2014-05-25    7 2014-05-26    0 2014-05-27    6   Please note that I have read only access to the server.
52304
Trying to insert a Contact from an Oracle Database:  Contact Database Entity:  @Temporal(TemporalType.DATE) @Column(name="DOB") private Date dob;   Contact SOAP SObject set Birthday:      Calendar dobCalendar = new GregorianCalendar(); dobCalendar.setTime(dwContact.getDob()); contact.setField("Birthdate", dobCalendar);   Error Message:     ERROR CODE: FIELD_INTEGRITY_EXCEPTION MESSAGE: Birthdate: invalid   date: Fri Nov 30 00:00:00 GMT 3   Any suggestions?
63369
Does any one know how I can specify the Default value for a DateTime property using the System.ComponentModel DefaultValue Attribute?  for example I try this:  [DefaultValue(typeof(DateTime),DateTime.Now.ToString("yyyy-MM-dd"))] public DateTime DateCreated { get; set; }   And it expects the value to be a constant expression.  This is in the context of using with ASP.NET Dynamic Data.  I do not want to scaffold the DateCreated column but simply supply the DateTime.Now if it is not present.  I am using the Entity Framework as my Data Layer  Cheers,  Andrew
117870
I can't figure out how to do this.  There seems to be no global constant for blank/null associated with Date fields.  If I attempt to add {!$GlobalConstant.EmptyString}, or {!$GlobalConstant.Null} to the assignment I get a validation error.  If I leave the assignment field empty, then it doesn't do anything to the date. Below is a picture of my options.    Is this even possible?
44774
I have a table structured like this:  CREATE TABLE [TESTTABLE] (     [ID] [int] IDENTITY(1,1) NOT NULL,     [DateField] [datetime] NULL,     [StringField] [varchar](50),     [IntField] [int] NULL,     [BitField] [bit] NULL )   I execute the following code:  BEGIN     INSERT INTO TESTTABLE (IntField, BitField, StringField, DateField)     VALUES ('1', 1, 'hello', {ts '2009-04-03 15:41:27.378'});       SELECT SCOPE_IDENTITY()   END   And then   select * from testtable with (NOLOCK)   and my result shows:  2009-04-03 15:41:27.*377*   for the DateField column.  Any ideas why I seem to be losing a millisecond??
116392
If I create a table with an identity column as primary key, and all the other columns have default values, for example  create table rr (id int identity(1,1) primary key, dt datetime default getdate())