Category: MySQL

Posted on

Inserting multiple records at once into a database table

For demonstration purposes, we are going to assume that we have a table named “employees” with two fields, name and department.

Oracle

insert all
  into employees (name, department) values('Aaron','Accounting')
  into employees (name, department) values('Ibis','IT')
  into employees (name, department) values('Mary','Marketing')
  into employees (name, department) values('Paul','Useless')
select '' from dual;

MySQL, PostgreSQL, and SQL Server

insert into employees (name, department) values
  ('Aaron','Accounting'),
  ('Ibis','IT'),
  ('Mary','Marketing'),
  ('Paul','Useless');
Posted on

Performing MySQL query with case sensitivity

Let us take note of the following example data.

WEBSITE_NAME
dev-notes.com
Dev-Notes.com
DEV-NOTES.com

Note that the first row is in all lower case, the second in proper case, and the third contains all capitalized letters. If we run a normal MySQL select statement to search for “dev-notes” such as the below, the system will return all three records.

/* Standard query; not case sensitive */
select * from mytable where website_name='dev-notes.com';

To get around it, simply use the “binary” keyword before the column name, shown in the example below.

/* Case sensitive query */
select * from mytable where binary website_name='dev-notes.com';

The query above will only return the first record, which is the one that contains all lower case letters.

Posted on

Building an AIM buddy list from your database employee table with VBScript

The code below can be used against Oracle, SQL Server, or MySQL database table to automatically create an AIM buddy list. The SQL statement should select out a “group_name” field (ideas: a department name such as “Accounting” or “Purchasing”, an office location such as “Taipei” or “New York”, etc.) and an “aim_name” field. The “group_name” will be used as AIM groups, while “aim_name” are the users’ actual registered AIM names. The output file is a flat text file in a format that can be imported into AIM.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AIM Buddy List Builder                                        '
'                                                               '
' Description: Builds an AIM buddy list from your database      '
'      employee table.                                          '
' Author: C. Peter Chen                                         '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "hostName"              ' Hostname of the database server
dbName = "dbName"                ' Name of the database/SID
dbUser = "user"                  ' Name of the user
dbPass = "password"              ' Password of the above-named user
outputFile = "c:buddylist.blt"  ' Path and file name of the output CSV file

' SQL statement below; be sure to select out "group_name" and "aim_name" in your SQL statement.
sqlStr = "select department_name as group_name, aim_name from employees_table where aim_name is not null order by department_name, aim_name"
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

dim a, showList, prevGroup

set a = fso.createtextfile(outputFile)
a.writeline ("Config {")
a.writeline (" version 1")
a.writeline ("}")
a.writeline ("User {")
a.writeline (" screenName dummyAimName")
a.writeline ("}")
a.writeline ("Buddy {")
a.writeline (" list {")

set showList = conn.execute(sqlstr)

prevGroup = "placeholder"
do while not showList.eof
	if (showList("group_name") <> prevGroup) then
		if (prevGroup <> "placeholder") then
			a.writeline ("  }")
		end if
		a.writeline ("  """ + showList("group_name") + """ {")
	end if
	a.writeline ("   " + showList("aim_name"))
	prevGroup = showList("group_name")
	showList.movenext
loop
showList.close
set showList = nothing

a.writeline ("  }")

a.writeline (" }")
a.writeline ("}")

' Close
set a = nothing
set fso = nothing
conn.close
set conn = nothing

'You're all done!!  Enjoy the file created.
msgbox("AIM Buddy List Created!")

Interested in obtaining a generic AIM buddy list import file format? Please see below for an illustration with fictional data.

Config {
 version 1
}
User {
 screenName dummyAimName
}
Buddy {
 list {
  "Accounting" {
   MrCFO_fictionalUser
   BobAtAccounting_fictionalUser
   JaneDoe_fictionalUser
  }
  "Purchasing" {
   LewisTheBuyer_fictionalUser
  }
  "Useless Employees" {
   PaulJohnson_fictionalUser
  }
 }
}
Posted on

Recovering from MySQL *.MYI corruption

Personally, I experienced this with the Bugzilla software issue tracking software. The message I got on the browser screen is as follows.

Software error:

DBD::mysql::db selectrow_array failed: Can’t open file: ‘logincookies.MYI’ (errno: 145) [for Statement "SELECT profiles.userid, profiles.disabledtext FROM logincookies, profiles WHERE logincookies.cookie=? AND logincookies.userid=profiles.userid AND logincookies.userid=? AND (logincookies.ipaddr=? OR logincookies.ipaddr=?)"] at Bugzilla/Auth/Cookie.pm line 69

Bugzilla::Auth::Cookie::authenticate(‘Bugzilla::Auth::Cookie’, 5, 4928) called at Bugzilla/Auth/CGI.pm line 106

Bugzilla::Auth::CGI::login(‘Bugzilla::Auth::CGI’, 1) called at Bugzilla.pm line 74

Bugzilla::login(‘Bugzilla’) called at C:/bugzilla/buglist.cgi line 81

For help, please send mail to the webmaster (…@……), giving this error message and the time and date of the error.

To resolve the issue, I first stopped the MySQL database.

C:>net stop mysql
The MySQL service is stopping..
The MySQL service was stopped successfully.

Then, I issued the myisamchk.exe command as below to recover the table that became corrupted; for me, it was the logincookies.MYI file.

C:>c:mysqlbinmyisamchk.exe -r logincookies.MYI
- recovering (with sort) MyISAM-table 'logincookies.MYI'
Data records: 158
- Fixing index 1
- Fixing index 2

Once the recovering process was completed, I restarted the MySQL database.

C:>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

Once the MySQL restart is completed, the database was functioning properly once again, and my Bugzilla system was running without a hitch. Alternatively, I could have logged in to MySQL and issued the following SQL command to repair the corrupted table.

use bugzilla;
repair table logincookies.MYI;
Posted on

MySQL backup and recovery

I am anal with backups. Personally, I probably over-do it with the amount of redundancy, but I think the over-kill is justified. I spend hours and hours each week building up World War II Database, for example, and I do not want to chance a hard drive failure to destroy hours of work. Backup is important. Period.

With that said, it is actually very easy to backup a MySQL database, which is what powers World War II Database in the backend. The following command line illustrates how to create a MySQL database dump file which will contain your latest data.

mysqldump --user=username --password=password --host=localhost db1 --opt > ~/backups/db1dump.sql
mysqldump --user=username --password=password --host=localhost --databases  [db1 db4 db9] --opt > ~/backups/dbsdump.sql
mysqldump --user=username --password=password --host=localhost --all-databases --opt > ~/backups/dballdump.sql

The first example above dumps only one database, “db1”, to the file “db1dump.sql” in the specified directory. The second example illustrates how we can backup data from multiple databases. If we just wish to dump out all data across all database, the third example shows how we can do so.

Once we have chosen the method we want and have the command line we wish to use, we should schedule it to run regularly. For windows, the built-in “Scheduled Task” utility works well for this. For Unix/Linux, “cron” could be used to generate the command line. Do not forget it is probably wise to transfer a copy of your database dump file to a different machine immediately to prevent a hard drive failure wiping out both your database and backups at the same time.

In the dreadful event that our MySQL database goes bad and we are forced to establish a new database machine, we just have to retrieve our most recent database dump file and run the following command on the new install.

mysql --user=username --password=password --host=localhost db1 < db1dump.sql
Posted on

Writing SQL output to CSV with VBScript

Enter your specific information in the “Configuration” section near the top of the script. For the “dbType” variable, the only accepted values are “oracle”, “sqlserver”, or “mysql”. Once this is done, just run the script and you should have your quote-delimited comma-separated CSV file!

The email-related variables are optional. To enable the emailing functionality (send the generated CSV file to the address as an attachment), enter the recipient email address in the box. If you do not wish to email, just leave that variable as empty string (“”), and the other email related variable such as smtp and smtpPort will be ignored.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'	1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject"            ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!"      ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com"          ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25                     ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = "Test Email from vbs"
	objMessage.From = email
	objMessage.To = email
	objMessage.TextBody = "Please see attached file."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")
Proudly powered by WordPress