Database
Group by date from a datetime or timestamp field in Mysql
This is a simple but greatly useful mysql query. You can use it to find all the dates even with time included in datetime or timestamp field.
No CommentsHow to import large csv file to mysql database in seconds
I had faced a troublesome situation while working with SUST automated mobile based admission registration system. The scene was that, Bangladesh education board give us their database of 1 million students personal info and academic results in old MS Foxpro format. It is single file with more than 1 gb in size. It was really causing so much trouble to convert it into mysql database until i reach the solution below. Before finding it we were trying to parse all that 1gb file into sql query using java. In a P4 computer it took nearly 4/5hrs to parse arrount 20K entries as java heap memory was continiously exceeding limit.
No CommentsBackup mysql database using php
When working with the library automation system of our university, we had to develop a system to backup database to sql file. After searchin in the net for a while we found two solution one is using exec to call mysqldump and generate the output file.
But we found another simple php script which will also generate sql output from database. Here is the code.
No CommentsHow to change default port of oracle after installation in ubuntu
When I was installing all apache, tomcat, oracle and mysql in my ubuntu 9.04. Apache took 80 port. Thereafter tomcat took 8080. When I setup oracle-xe I didn’t notice that. But oracle also demands 8080 port by default. That created conflict with tomcat. When I click “Applications > Oracle 10g Express Edition > Go to Database Home page” it showed an tomcat page not found error!!!
It was a really painful coz there was no way to reconfigure port for oracle neither any way to reinstall that would reconfigure. I googled for a while and found a solution.
No CommentsCreating auto increment sequence in oracle
In mysql we often use auto increment feature. But there is no such feature in oracle. Instead we have sequence and we need to use a sequence and a trigger to create auto increment functionality in oracle.
Create table
-- Admin Table CREATE TABLE admin ( id number(4) NOT NULL, name varchar2(50) NOT NULL, username varchar2(20) NOT NULL, password varchar2(20) NOT NULL, constraint admin_pk_1 PRIMARY KEY(id) );No Comments
How to connect to mysql database using ANSI C language
This is a sample program which connect to mysql database using in c programming.
#include<stdio.h>
#include<mysql/mysql.h>
#include<stdlib.h>
int main(int argc, char *argv[])
{
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char *server ="localhost";
char *user = "root";
char *password = "123456";
char *database = "dsc";
conn = mysql_init(NULL);
// Connect to database
if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
{
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
// sql query
if(mysql_query(conn, "select * from dsc_scripts"))
{
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
//output table name
printf("Mysql Tables in mysql database: \n");
while((row = mysql_fetch_row(res)) !=NULL)
printf("%-3s%-30s%-30s\n", row[0], row[1], row[2]);
// close connection
mysql_free_result(res);
mysql_close(conn);
return 0;
}
No CommentsMySQL enum equvalent alternate in oracle
One of Mysqls great features in enum data type. An ENUM is a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.
But in oracle there is no such data type. So if you want to restrict column value input like mysql enum datatype in oracle you can make use of an check constraint.
-- Student Table
CREATE TABLE student
(
reg number(10) NOT NULL,
name varchar2(50) NOT NULL,
dept varchar2(5) NOT NULL,
ses number(4) NOT NULL,
fname varchar2(50),
mname varchar2(50),
bday date,
gender varchar2(6),
cgpa number(4,2),
grade_letter varchar2(2),
constraint student_pk_1 PRIMARY KEY(reg), constraint student_en_1 CHECK(gender IN('Male', 'Female')),
constraint student_fk_1 FOREIGN KEY(dept) REFERENCES dept(code) ON DELETE cascade,
constraint student_fk_2 FOREIGN KEY(ses) REFERENCES ses(ses) ON DELETE cascade
);
Here in line 14 check function is working as like enum data type in mysql
No CommentsHow to implement query limit or pagination in oracle
There is no limit command like mysql in oracle. If you need to limit your query in oracle or want to make a pagination in oracle what you need to do is really rubbish!
You need to make a nested select query like below… uff
SELECT url, title, metadesc FROM (
SELECT url,title, metadesc, rownum rn FROM (
SELECT url, title, metadesc FROM web
)
WHERE rownum <= 60
)
WHERE rn > 50
This query is as same as mysql query like below
SELECT url,title,metaDesc FROM web LIMIT 50,10No Comments
How to connect to oracle database with php
We all are familier with php mysql applications. Now a days there is a very few php applications that uses oracle. Reference on how to connect to php oracle is also much low in number. I had trouble when searching for the solution on net.
To connect to php oracle. First you should enable oci module in your php.ini configuration. Generally it is located at “C:/windows”
So open “php.ini” with a notepad. Find the line
Calling oracle procedures using C#
I have this procedure stored in oracle database.
-- procedure to to calculate student cgpa/grade letter of whole semester
create or replace procedure calc_sem_cgpa(regIn in number, semIn varchar2)
as
cursor result is
select cgpa.gpa, syllabus.credit
from cgpa,syllabus,student
where
cgpa.reg = regIn
and cgpa.semester = semIn
and cgpa.course = syllabus.course
and cgpa.semester = syllabus.semester
and student.reg = cgpa.reg
and student.d ept = syllabus.dept
and student.ses = syllabus.ses;
eachResult result%rowtype;
sum_cgpa number(5,-2) := 0;
sum_credit number(5,2) := 0;
final_cgpa number(4,2) := 0;
countRow number(2) := 0;
begin
open result;
loop
fetch result into eachResult;
exit when result%notfound;
if eachResult.gpa > 0.0 then
sum_cgpa := sum_cgpa + eachResult.gpa * eachResult.credit;
sum_credit := sum_credit + eachResult.credit;
end if;
end loop;
if sum_credit > 0 then
final_cgpa := round(sum_cgpa/sum_credit, 2);
else
final_cgpa := 0;
end if;
select count(*) into countRow from cgpa_sem
where cgpa_sem.reg = regIn
and cgpa_sem.semester = semIn;
if countRow > 0 then
update cgpa_sem set cgpa = final_cgpa, credits_comp = sum_credit
where reg = regIn and semester = semIn;
else
insert into cgpa_sem(reg, semester, cgpa, credits_comp)
values(regIn, semIn, final_cgpa, sum_credit);
end if;
end;
/
No Comments