What?
This is an article to remind me how to modify a column in a database table the old fashioned way (as in stop making me use GUI interfaces so poorly programmed when even I've made better DBMS tools).

All SQL
copyraw
-- Add a column to an existing table (giving it datatype char(2) and allowing NULL)
ALTER TABLE myTable ADD myColumn CHAR(2) NULL

-- Delete a column
ALTER TABLE myTable DROP COLUMN myColumn 

-- Reorder a column
ALTER TABLE myTable MODIFY COLUMN misplacedColumn AFTER otherColumn;
  1.  -- Add a column to an existing table (giving it datatype char(2) and allowing null) 
  2.  ALTER TABLE myTable ADD myColumn CHAR(2) NULL 
  3.   
  4.  -- Delete a column 
  5.  ALTER TABLE myTable DROP COLUMN myColumn 
  6.   
  7.  -- Reorder a column 
  8.  ALTER TABLE myTable MODIFY COLUMN misplacedColumn AFTER otherColumn; 
Category: Databases :: Article: 514

What?
Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

How?
Taken from the best forum for programming Qs&As: http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:


What?
So this is an article to serve as documentation for one of the data sources available to download for FREE from this site.

Why?
It's useful to have a CSV list of all the IP ranges and their allocated country. Our use here was to create a refreshable country by IP address list that we can use when parsing our website logs to check where visitors were from.

Note: we also use and love Advanced Web Statistics (awstats) but this was so that you could have your own country lookup script.

The Download
Should contain the following:
  1. create_countriesipranges_table.sql: SQL to create a pre-populated table into a MySQL database.
  2. iana_ipv4_address_space_registry.csv: comma separated values list of all countries, regions and their respective IP ranges.
  3. ip_lookup.php: to use the database
  4. License.txt: Released under GNU/GPL
  5. ReadMe.txt
What is it?
I wanted my own IP lookup script to tell me the country that is allocated a given IP address, this is what I use.

The scripts in the folder you have downloaded are to create a lookup function using a MySQL database and a PHP script. As well as a CSV file equivalent.


How to use:

  1. Download from my site: «Download»
  2. Run the SQL script against a test database,
    1. Modify the table name to use if necessary.
    2. Uncomment the drop statement if running for the second time.
  3. Modify the PHP file to use database login credentials,
    1. $db_host = "localhost"; // Database host name
    2. $db_user = "my_database_user"; // User for database authentication
    3. $db_pass = "my_database_password"; // Password for database authentication
    4. $db_name = "my_database"; // Database name
    5. $db_table = "mytable_countryipranges";// Database table
  4. Upload the PHP file to your website
  5. Test by using a web browser to visit: http://www.mysite.com/ip_lookup.php?ipv4=123.123.123.123 where www.mysite.com is your website where you uploaded this script. // should return China

Any questions, suggestions?
We are looking to add the ability to generate this data using other sources and in time a region by IP for more accuracy.

Feel free to feedback. It's FREE to download unlike others which is probably why I made the generator in the first place but I do like to make people sign up so I can gauge the level of interest.

What?
This is an article on how to do a restore from backup on a database but when you get the error: "... database is in use".
copyraw
-- SQL Server 2005
EXEC SP_WHO          // details on who is logged in
GO

-- SQL Server 2008
EXEC SP_WHO2         // even more details
GO

-- Run as database owner to see ALL connected processes as well.
  1.  -- SQL Server 2005 
  2.  EXEC SP_WHO          // details on who is logged in 
  3.  GO 
  4.   
  5.  -- SQL Server 2008 
  6.  EXEC SP_WHO2         // even more details 
  7.  GO 
  8.   
  9.  -- Run as database owner to see ALL connected processes as well. 


Why?
For every DBA this is a doddle and doesn't warrant its own article but for those of us who merely use SQL Server Management Studio (SSMS) and Microsoft's SQL Server 2008 R2 for development purposes, the once-in-a-blue-moon restore from backup process is quickly forgotten.

How?
You need to set the database to single-user mode.
Category: Databases :: Article: 491

Applies to:
- MS Windows 7 Enterprise


What?
So I can ping the server the Oracle 11g database sits on. I've installed the Oracle 11g client tools and can connect using sqlplus. But if I tnsping any oracle database, I get something along the lines of:
copyraw
Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n
ot found; No message file for product=NETWORK, facility=TNSMessage 3509 not foun
d; No message file for product=NETWORK, facility=TNS
  1.  Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage 
  2.  3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n 
  3.  ot found; No message file for product=NETWORK, facility=TNSMessage 3509 not foun 
  4.  d; No message file for product=NETWORK, facility=TNS 

Why?
We NEED several instances of Oracle installed as we have different products/services and these run off various instances of Oracle 9i, 10g, and 11g. This article was written for those that need to support these kind of systems and not as an invite for suggestions on how my business should be run.


How?
So there are a variety of solutions that will fix this. However we have certain restrictions in my work environment thanks partly to group policies:
Category: Oracle PL/SQL :: Article: 490

What?
This article isn't unique as there are other tutorials out there. This is meant to detail a process to export a database using MySQL Workbench and then to import it into another database.

Why?
If you're confined to use this product then this is how to do it. Personally, even a web-based app such as PhpMyAdmin would fare better.

How?

What?
This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

Why?
I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

How?
For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change:

What?
So this is a quick article on how to install the mySQL add-on for Oracle SQL Developer v3.0.04.

Why?
I've used various mySQL administration tools to manage mySQL databases over the years, all freeware until someone expresses an interest in using it and then the supplier will implement restrictions and trial based versions. Oracle SQL Developer is currently free at time of print (01-FEB-2013).

I also need to use Oracle databases in my day job so the Oracle SQL developer is already a pre-approved software for our work computers. We have SQL Server Management Studio (SSMS) as well for our SQL Server instances but it wasn't practical to link this to mySQL databases.

How?

What?
This is a quick note on finding the last occurrence of a string in a longer string. This has to be in Transact SQL for a SQL Server instance only and not filtered by other code.


Why?
I have a string such as the following (column positions added for demo purposes):
copyraw
String1.String2.String3.String4
1   5   10   15   20   25   30 -> length = 31
  1.  String1.String2.String3.String4 
  2.  1   5   10   15   20   25   30 -> length = 31 
I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form.

For argument's sake, I'm assigning this string to the variable "haystack".


How?
Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle):
Category: Transact-SQL :: Article: 478

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

need   parameter   license   source   code   server   system   website   first   report   google   work   where   name   create   deluge   windows   time   uploaded   form   case   creator   files   error   added   script   field   function   table   find   page   mysql   would   display   using   file   zoho   client   database   user   order   used   joomla   list   following   data   value   version   note   date   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.