Database Archives - Page 5 of 6

MySQL | Find if particular value is selected in multi pick list

Author - Webner

In simple pick list we can select only one value but in multi pick list we can select one or more values.

Scenario: We had to multi pick list data type field

Fields that we wanted to select :

1. Field_1
2. Field_2
3. Field_3
4. Field_4

Case 1 : Check if Read more…

Maintaining record history in database

Author - Webner

When you are building a web-based multi/single tenant system or a desktop system which has a database at its backend to maintain records, keeping the history of records proves to be very useful in the long run from information and security perspectives. History of records means keeping track Read more…

How to use SimpleSAMLPhp as a Service Provider for authenticating users?

Author - Ishpreet Kaur

How to use SimpleSAMLPhp as a Service Provider for authenticating users? Some Basic steps to start using simplesamlphp.

1. Download stable version of SimpleSAMLphp from this link:

Click here

2. Go to the directory or your workspace where you want to install simpleSAMLphp, extract the zip file. Give Read more…

Ubuntu 14.04 | Installing postgresql 9.4

Author - Webner

Follow steps below to install Postgresql 9.4 (instead of 9.3 for which sources are added by default in Ubuntu 14.04):

1.  Remove the previous PostgreSQL Version:

*Make sure that you firstly backup your databases.
$ sudo apt-get –purge remove postgresql-*
2.  Add Repository:
$ sudo add Read more…

java.sql.SQLException: Data truncated for column date at row 1

Author - Webner

You may face this exception while trying to insert data in a mysql table using hibernate:

java.sql.SQLException: Data truncated for column date at row 1

While there can be different reasons of this problem, in our case it was occurring because by mistake we were trying to insert Read more…

Java | Execute SQL script using Maven

Author - Rita

Whenever there is a change in the database whether we are altering database structure or adding/updating data inside database most of the times we create a script file and run it manually. We can automate this process using Maven.

Solution: Maven provides a plugin called flyway-maven-plugin to Read more…

MySQL | How to automatically update timestamp field

Author - Webner

Sometimes, we need to check when was the data last accessed. In MySQL, we had taken a field last_modified_time as timestamp datatype in a table, which was set to CURRENT_TIMESTAMP by DEFAULT.

When a new record was inserted, it automatically updated the field and set the current datetime into this Read more…

Eclipse | Ctrl+click doesn’t redirect to parent function or class

Author - Webner

While working in eclipse, we faced a problem in which Ctrl+click was not redirecting to the parent function or class.

Solution: To solve this problem, we followed the below steps :

1. Exit Eclipse
2. Go to Workspace
3. Open .metadata folder
4. Open .plugins folder
5. Search for folder Read more…

Mysql | How to access database remotely

Author - Kiran Preet

To access Mysql database remotely a good way is to create a new Mysql user and grant it permissions to access database from remote, as explained below:

You can create user with two ways

From Mysql command prompt:

1. Open command prompt.

2. Open the terminal of MYSQL Read more…

SQL Server / MySQL | Combine data from multiple rows into a single row

Author - Webner

We have data as below:

We need output like this:

For SQL server
STUFF function is the key here:
SELECT distinct ‘ ‘ + cast(Name as varchar(500)) from table1 t2 where t1.Id = t2.Id FOR XML PATH(”), TYPE ).value(‘.’, ‘NVARCHAR(MAX)’) ,1 Read more…

Mysql | Maximum execution time of 360 seconds exceeded at data import time

Author - Webner

In one of the project we had to import some .csv files into Mysql database. Files were of size 80mb and more. When we tried to upload the files through phpmyadmin it was resulting in timeout error – Maximum execution time of 360 seconds exceeded (see image below):

A better solution Read more…

Joomla- Database Error: Unable to connect to the database

Author - Webner

While working with Joomla if you get an error – “Database Error: Unable to connect to the database:Could not connect to database”, check following properties in Joomla’s configuration.php file :

//given values are sample data only.

var $dbtype = ‘mysql’;

var $host = ‘localhost’;

var $user = ‘user_jo151’;

var $password = ‘YourPassword’;

var Read more…

Moodle | Fetch all the categories based on the programs assigned to a user

Author - Neha Mishra

Below are the Moodle database tables that will be used to fetch all the categories based on the programs assigned to a user (complete query is also written after table information):
1. mdl_prog : This table contains each program along with the category to which program belongs, its summary, fullname and Read more…

Mysql | Forgot root password in Mysql

Author - Navneet Kashyap

If you forget mysql root password these are few easy steps to regain access:
1. shell> mysqld_safe –skip-grant-tables
2.Open another terminal shell> mysql -u root -p (hit enter and use blank password)
3. mysql>UPDATE mysql.user SET Password=PASSWORD(‘NewPassword’)WHERE User=’root’;

4. mysql>FLUSH Read more…

Couple of approaches to generate test-data in bulk

Author - Rita

Sometimes you need to fill a database table with thousands of test data records. You may also need to generate test data in the form of csv file to feed to a program. How can you generate such a large set of data in short time?
One approach to generate Read more…

Moodle | Call to undefined method mysqli_native_moodle_database :: insert_records()

Author - Manju Kashyap

We were using insert_records() function to insert multiple records in a Moodle database table but it was not working.

We checked for errors by adding following lines in config.php file of moodle:
@error_reporting(E_ALL | E_STRICT);

@ini_set(‘display_errors’, ‘1’);

$CFG->debug = (E_ALL | E_STRICT);

$CFG->debugdisplay = 1 Read more…

SQL Server not starting at startup and even not starts manually

Author - Webner

In one case we noticed that SQL Server (SQL Server 2014) service was scheduled to start Automatically on system boot but still it was not starting up. It did not even start when we manually tried to start the service. We got following errors:

Error1: A timeout was reached (30000 Read more…

Mysql | How to drop table having dot (.) in the table name in MySQL

Author - Rita

We had a table in our database having dot in the table name as below:

Table name – kpc.product_categories

On trying to delete kpc.product_categories using below command:

drop table kpc.product_categories

It used to give error that no such table exists.

Dot(.) is the reserved symbol in mysql so Read more…

Heroku | How to delete prior database backups

Author - Namisha

Following are the steps to delete the Postgres database backup:

1. Login into heroku.

2. Click on the database>PG Backups. Here you can see all the PG backups list.

3. . For deleting the database, first of all you have to install the toolbelt.

You can download Toolbelt from Read more…

Zoho CRM | Reduce API call consumption

Author - Webner

This is a simple idea to reduce API calls consumption when you pull data from Zoho CRM into an integrated piece of code (like server side code in PHP or Java that connects to Zoho CRM to retrieve records). In one of the projects we were working on, we had Read more…