Old Bug Resurrects

[peterlaursen]

this is generated by SQLyog 4.2 beta1:

Code:

insert into `test`.`tablename1` ( `a`, `b`, `c` ) values (  2,  'CURRENT_TIMESTAMP',  'ff' )

Wrong!!

Correct would be

Code:

insert into `test`.`tablename1` ( `a`, `b`, `c` ) values (  2,  CURRENT_TIMESTAMP,  'ff' )

It is not the literal string 'CURRENT_TIMESTAMP' but the value CURRENT_TIMESTAMP that should be written.

I believe this has been fixed once before ?

Create statement for the table simply

Code:

CREATE TABLE `tablename1` (                                      
             `a` bigint(20) NOT NULL auto_increment,                        
             `b` timestamp NOT NULL default CURRENT_TIMESTAMP,              
             `c` varchar(20),        
             PRIMARY KEY  (`a`);

[Ritesh]

This was never fixed 🙁

We dont consider any function/keyword in Insert/Update tab. We always generate simple quoted ' statement to insert value into the table.

[peterlaursen]

OK … I also justed worked a little bit more on it.

I believe it was a problem with the CREATE TABLE and ALTER TABLE -panes, that we were discussing about a year ago and that was (more or less) fixed.

As far as i remember the ALTER TABLE pane replaced “default CURRENT_TIMESTAMP” with ” default 'CURRENT_TIMESTAMP'”, but that does not happen anymore.

And there might be details that I don't remember. I can't find the old threads in the Forums.

[peterlaursen]

Proposal for a solution:

Write only:

Code:

insert into `test`.`tablename1` ( `a`, `c` ) values (  2,  'ff' );

If a column has a DEFAULT and input cell in DATA pane is empty then there is no use in writing anything, when inserting data. The server handles it!

I a column has NO DEFAULT and input cell in DATA pane is empty then probably it should be left as NULL.

This must apply to all datatypes, I think?

There is a problems with strings. Does an empty cell mean NULL or empty string? I think the best solution is that the empty row at the buttom for new data display DEFAULT if there is one or NULL if there is no default.

hhmmmmmm …

NB: Edited!

[peterlaursen]

And one detail more.

A TIMESTAMP defined with

Code:

default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Shall always be left untouched by any INSERT or UPDATE statement.

The idea with this definition is to let the server handle it!

[Ritesh]

peterlaursen wrote on Sep 17 2005, 07:52 AM:

There is a problems with strings.  Does an empty cell mean NULL or empty string? 

It means empty string. You have to explicitely specify NULL if you want to put NULL value in a field.

[LetsSurf]

I just bought a copy of SQLYog to find this stupid bug in it. I also find posts going back along time with regards to it and yet no fix. Why not ??

[Ritesh]

http://www.webyog.com/faq/5_20_en.html

[peterlaursen]

The 'workaround' to this for the next few weeks is to work from RESULT and not the DATA-pane.

Start issuing a statement like

Code:

SELECT column_1, column_2, … , column_n etc.

The SELECT statement should include all columns that you want to edit and omit all columns having defaults that you don't want to overwrite.

Now you can UPDATE etc. from RESULT-pane and the SQL generated by SQLyog when saving will only write to the columns and rows that were included in the original SELECT statement.

[Author]

Posts