Use SQLite with Java

In order to use SQLite 3 with Java, you need to do the following steps on your Linux or Windows box. I have deliberately kept off specific commands.

Install SQLite3 from your Linux distribution repository. You can also download the Windows edition if you use Windows. Visit and download the JDBC driver. I downloaded . . . → Read More: Use SQLite with Java

Oracle Data Modeler

Look for it on google. It is a great way to design databases. Added bonus – It creates the scripts to create your database for you.

Dump MySQL tables to text file for backup

$ mysqldump address –add-drop-database –add-drop-table –add-locks –complete-insert –flush-privileges -R –triggers > /home/user/addressdump.mysql

Repeat above for every table in your system. You can also put it in a batch file for a series of tables.

This is the only way to restore databases if you upgrade to MySQL to a major version. This was an exception, . . . → Read More: Dump MySQL tables to text file for backup

/var/lib/mysql folder permissions in Linux

/var/lib/mysql (drwx——-) /var/lilb/mysql/mysql (drwx——) files in this folder should be (rw-rw—-) Same for all databases folders Owner /var/lib # chown -R mysql.mysql mysql

Forgot MySQL password

# su # /etc/init.d/mysql/bin/sqfe_mysqld -user=-mysql –skip-grant-tables –skip-networking& # /usr/local/mysql/bin/mysqladmin -u root flush_privileges password ‘newpassword’ # /etc/init.d/nmysql server restart Now you can use the new root password

Install and setup GTK+ and MySQL in Windows

Install Dev C++ (devcpp) Install gtk-dev-2.8.6.DevPak Install mysql.devPak Install mysql gtk_win32_devel_2.8.6_rc3.exe gtk-2.8.6-runtime.exe

In Dev C++, do the following steps:

Add all of Dev_cpp/ lib to Project->ProjectOptions Add libmysql-lib to above also Copy MySQLLib.dll into windows/system32 and this gets rid of the mysql DLL runtime error. Remove files not recognized during build such as ../lib/jpeg-bcc.lib. . . . → Read More: Install and setup GTK+ and MySQL in Windows

Huge MySQL table. Use index to speed queries

Table has 2500000 records. Normal search takes 38 seconds. After indexing it takes only .16 seconds. > alter table mtp40 add index sn(sort_name, g_srno);

Above creates index sn which gives the query search, above boost. > alter table mtp40 drop index sn; This drops the index ‘sn’ from the above table.

MySQL error in mysql_connect_db()

If you give the mysql_connect_db() statement in many places in your code, then you will get an unknown mysql_error, “Database not selected”.

Small step to prevent SQL injection in MySQL

Use mysql_real_escape_string($string_var) to be used for every string variable.

Eg: $tip_no = mysql_real_escape_string($_POST[‘tip_no’]);

Then build the update string using the result of above statement.

. . . → Read More: Small step to prevent SQL injection in MySQL

Pagination in MySQL

Select * from tips order by tip_no limit 25,4;

where 25 is the start row of the record fetched and 4 is the number of records to be fetched.

. . . → Read More: Pagination in MySQL

May 2017
« Mar