Database Archives - Page 5 of 6

Microsoft SQL Server | MySQL LIMIT equivalent in Microsoft SQL Server

Author - Kailash
|
0 Comments
|

Suppose you want to retrieve values from row from 5 to 10 from a table in SQL server. Since LIMIT keyword does not work in it so we can use any of these 2 alternative methods:

Solution 1 :

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM TableName Read more…

CakePHP bake: SQLSTATE[42S02]: Base table or view not found

Author - Rita

While running cake bake command for cakephp 3.0 we got the following error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘tablename .columnname’ doesn’t exist

The culprit was a column with name test_id in one of the tables. As per bake command’s conventions about foreign Read more…

Selenium | Mass CSV file generation by fetching data from database

Author - Webner
|
0 Comments
|

In a project we are working on there are large number of pdf files for which field mapping data is stored in the database. In this project pdf forms are auto filled with data from Salesforce. Which PDF form field is mapped to which corresponding Salesforce object or field is Read more…

MySQL | A short note on database indexes

Author - Webner
|
0 Comments
|

Index is used for storing values for a specific column in a table. An index is created on a column of a table and used to speed up search queries.

For Example :

Suppose we have a database table called Users with three columns – User_Name, User_Age, and User_Address. Assume that this Read more…

Database Connectivity with Selenium Webdriver

Author - Webner

How to connect to Mysql in Selenium script?

Step 1: Import Mysql connector (JDBC driver):

Download connector from:

Find-ur-pal

Download this jar file and import in Eclipse : MySQL-connector-java-5.1.18-bin.jar

Follow these steps in Eclipse:

Add the downloaded Jar to your Read more…

Mysql Workbench | How to copy Remote database into your local machine

Author - Rita

1.  Select Database: Schema Transfer Wizard from the Mysql workbench menu bar. You will see schema transfer wizard on your screen as below:

Click on Start the Wizard option.

2.  Connection Selection: Now select your source and destination location to copy database:

If you want to Read more…

MySQL | Insert multiple records and update if record(s) already exist in a single query

Author - Ishpreet Kaur
|
0 Comments
|

In MySQL, we generally perform insert, update, select and delete operations on records of table. Multiple Insert operation can also be easily performed on table.

Take an example of Employees table with fields:

Multiple records can be inserted in Employees table in a single query using following MySQL query :

INSERT Read more…

MySQL | Upgrade MySQL 5.5 to 5.6 in Ubuntu 14.04

Author - Webner

MySql 5.5 is the default version for Ubuntu 14.04

Steps to Upgrade the mysql-server

If you have existing database in Mysql 5.5. It should be migrated automatically.

Take the backup of existing databases:

mysql> mysqldump –lock-all-tables -u root -p -all-databases > backup Read more…

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

Author - Webner
|
0 Comments
|

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
|
0 Comments
|

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
|
0 Comments
|

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
|
0 Comments
|

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
|
0 Comments
|

We have data as below:

We need output like this:

Solution:
For SQL server
STUFF function is the key here:
SELECT Id,STUFF(
(
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…