Importing a CSV file into PostgreSQL table

1. Export your data into CSV format. Remember the field delimiter you specify here (I prefer the semicolon) and you must pay attention or change it later if the import process fails. Also uncheck the option for first line as a header so that the CSV file contains only data.

2. If not exists, create a table in PostgreSQL with the data type that can accommodate the data to be imported.

3. Change directory to PostgreSQL binary folder or just type this command if the search path is already listed in your environment paths.

C:\pgsql83\bin>psql -dmydb -Uferry
mydb# COPY dest_table FROM 'C:/myfolder/myfile.csv' USING DELIMITERS ';';
COPY 10537

mydb# COPY another_dest_table FROM 'C:/myfolder/myfile22.csv' USING DELIMITERS ';';
COPY 300024

mydb# \q

See: Export from PostgreSQL to CSV files

My list show only one record after doing edit-close form repeatedly

I was coding a form with a datagridview for single table. All Insert, Update, and Delete operations work well and satisfy me. But I saw a strange behaviour, when I did these:

1. Open the form, Edit some data, then click Save button..(data updated successfully!)
2. Close the form.
3. Do step 1-2 repeatedly.

After n times opening-editing-closing the form, the datagridview only show one record???
My list show only one record after doing edit-close form repeatedly.

public partial class frmCoa : mybasefrm.frmMainGrdView
{
  private MySqlDataAdapter gda;
  private DataTable gdt;
  private DBOPS.SqlHelper;

  private void frmCoa_Load(object sender, EventArgs e)
  {
     gHelper = new DBOPS.SqlHelper();
     gda = gHelper.getDataAdapter("SELECT * FROM akun");
     MySqlCommandBuilder gcmdBldr = new MySqlCommandBuilder(gda);
     gdt = new DataTable();
     gda.Fill(gdt);
     bindSrc0.DataSource = gdt;
     bindNav0.BindingSource = bindSrc0;
     dgv.DataSource = bindSrc0;
     ...do binding
  }

  private void btnSave_Click(object sender, EventArgs e)
  {
     try
     {
        Validate();
        bindSrc0.EndEdit();
        gda.Update(gdt);
        startingEdit(false);
        base.buttonNormal();
     }
     catch (System.Exception ex)
     {
        MessageBox.Show("Error: " + ex.Message);
        return;
     }
     dgv.Enabled = true;
  }
}

.

The form has a Query button, then without closing the application, I did some query to display all or some data, all query work as expected.

Don't know why this happened, and my guess is related to connection and data adapter (Fill method). I disable pooling connection in my connection string, and this seems quite resolve my problem. Any idea?

Backup and restore all database between MySQL instances

I have two instances of MySQL, one using 5.0.27 and the other 5.5.3. My requirement is to transfer all database to a new one. I try to backup all database and restore using My SQL Administration Tools. All database successfully restored (imported) to the new instance, but got problem with privilege that reports Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

Then I look at the backup script but could not found command to create/insert user privilege on mysql database. Probably I missed something, and glad to hear from you. I switch the way, and try following steps:

1. Do backup:
shell5027>mysqldump -uroot -ppass --all-database > C:\temp\sqldataku.sql

Then opened the backup script and found commands to create-insert users privileges on mysql database, something like these:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
DROP TABLE IF EXISTS `db`;
CREATE TABLE `db` ( .... )...;
INSERT INTO `db` VALUES ('%','cms','fercms','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),....);

INSERT INTO `user` VALUES ('localhost','root','*7775476747C6AD73358FA54DAED7828A72014B4E','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('%','fercms','*778F407DE7C65307389FA34AAED7828A72014B4E','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0), ...);

mysql.user table contains user list (with passwords) and their global privileges, mysql.db handles users' database-level privileges.

2. Do restore:
shell553>mysql -uroot -ppass < C:\temp\sqldataku.sql

3. Do flush privilege:
shell553>mysql -uroot -ppass
mysql> flush privileges;
mysql>\q

Test the database using myprogram or tools but come with error message: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

4. Do upgrade, by run this command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\

Looking for 'mysql.exe' as: shell553\mysql.exe
Looking for 'mysqlcheck.exe' as: shell553\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
drupal64.access OK
drupal64.actions OK
fkm.phpbb_acl_groups OK
fkm.phpbb_acl_options OK
Running 'mysql_fix_privilege_tables'...
ERROR 1547 (HY000) at line 167: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 168: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 181: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (cp850). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (cp850_general_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
FATAL ERROR: Upgrade failed

Once again I run the command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\
...
...
Running 'mysql_fix_privilege_tables'...
OK

I did the command again, and reported that "This installation of MySQL is already upgraded to 5.5.3-m3, use --force if you still need to run mysql_upgrade".

If these solve my requirement above (I have to see in future), than I suspect that first try using MySQL Administration Tools should work as well.

SELECT command denied for table 'proc'

By using MySQL Administrator Tool or phpMyAdmin, we could administer users. For example create Esterina as a new user then grant all available privilege to her. One of the privilige granted was run by this command.

GRANT CREATE ROUTINE ON mydb.* TO esterina@ 'localhost';

The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).

On her desk, she open TOAD, create some table then create a procedure with this command:

DROP PROCEDURE IF EXISTS mydb.'tester';
CREATE PROCEDURE mydb.'tester'(IN stat INT)
BEGIN
set @STATUS=stat;
END;

The procedure created successfully, but she could not see the procedure on TOAD GUI. She will receive an error message:

MySQL Database Error
SELECT command denied to user esterina@ 'localhost' for table 'proc';

For her to be able to see her procedure, she must contact her boss, then with super user privilege the adminstrator run this:

GRANT SELECT ON mysql.proc TO esterina@ 'localhost';

Esterina back to her workbench, with tons of plan!

Does SQLite support foreign keys?

Short Answer:
FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers.

A. Foreign Key (FK)
FK is a referential constraint between two tables. A FK is a field(s) that reference to the primary key of another table. The purpose of the FK is to ensure referential integrity of the data. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL).

This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

FK is defined in DDL (Data Definition Language), so we do not code in our application. Once we define FK constraints, they must remain true while inputting/modifying/deleting data in the database. Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity).


B. Enforce Foreign Key Constraint using PRAGMA
The PRAGMA statement is a SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. The foreign_keys pragma controls the enforcement of foreign key constraints in all of the attached databases. If set to off, foreign key constraints are ignored. The default value is off.

For many years SQLite would parse foreign key constraints, but was unable to enforce them. When native support for foreign key constraints was finally added to the SQLite core, enforcement was left off by default to avoid any backwards compatibility issues.

This default value may change in future releases. To avoid problems, it is recommended that an application explicitly set the pragma one way or the other. This pragma cannot be set when there is an active transaction in progress.

1. Parent table: We define Primary Key
CREATE TABLE parentorder (
jmid VARCHAR(16) NOT NULL PRIMARY KEY,
jmdate DATE NOT NULL,
jmsupp VARCHAR(6) NOT NULL);

2. Child table: We define a reference to primary key above
CREATE TABLE childorder (
jm2id VARCHAR(16) NOT NULL CONSTRAINT fk_childorder_id REFERENCES parentorder(jmid),
jm2no VARCHAR(3) NOT NULL,
jm2itemid VARCHAR(6) NOT NULL,
jm2qty INTEGER NOT NULL,
PRIMARY KEY (jm2id,jm2no));

Test them in SQLite
sqlite>select sqlite_version();
3.6.23.1
sqlite> insert into parentorder values ('A1','2010-05-08','SUP1');
sqlite> insert into childorder values ('A1','P','CODE1', 10.5);
sqlite> insert into childorder values ('A1','V','CODE2', 11);

here we try with unexisting key in the primary key of parentorder table.
sqlite> insert into childorder values ('A2','X','CODE3', 12);

We see, FK constraint does not work, because the data A2 successfully inserted! It should raise an error for A2 is not exist in parent table.

Let's continue...with the PRAGMA statement
sqlite> PRAGMA foreign_keys;
0

Here we enforce the foreign key constraint to ON by using PRAGMA statement
sqlite> PRAGMA foreign_keys=1;
sqlite> update childorder set jm2id='A3' WHERE jm2id='A2';
Error: foreign key constraint failed

Look! We could not change A2 to A3, it means the FK constraint works!

sqlite> update childorder set jm2id='A1' WHERE jm2id='A2';
sqlite> select * from childorder;
A1 P CODE1 10.5
A1 V CODE2 11
A1 X CODE3 12

sqlite> select * from parentorder;
A1 2010-05-08 SUP1

sqlite> update childorder set jm2id='A3' WHERE jm2id='A1';
Error: foreign key constraint failed

C. Using Pragma in Program
using C#:
conn = new SQLiteConnection(connString);
conn.Open();
const string sqlString = "PRAGMA foreign_keys = ON;";
SQLiteCommand command = new SQLiteCommand(sqlString, conn); command.ExecuteNonQuery();

D. Trigger
Maintain FK constraints in above scenario might be not simple as it should be, such as enforce FK constraint each time we use database or when using application through connection string, or set the pragma in SQLite command line. For these reason, below we implemet triggers for FK constraints. See more.

-- Foreign Key Preventing insert
CREATE TRIGGER fki_childorder_jm2id_parentorder_jmid
BEFORE INSERT ON [childorder]
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"')
WHERE (SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_childorder_jm2id_parentorder_jmid
BEFORE UPDATE ON [childorder] FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_childorder_jm2id_parentorder_jmid
BEFORE DELETE ON parentorder
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jm2id FROM childorder WHERE jm2id = OLD.jmid) IS NOT NULL;
END;

E. Testing Trigger Code
sqllite>.exit
C:\sqllite3>sqlite tesdb
SQLite version 3.6.23.1

sqlite> copy-paste fki_childorder_jm2id_parentorder_jmid trigger above!
sqlite> insert into childorder values ('A1ERR','P','CODE1', 10.5);
Error: insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fku_childorder_jm2id_parentorder_jmid!
sqlite> update childorder set jm2id='TESTING';
Error: update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fkd_childorder_jm2id_parentorder_jmid!
sqlite> delete from parentorder;Error: delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"

--This pragma returns one row for each foreign key that references a column in the argument table.
sqlite> pragma foreign_key_list('childorder');
0 0 parentorder jm2id jmid NO ACTION NO ACTION NONE


F. Utility
1. Use this utility to generate triggers to enforce foreign key constraints with sqlite, operates on database schema and understands most CREATE TABLE syntax. Win32 and linux x86 binaries included, public domain source code.

2. The triggers code above is generated using this web tool. You could try by copy-paste the two Create Table statement above (B.1 and B.2) then click Submit.


G. Trigger vs Constraint
Constraint is a rule define in DDL statement (Foreign Key, Primary Key, etc).
Trigger is a rule define using DML statement.
Both are useful in special situation.

Using triggers we could define complex processing logic, and also support all of the functionality of constraints; however, DML triggers are not always the best method for a given feature.

Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application. DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.

Each RDBMS has their way for implementation of constraints and triggers, and we also know that there is SQL standards to meet. For constraints and triggers,  I keep these guidance:
-FK constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
-Constraints can communicate about errors only through standardized system error messages.
-DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.
-DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.
-If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Wish that in the next officials release of SQLite, the foreign keys constraints come with default on.

SQLite: ROWID and VACUUM

A. ROWID
Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is called "rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Because rows are stored in rowid order, a rowid is a candidate for a primary key, as it can uniquely identify a record within a table...

Below we see how it works:

1. WITHOUT Primary Key Keyword
sqlite>CREATE TABLE t1 (name TEXT);
sqlite>insert into t1 values ('a');
sqlite>insert into t1 values ('b');
sqlite>insert into t1 values ('c');
sqlite>insert into t1 values ('d');
sqlite>select rowid, name from t1;
rowid name
1 a
2 b
3 c
4 d

sqlite>delete from t1 where name='a';
sqlite>delete from t1 where rowid=3;
sqlite>select rowid, name from t1;
rowid name
2 b
4 d
sqlite>vacuum;
sqlte>select _rowid_, name from t1;
rowid name
1 b
2 d

2. WITH Primary Key Keyword
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, name TEXT);

insert into t2(name) values ('a');
insert into t2(name) values ('b');
insert into t2(name) values ('c');
insert into t2(name) values ('d');

select rowid, pk, name from t2;
pk pk_1 name
1 1 a
2 2 b
3 3 c
4 4 d

delete from t2 where name='a';
delete from t2 where rowid=3;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

sqllite>vacuum;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

B. OBSERVED
- As we saw, the ROWID in the second sample will be retain static!
- So, don't forget to include INTEGER PRIMARY KEY in your Create Table statement.
- Rowid is integer key with amount of 9223372036854775807. Is it enough for you?.
- The rowid value can be accessed using one of the special names "ROWID", "OID", or "_ROWID_".
- If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value.
- The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

C. VACUUM
When an object (table, record, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

Unlock DataGridView properties in Inherited Form

Form inheritance improve our coding time. But I'm facing a problem while inherit a form that contain DataGridView control. I could not modify the DataGridView properties. Eventhough change the modifier property to protected or public. The DataGridView control still locked in designer.

Here a brief step to work with:
1. I add a new class file for example FerDataGrdView.cs in my library objects (mylib.dll).
2. Type this code:
using System.Windows.Forms;
using System.ComponentModel;
namespace Ferryptk
{
[Designer(typeof( System.Windows.Forms.Design.ControlDesigner))]
public class FerDataGrdView : DataGridView { }
}

3. Build library (mylib.dll).
4. Add reference to mylib.dll
5. Drag the control (FerDataGrdView) in ancestor form.
6. Do form inheritance.
7. Now, I could modify the properties of DataGridView control.

O.. happy day..