janewilliams20: (geek)
Microsoft Word does not like "denormalisation". It thinks it should be spelt "demoralisation". I see its point, but in this case will sadly have to insist on the more accurate but less descriptive spelling.
janewilliams20: (geek)
When you're doing a UNION in a ColdFusion Query of Queries, that's when. I've met some interesting idiosyncracies in there before, but this is a new one on me.
techy bit )
janewilliams20: (geek)
update mytable
set mycolumn = 1
where mycolumn = 0
;

Easy enough? No.

"Error Code: 1175. To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect."

So I go there, and am told that safe mode prevents updates and deletes with no WHERE clause. A very good idea - but I've got a perfectly good Where clause, so what's the problem?

That error message has more than one line, and it's hard to even know the second is there to see. It says: "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"

A key column? Well, no... and there's no way I sensibly could, for this update.

I don't want to turn off safe mode completely, nor to start & stop. But a bit of Googling comes up with an answer.

SET SQL_SAFE_UPDATES=0;
Do update
SET SQL_SAFE_UPDATES=1;
janewilliams20: (geek)
We have a copy of the main, live DB on another server, for test purposes. It's rather out of date, and could use a refresh. I've got some test tables and data in there that I'd rather not lose until I've checked them, so what I want to do is rename the old copy rather than dropping or over-writing it, then import a new copy from last night's live backup.
how to, how not to )
janewilliams20: (geek)
"Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE"

[[headdesk]] Set a column to default to the current date/time? Say, one called "createddate"? Why would I ever want to do that? It's not as if keeping an audit trail is something any serious DB would ever want to do.....

What you can do is "The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."
But Timestamp is not the same as datetime.... it'll do, but it's limited.

Also, I can either default my "created at" timestamp, OR do a "on update current_timestamp" to store the updated date in another column. Not both. MySQL can only cope with one per table.

aarrgh.... let me back to proper grown-up databases again! Even Microsoft ones!
janewilliams20: (geek)
http://computing.brighttalk.com/upcomingevents

"Next Generation Backup with Deduplication

This Computing web seminar in association with EMC will address the question of what data de-duplication is, how it is performed, and its impact on backup and recovery environments. The topics covered include fixed and variable length segmentation, protecting the integrity of de-duplicated data, flexible deployment options, and the complementary use of virtualisation and replication technologies."

I think I know what it is, and have been doing it, but let's learn more details, and how to do it better. Starting in twenty minutes, but I'll be able to view the recorded version anyway.
janewilliams20: (geek)
I'm not sure I believe this. Using MS Access as a backend DB for a "teach myself" CF site, and my next trick is to move all my existing queries into stored procedures. It's been a while since I used Access, and I couldn't remember how it handled them. After a bit of Googling, I now know why: it doesn't.

Oh, an Access stored proc can exist: I just created one. But you can't do it through the huge, bloated, over-complicated user interface. Not even in SQL edit mode: it looks at "create proc" and decides it's a "create table" with a syntax error. No, you have to attack it through some other interface (ColdFusion and a cfquery tag, in my case) to get the SQL to create the stored proc to run.

They expect this to be taken seriously???

I can feel a CF app for writing and testing Access stored procedures coming on....

or maybe I get tempted into MySQL?
janewilliams20: (geek)
And so I'll put most of it under a cut. I'm moving a bunch of code from PL/SQL to T_SQL: this is about the lack of %ROWTYPE in T-SQL.
Read more... )

Profile

janewilliams20: (Default)
janewilliams20

March 2017

S M T W T F S
   1234
567891011
1213141516 1718
19202122232425
26 2728293031 

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 25th, 2017 05:24 pm
Powered by Dreamwidth Studios