Catch the Phillies Phever World Series 2008 Unique Collectibles and T's

Archive for the 'MySQL / MSSQL / Postgre' Category

Rename SQL DB

Wednesday, June 25th, 2008

To rename a MSSQL database, run this code in Web Analyzer::

sp_renamedb >oldname< , >newname<

remember you must first make the database accessible by ONLY  -m “one user”
then you can run this SQL script…

SQL BAK restore

Wednesday, June 25th, 2008

If a backup BAK is provided how to you go about restoring in Web Analyzer?

Here’s how

RESTORE FILELISTONLY FROM DISK
=’D:\Sites\…\***NAME HERE****.bak’

This revealed within your .bak file :
SQLDBName_Data  D:\Data\MSSQL\data\***NAME HERE****_Data.MDF

SQLDBName_Log   D:\Data\MSSQL\data\***NAME HERE****_Log.LDF

Then created all destination directories as desired to reflect intended instruction set of the next string::

RESTORE DATABASE ****NEW NAME HERE****

FROM DISK = ‘D:\Sites\***NAME HERE****_db.bak’

WITH REPLACE, MOVE ***NAME HERE****_Data’
TO
‘D:\Sites\databases\***NAME HERE****_Data.MDF’,

MOVE ‘***NAME HERE****_Log’
TO
‘D:\Sites\databases\data\***NAME HERE****_Log.LDF’, STATS

DBconn config file

Thursday, June 19th, 2008

An application configuration file follows a specific XML schema. The appSettings section is a predefined section of the configuration file designed to make it very easy to retrieve a value based on a given name. This is the easiest way to add application-specific settings into an application configuration file. The appSettings section of the configuration file consists of a series of “add” elements with “key” and “value” attributes. While the appSettings section is predefined, it is not included in a configuration file by default and must be manually added.

A simple example of a configuration file would be the following:

< ?xml version="1.0" encoding="utf-8" ?>

The AppSettings property of the ConfigurationSettings object in the System.Configuration namespace is used to get the settings.

For example, to read either of the settings above,
the following sample code would do the trick:

// Read appSettings string title = ConfigurationSettings.AppSettings[”ApplicationTitle”];

string connectString = ConfigurationSettings.AppSettings[”ConnectionString”];

Now, you could easily set a dynamic title value or read database connection string information from the application configuration file. This gives you the freedom to adjust settings without having to recompile any code. then just pass it into say a SQL connection like so :

SQLConnection cnn = new SQLConnection ( connectString );
// there ya go !

PostgreSQL vs MySQL

Monday, April 14th, 2008

Ask yourself why you are paying so much in licensing costs and annual maintenance when you could use PostgreSQL.

  1. it’s free
  2. you get community support
  3. upgrades are free as well

In terms of standard SQL support, PostgreSQL is very good indeed. If a feature is in the SQL92 standard, you can be pretty sure that PostgreSQL is going to support it correctly.

I advise developers to stay away from extensions to the SQL standard in any case.

The ability to write functions and stored procedures is somewhat limited in PostgreSQL.

The advantages of PostgreSQL are cost and the ability to look at the source code to understand what’s going on. Very few developers will ever make changes to the source or, even better, submit fixes. I do believe the ability to examine the code in order to understand why something doesn’t behave as you expect is a great benefit.

In my opinion, PostgreSQL is enterprise ready. For many uses, PostgreSQL is just as suitable as Microsoft SQL Server or Oracle, but with a big cost advantage. The features that you need 95% of the time are there and work as expected. The underlying engine is very stable and copes well with a good range of data volumes. It also runs on your choice of hardware and operating system, not just whatever some big vendor might insist you buy to run your database .

Of course, it’s not the solution to all database needs, any more than any other vendor’s product would be. For a large, multi-terabyte data warehouse you still need a specialized database product with some advanced features, specifically for handling those kinds of data volumes.

It’s difficult to see why you would use MySQL and PostgreSQL side-by-side. MySQL has historically traded some functionality for performance. For most purposes, you don’t need to make that trade, and PostgreSQL performance is more than adequate.

http://www.postgresql.org/

install

Alias

Friday, December 7th, 2007

sometimes, I get this mixed up trying to draft my methods so I wanted to burn it in my mind by posting about it…

SELECT column FROM table AS table_alias

heres some documentation

JOIN defined

Friday, December 7th, 2007

heres a good key reference I could follow better than most

Select Statement

Friday, December 7th, 2007

The * (is also sometimes called “splat“, probably because it looks like a bug on your windshield) is a shortcut that allows you you ask for everything from the table.

Cart Capture

Thursday, December 6th, 2007

INSERT INTO TablePrefix.TableClass
(

first_name,
last_name,
pay_date,
address,
email,
item_name,
item_number,
memo,
payment_status,
pending_reason,
gross,
fee,
txn_id,
attempts,
sid,
invoice,
refund,
refund_date,
delivery_status

)
VALUES
(

‘Aaron’,'Anderson’,'Y-m-d’,'My Billing Address Here’,'My@PayPal Buyer Email’,'My Item Name’,'Inventory Number’,'Memo VarChar’,'payment_status’,'pending_reason’, ‘Order Gross’,'OrderSHfee’,'txn_id’,'0′,’data1′,’invoice’,'0′,’0000-00-00′,’0′

)

SQL SaveOut DB Snippet

Wednesday, November 14th, 2007

If I want to extract a specific Table from a specific LIVE Database here is how I do it on MSSQL

Here’s how

First of all make sure you have proper permissions and are logged in Windows Authentication is fine. You can only export tables and columns for which you have read security.

Next,

Next on the left Object Browser navigate through the collapsing cascading tree and Left Single Click (LSC) on target tables.

Next Right Single Click (RSC) the selected table

Then you will get this

Next, push play above to execute the query,

(do I need a screen capture of the green triangle really )

Once the query executes, the data will be returned just like in xcel/access LSC the top left marginal 0/0 square to select all data results

and then goto file save As… in the menu.

Save as you like,
voila !

=’red’

This revealed within your .bak file :
SELECT [TargetedTableValueID], [xrefColumn1], [xrefColumn2], [description], [sortorder], [isActive] FROM [Widener_AC_193].[dbo].[ActualLiveTable]

MySQL Installation

Tuesday, October 9th, 2007

First goto

http://dev.mysql.com/downloads/mysql/5.0.html#downloads

Documentation for RHEL 9 Linux reference here :
http://downloads.mysql.com/docs/mysql-linuxunix-excerpt-5.1-en.pdf

I dont like working in RPMs so Download the Tarball…

  1. groupadd mysql
  2. useradd -g mysql mysql
  3. GunZip to /home/install from here
    if it’s a *****.tgz
    run ::
    gzip -d mysql-5.0.67-linux-i686.tar.gz
    then tar it out with ::
    tar xzvf mysql-5.0.67-linux-i686.tar
  4. cd /usr/local/
  5. There will be times when you will want to create “symbolic links” without moving files. This works like creating a shortcut to your desktop. You can do this using the ln command. See the output below:
    ln -s mysql-5.0.67-linux-i686/ mysql
    so in the greatest of sense, MySQL installs to /usr/local/mySQL
  6. cd /usr/local/
    /usr/bin will come before /usr/local/bin in most cases
  7. chown -R mysql mysql
    //change ownership of mysql dir from root to mysql
  8. Next, Add a PATH to your enviromental variables:
    Look ~/.bash_profile, and the scripts in.
    add /usr/local/mysql/bin to PATH ( add : delimiter )
    also check in scripts vi into /etc/profile and /etc/profile.d/
  9. cd /home/INSTALL/mysql-5.0.67-linux-i686

POST-INSTALLATION & TESTING

  1. Next create MySQL Server’s data directory
    and initialize the grant tables:
    ./scripts/mysql_install_db –user=mysql
    use ./bin if mysql_install_db is located in bin directory
    you can now create a datatable with the name test

  2. chown -R root .
    //space period is important
  3. data directory must be owned by mysql.
    chown -R mysql data
  4. Next, Let’s start and run the server:
    To start the server type this:
    cd . ; ./bin/mysqld_safe &
    If the command fails immediately and prints mysqld ended, you can find some information in the host_name.err file in the data
    directory.

PRIVLEDGES & TESTING

  1. If you wish for mysqld to run at boot time ,
    copy support-files/mysql.server to your system startup files
    so, to Begin cd to /usr/local/mysql/
    from there start the daemon process to run MySQL
    ./bin/mysqld_safe –user=mysql &
    adding the & here to run the service in the background
  2. within the above /usr/local/mysql directory perform these next,
    cp -Rp support-files/mysql.server /etc/init.d/
    cp -Rp support-files/mysql.server /etc/init.d/mysql
    chmod +x /etc/init.d/mysql

    ln -s /etc/init.d/mysql.server /etc/rc5.d/
    ln -s ../init.d/mysql.server S98mysql.server
    chkconfig –add mysql
    chkconfig –level 345 mysql on
  3. SET A PASSWORD FOR MySQL ROOT USER
    Alternatively you can run:
    /usr/bin/mysql_secure_installation
    or manually follow below to change the root password if desired
    within the above /usr/local/mysql directory perform these next,
    ./bin/mysqladmin -u wumysql password ‘new-password’
    ./bin/mysqladmin -u
    wumysql -h vanity.domain.tld password ‘new’
    ./bin/mysql –u root –p
  4. Check for all registered users on mysql
    umysql> SELECT Host, User FROM mysql.user;
    so the output looks like:
    +———————-+——+
    | Host | User |
    +———————-+——+
    | 127.0.0.1 | root |
    | my.server.com | root |
    | localhost | root |
    +———————-+——+
    3 rows in set (0.00 sec)
    If you need to run additions to this matrix, heres the code:
    INSERT INTO mysql.user (Host,User) VALUES (’here’,'un’);
    then check your list refreshed again :
    mysql> SELECT Host, User FROM mysql.user;
  1. make sure mysql user has been added to the [mysqld] group in
    vi /etc/my.cnf
    the script block/group should look something like this
    [mysqld]
    datadir=/usr/local/mysql/var
    socket=/var/tmp/mysql.sock
    port=3306
    user=mysql
    [mysql.server]
    basedir=/usr/local/mysql
  2. A good practice is to make a version archive of the mysql.server file to reinstall just in case it may be overwritten by upgrades
    cp -Rp support-files/mysql.server /home/jaaron/SCRIPT_BAK/

STARTING & STOPPING MySQL

  1. changed the top of /etc/init.d/mysql to:
    #! /bin/sh -xyou start the mysqld server on Linux in one of these ways:
    By invoking mysqld directly. This works on any platform.
    mysql -u un -p pw
    stop mysql try mysqladmin shutdown
    Shutdown: support-files/mysql.server stop
    or
    By invoking mysqld_safe, which tries to determine the proper options for mysqld and then runs it with those options. This
    script is used on Unix and Unix-like systems.

    mysql.server start
    ./bin/safe_mysqld -user=mysql &
    mysql.server stop
    ./bin/mysqladmin –u root –p shutdown
    to enable, add these commands to your /etc/rc5.d file
  2. Logging Onto the Server
    ./bin/mysql –u >un< –p >pw<
    WORK IN MySQL to enter your new user’s password
    drop database test;
    use mysql;
    delete from db;
    delete from user where not (host=”localhost” and user=”root”);
    flush privileges;
    update user set user=”sqladmin” where user=”root”;
    flush privileges;
    INSERT INTO mysql.user (Host,User) VALUES (’vanity.domain.tld’,’sqladmin’);
    INSERT INTO mysql.user (Host,User) VALUES (’127.0.0.1′,’sqladmin’);
  3. Finally lastly test the services running by typing ::
    cd mysql-test ; perl mysql-test-run.pl

SQL TABLE MANIPULATION
changed the top of /etc/init.d/mysql to:

  1. you can view your tables by this code:
    use mysql;
    show tables;
  2. the following line helps some bugs for restarting MySQL
    I just do it to cover my bases:
    touch /var/run/mysqld.pid

Other SQL Notes
[mysqld_safe]
log-error=/var/log/mysqld.log which you can  vi into
pid-file=/var/run/mysqld/mysqld.pid
all your databases exist in /var/lib/mysql
ls   /var/lib/mysql/mysql

DataReader Facts

Wednesday, December 27th, 2006

The DataReader represents a set of read-only, forward-only rows from a database. DataReader classes are provider-specific. Use a DataReader when you need fast access and need only to read through the data from beginning to end one time. The following example shows a SqlDataReader:

connection.Open();

SqlCommand command = new SqlCommand(
“select LastName from employees”, connection);
SqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
Console.WriteLine(reader[”LastName”]);
}

connection.Close();

Note the following concepts in the above example:

* A SqlCommand object is used to obtain a SqlDataReader object populated with the LastName column of the Employees table in the Northwind database.
* To use the ExecuteReader method the programmer is required to explicitly open and close the data connection.
* The SqlDataReader.Read() is used to cycle through all the rows in the reader.
o The Read method returns false when there are no more rows to be read.
o Because a DataReader object is forward only, in this example, it is no longer usable after the while loop.

Another thing that the read method does is it checks for the end of file automatically.

New UserConn

Sunday, October 29th, 2006

mysql> create database amarokdb;
//We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:
mysql> grant usage on *.* to amarokuser@localhost identified by ‘amarokpasswd’;
//And finally we grant all privileges on the amarok database to this user:
mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
//And that’s it.
//You can now check that you can connect to the MySQL server using this command:
$ mysql -u amarokuser -p’amarokpasswd’ amarokdb