Technology Bits: May 2010

Friday, May 28, 2010

I have found a great way to use excel for user defined functions

http://blog.mclaughlinsoftware.com/2010/05/28/udf-replaces-vlookup/

I have found a great site that tells how to make a grade lookup in excel without using arrays.

It uses virtual lookups:

'Define a single dimension array of a UDT (record)

Dim VirtualLookup(1 To 15) As VirtualLookup

' Record initialization

VirtualLookup(1).lowerLimit = 0.93

VirtualLookup(1).upperLimit = 1#

VirtualLookup(1).letterGrade = "A"

Then you can get and use the values in the lookups like you would with a vlookup in excel.

In a for loop you do this to make the grade = to the vlookup

grade = VirtualLookup(i).letterGrade

I have also been learning how to make arrays in vba in excel

http://www.anthony-vba.kefra.com/vba/vbabasic3.htm#Declaring_Array_With_Dim_Statement

Tuesday, May 18, 2010

Online Radio for free

I created my own online streaming radio station that allows people to listen to your music as you stream it. So everyone that listens will hear the same thing at the same time. I did this with Windows XP but linux would be even better to use.

I installed Icecast2 which is the program that shows you the radio stations that you are streaming. Make sure to look at the settings first by clicking edit configuration in the configuration menu. Next click the start server button. You can see your music streams by going to for example http://localhost:8000

But once you set up your own VPN or website you can use that ip address to allow anyone to listen. But if you are listening to copyrighted music make sure it requires a password so only you can listen to it. I will add a post sometime about how I got my own website built using dyndns.org for free and can host the website from my own laptop or any other computer using a dynamic ip address.

After I installed Icecast2 I looked on their website and found 3rd party client that will read a playlist of mp3's or ogg file and send them to the Icecast2 service which sends them on to the listeners. I picked Ezstream for the client. All you have to do is make a m3u playlist with a program like VLC. I added as many songs as I wanted to the VLC player then clicked playlist menu and then save playlist to file. I put this file in the Ezstream folder. I also edited the ezstream_mp3.xml file in the example folder and moved it into the root of Ezstream folder instead of the example folder. Next add Ezstream to environment path so you don't have to travel to that folder while in CMD. Then type the command:

ezstream -c ezstream_mp3.xml

This will start the streaming if you did everything right. Then just go to the localhost:8000 address to see all your streams. Click the m3u link to the right to download the m3u playlist that you can open with VLC or any other music player. It will start playing the stream. It took me about 3-4 hours to get everything to work. It would have taken less time if I knew what I was doing.

After getting it to work on localhost I made it work onn my VPN and it works. I also added another xml file for Ezstream that pointed to a different m3u playlist so when someone goes to your port 8000 address page they will see both streams. But you have to have 2 command prompt windows open and do the ezstream -c command using both of your xml files one in each of the cmd windows.

What will I try in the future? I want to use mysql and php with this type of streaming to allow uses to login to a website and they can look through the music that is available and pick music to be qued up next on the radio station. When there is no music qued it will pick a random genre and play the music in that genre for 1 hour and then pick another genre. Too many music stations I know of play a small amount of songs and repeat songs all day. I would want songs to repeat once a week so it doesn't get boring to listen to all day.

MySQL table is full error for InnoDB

I was playing with importing data from csv into a table that is only in memory with InnoDB and it came up with an error:

"table is full"

There was 107,000 records added and it stopped before finishing the import.

-- here is the sql code that I used to make the table

SELECT 'ITEM_IMPORT' AS "Drop Table";

DROP TABLE IF EXISTS item_import;

-- ------------------------------------------------------------------

-- Create ITEM_IMPORT table.

-- ------------------------------------------------------------------

SELECT 'ITEM_IMPORT' AS "Create Table";

CREATE TABLE item_import

( item_barcode CHAR(14) NOT NULL

, item_type INT UNSIGNED NOT NULL

, item_title CHAR(60) NOT NULL

, item_subtitle CHAR(60)

, item_rating CHAR(8) NOT NULL

, item_release_date DATE NOT NULL

, screen_type INT UNSIGNED DEFAULT NULL

) ENGINE=memory;

-- loads the csv into the temporary table

LOAD DATA LOCAL INFILE 'C:/Documents and settings/mike/Desktop/datawarehousing/import.csv'

INTO TABLE item_import

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n';

to fix the limit on the amount of rows in the InnoDB table change or add the following line to the my.ini if you are using windows:

max_heap_table_size = 600M

I tested this and it enabled me to add 4 million rows in the table. Since it is a table in memory it removed the tables data as soon as you exit mysql.

Friday, May 14, 2010

Cool new trick to use to stop system beep

My computer is a Dell inspiron 1521 and the system beep it makes when ever I make an error while typing commands in MySQL are so loud it startles me. I wanted to stop the beeps so I googled it and found this page or here. Hope it helps. All you need to do is use a -b in the mysql sartup command like this:

C:\Documents and Settings\mike>mysql -uuser -ppassword -b

now you can type a bad command and it won't beep at all. Another method to use to stop it until you restart the computer is:

net stop beep

the beeps will now be off.

then use:

net start beep

and the beep starts up again.

If you want it to stop for good then use this line:

sc config beep start= disabled

then use

sc config beep start= auto

to turn it back on