Category Archives: Databases - Page 6

Pervasive Workgroup Engine v11- Error 7224: User lacks the appropiate rights to apply or delete key (Vista/Win7)

Posted by on March 17, 2011 10 comments

I ran into this problem a little while back. This happens when you try to apply license keys to a Workgroup engine that runs as other user then Administrator on a Vista or Win7 machine. Here is what I did to fix it:

1. Log in to the computer as Local Administrator
2. Shut down the Workgroup Engine (w3dbsmgr.exe)
3. Run the Workgroup Engine as Administrator
4. Start the License Administration as Administrator
5. Enter the license key/s and hit ‘Apply’

After this I finally could add my permanent licenses for Pervasive Workgroup Engine v11 and DataExchange 4.

Backup and restore a MySQL database

Posted by on February 20, 2011 No comments

For this task I normally use the mysqldump program bundled with the mysql database package. It is a niffty little program that lets you backup multiple databases, a selection of tables or just one database to a SQL file. Here is the syntax:

Backup one database:

mysqldump --user=username --password=password --databases dbname > filename.sql

This will backup alla tables of the database dbname to the file filename.sql

Backup multiple databases:

mysqldump --user=username --password=password --databases dbname1 dbname2 dbname3 > filename.sql

This will dump database dbname1, dbname2 and dbname3 to the file filename.sql

Backup all databases:

mysqldump --user=username --password=password -A > filename.sql

The -A option tells the program to dump all databases

Backup tables:

mysqldump --user=username --password=password --databases dbname --tables tablename1 tablename2 > filename.sql

This will backup tables tablename1 and tablename2 from database dbname to file filename.sql

Restore a database: 

mysql --user=username --password=password dbname < filename.sql

Yes, when restoring a dump we use the normal mysql program and not the mysqldump program. This will restore the database/tables in the file filename.sql to the dbname database

Oracle DB syntax cheat sheet

Posted by on February 10, 2011 No comments

When working with many different databases in CLI mode a “cheat sheet” for each is really necessary to keep you from going bonkers. Here is mine for Oracle 10g (from inside SQLPlus):

Show table
Tables you own

select table_name from user_tables;

Tables you own and tables you have been granted select rights on

select table_name from all_tables;

To list all tables you are granted to see

select table_name from dba_tables;

Show primary key of table

SELECT cols.column_name, cols.position, cons.status
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'YOUR_TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

This will show you the name of the key/s, order (if more then one) and status of the key/s

Create tables

create table tablename ( columnname type, columnname type …, primary key(keycolumn,keycolumn,...));

Get table information

DESCRIBE tablename

Alter table
Add column

alter table tablename add(  columnname datatype, ...);

Change datatype of column

alter table tablename modify ( column newdatatype);

Drop column

alter table drop column columnname;

Delete table

drop table tablename;

INSERT

INSERT INTO tablename (column1, column2,... ) VALUES (1, 2,...');

SELECT

SELECT column1, column2,... FROM tablename WHERE column1 = 1 AND column2 = 2 ORDER BY column1, column2,...;

UPDATE

UPDATE tablename SET column1 = 1, column2 = 2,... WHERE column1 = 1 AND column2 = 2 AND ...;

DELETE
Delete all rows

DELETE FROM tablename;

Delete rows

DELETE FROM tablename WHERE column1 = 1 AND column2 = 2, ...

Write query result to file
From inside SQLPlus:

SQL>SPOOL /path/to/file
SQL>SELECT * FROM users;
SQL>SPOOL OFF

The result from the query will now be in the file specified on the fist line