Erm's I.T. Girl – Zelna Ellis

Don't fear when Zel is near…

Install XAMPP Lite on Windows

XAMPP lite is a server software and is perfect for testing a website offline, on your computer.
Download XAmpp Lite.
Note: XAMPP is for developers in a development environment.

  • Open the self-extracting archive.
    XAMPP Install
  • Click Install
    XAMP Install
  • Wait, until the command window opens.
    Desktop Shortcut: Type y and Press Enter.
    Shortcuts
  • Proceed: Type y and Press Enter.
    Proceed
  • Use Drive Letters: Since this is on a local computer, type y and Press Enter.
    Portable?
  • Wait… Press Enter.
    XAMPP Ready
  • Type the correct time zone and Press Enter. You can view PHP’s List of supported time zones here.
    Timezone
  • Launch the Control Panel and select Start next to Apache and MySQL.
    Control Panel
  • You should see that the services is running. You can select Admin to launch it in your web browser or enter the URL http://localhost/ in your browser
    XAMPP Control Panel
  • Once launched in your browser, select your preferred language.
    XAMPP Select Language
  • XAMPP is successfully installed on your computer.
    XAMPP for Windows

There are additional settings you can tweak.
The document directory is "C:\xampplite\htdocs"
Read more about XAMPP for Windows here.
Have Fun!

Advertisements

1 November 2009 Posted by | Software Development | , , , , | Leave a comment

VB.NET Run MySQL Script

To run a MySQL Script in VB.NET has one trick: The command window must stay open while the script is running.

Here is an example of the arguments string to pass to the cmd.exe:
"/K ""mysql.exe"" " & "-h hostname -u username -ppassword < filename.sql"

Explanation of some of the arguments string:
/K = Carries out the command specified by the string but doesn’t terminate – cmd window remains open.
Then the MySQL values:
hostname = MySQL Server hostname
username = MySQL username
password = MySQL user’s password
filename = MYSQL script – must be full path.

Note:
I’ve encountered errors when one doesn’t specify the time in process.WaitForExit().

Code Snippet:

Private Sub RunMySQLScript(ByVal TheArguments As String)
     Try
          Dim proc As New Process
          proc.StartInfo.FileName = "cmd.exe"
          proc.StartInfo.UseShellExecute = False
          'Don’t show command prompt window.
          proc.StartInfo.CreateNoWindow = True
          proc.StartInfo.Arguments = TheArguments
          proc.Start()
          'Time in milliseconds, can change value to wait.
          proc.WaitForExit(1000)
          proc.Close()
     Catch ex As Exception
          MessageBox.Show(Err.Description, "Run MySQL Scripts", _
          MessageBoxButtons.OK, MessageBoxIcon.Error)
     End Try
End Sub

10 October 2009 Posted by | .NET & MySQL | , , , , | 1 Comment

VB.NET & MySQL: Check Status, Start or Stop Windows Service

Well the title says it all. In this example MySQL Server is used to check whether the service exists. If it is disabled it will set the StartupStatus to Manual. Then it will check whether or not the Service is running, and start it if necessary.
You can just replace it with the Windows Service you need to manipulate.

You can download the code in PDF format here.

Note: tsStatus is a ToolStripStatusLabel that is on the form. Tested on Windows XP SP3

Imports System
‘Remember to add references to the following namespaces:
Imports System.ServiceProcess
Imports System.Management

Public Class MySQLCheck

     Private Sub MySQLCheck_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          Try
               Dim mo As New Management.ManagementObject("Win32_Service.Name=’MySQL’")
               tsStatus.Text = "Busy…"
               ‘Check if MySQL Service is installed. If not it will close the form.
               Try
                    mo.Get()
               Catch ex As Exception
                    MessageBox.Show("Need to install MySQL Server", "MySQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Me.Close()
               End Try

               ‘Check if the StartMode = Disabled. If so it will change it to Manual
               If mo("StartMode").ToString = "Disabled" Then
               Dim inParams, outParams As ManagementBaseObject
               Dim result As Integer

               ‘Get an input parameters object for this method
               inParams = mo.GetMethodParameters("ChangeStartMode")
                ‘Set the StartMode to ‘Manual’
               inParams("StartMode") = "Manual"
               outParams = mo.InvokeMethod("ChangeStartMode", inParams, Nothing)
               ‘Check for errors 0 means the request has been accepted.
               result = Convert.ToInt32(outParams("returnValue"))

               ‘Handle errors if any
               If result <> 0 Then
                    Dim myErrMsg As String = ""
                    Select Case result
                         Case 1
                              myErrMsg = "The request is not supported."
                         Case 2
                              myErrMsg = "The user did not have the necessary access."
                         Case 3
                              myErrMsg = "The service cannot be stopped because other services that are running are dependent on it."
                         Case 4
                              myErrMsg = "The requested control code is not valid, or it is unacceptable to the service."
                         Case 5
                              myErrMsg = "The requested control code cannot be sent to the service because the state of the service (Win32_BaseService State property) is equal to 0, 1, or 2."
                         Case 6
                              myErrMsg = "The service has not been started."
                         Case 7
                              myErrMsg = "The service did not respond to the start request in a timely fashion."
                         Case 8
                              myErrMsg = "Interactive process."
                         Case 9
                              myErrMsg = "The directory path to the service executable file was not found."
                         Case 10
                              myErrMsg = "The service is already running."
                         Case 11
                              myErrMsg = "The database to add a new service is locked."
                         Case 12
                              myErrMsg = "A dependency on which this service relies has been removed from the system."
                         Case 13
                              myErrMsg = "The service failed to find the service needed from a dependent service."
                         Case 14
                              myErrMsg = "The service has been disabled from the system."
                         Case 15
                              myErrMsg = " The service does not have the correct authentication to run on the system."
                         Case 16
                              myErrMsg = "This service is being removed from the system."
                         Case 17
                              myErrMsg = "There is no execution thread for the service."
                         Case 18
                              myErrMsg = "There are circular dependencies when starting the service."
                         Case 19
                              myErrMsg = "There is a service running under the same name."
                         Case 20
                              myErrMsg = "There are invalid characters in the name of the service."
                         Case 21
                              myErrMsg = "Invalid parameters have been passed to the service."
                         Case 22
                              myErrMsg = "The account which this service is to run under is either invalid or lacks the permissions to run the service."
                         Case 23
                              myErrMsg = "The service exists in the database of services available from the system."
                         Case 24
                              myErrMsg = "The service is currently paused in the system."

                    End Select
                    Throw New Exception("ChangeStartMode method error code " & result & ControlChars.NewLine & myErrMsg)
               End If
          End If

          ‘Check status of MySQL Server
          ‘If the service is running all is fine
          ‘Else it will wait for the Server to run, or attempt to start the Server
          ‘The status will be updated in the ToolStripStatusLabel tsStatus

          ‘ServiceControllerStatus Meanings
          ‘1 = Stopped – The Service is not running.
          ‘2 = StartPending – The Service is starting.
          ‘3 = StopPending – The Service is stopping.
          ‘4 = Running – The Service is running.
          ‘5 = ContinuePending – The Service continue is pending.
          ‘6 = PausePending – The Service pause is pending.
          ‘7 = Paused – The service is paused.
          Dim sc As New ServiceController("MySQL")
          Select Case sc.Status
               Case 1
                    tsStatus.Text = "MySQL Server is not running, please wait…"
                    sc.Start()
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 2
                    tsStatus.Text = "MySQL Server is starting, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 3
                    tsStatus.Text = "MySQL Server is stopping, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Stopped)
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.Start()
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 4
                    tsStatus.Text = "Ready"
               Case 5, 6, 7
                    tsStatus.Text = "MySQL Server is stopping, please wait…"
                    sc.Stop()
                    sc.WaitForStatus(ServiceControllerStatus.Stopped)
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.Start()
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
          End Select
          Catch ex As Exception
               MessageBox.Show(Err.Description, "MySQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
               Me.Close()
          End Try
     End Sub
End Class

28 September 2009 Posted by | .NET & MySQL | , , , , , , , , , , , , , , , , | 5 Comments

MySQL & SQL_Mode Issues

Sometimes I wonder why I’ve started to do things in MySQL, whilst Oracle PL/SQL give me so little trouble in comparison with MySQL.

O.K. SQL_Mode is not something to throw out of the back-door but I really hope that certain issues will get sorted out in the next version of MySQL. Will have to wait and see?

O.K. the next problem I’ve been running into is to insert the current username into a column in a table, and that is all got to with SQL_mode issues. I like the mode stuff coz it give us better data integrity….

Example Code:
#Loose up on the restriction!

SET @OLD_SQL_MODE=@@SQL_MODE;
DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;
USE TESTIT;
DROP TABLE IF EXISTS customer;
CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
myUserName VARCHAR(50),
lastUserName VARCHAR(50),
PRIMARY KEY (custID)
);
ALTER TABLE customer AUTO_INCREMENT = 1001;
DROP TRIGGER IF EXISTS testCustUpd1;
DELIMITER |
CREATE TRIGGER testCustUpd1 BEFORE INSERT ON customer
FOR EACH ROW BEGIN
SET NEW.creationDate = NOW();
SET NEW.myUserName = USER();
SET NEW.lastUserName = USER();
END;
|
DELIMITER ;
DROP TRIGGER IF EXISTS testCustUpd2;
CREATE TRIGGER testCustUpd2 BEFORE UPDATE ON customer FOR EACH ROW SET NEW.lastUserName = USER();
#LOOSEN STRICT SQL;
SET SESSION SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
INSERT INTO customer (firstName) VALUES ('Angel');
INSERT INTO customer (firstName) VALUES ('Bennie');
SELECT * FROM customer;
UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';
SELECT * FROM customer;
SET SESSION SQL_MODE=@OLD_SQL_MODE;

CONCLUSION
This will still give you a warning but the tables will be updated. Hope we can see these issues resolved in future releases of MySQL.

9 April 2009 Posted by | .NET & MySQL | , , , | Leave a comment

MySQL Date Issues

Oh what a week…
MySQL have a restriction towards dates.
I have 2 columns creationDate and lastUpdate in some tables but:

  • There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
  • You cannot use the function NOW() as a default value for a DATETIME variable.

I wanted both values in my table what now?

SOLUTION:
Set the variable type for creationDate to DATETIME variable type and lastUpdate to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Create a trigger to update the field creationDate.

CODE EXAMPLE:

DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;

USE TESTIT;

DROP TABLE IF EXISTS customer;

CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (custID)
);


ALTER TABLE customer AUTO_INCREMENT = 1001;

CREATE TRIGGER setMyCreationDate BEFORE INSERT ON customer FOR
EACH ROW SET NEW.creationDate = NOW();

INSERT INTO customer (firstName)
VALUES ('Angel');
INSERT INTO customer (firstName)
VALUES ('Bennie');

SELECT * FROM customer;

UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';

SELECT * FROM customer;

Click to view full picture in new tab page:
Use creation date and last update together

CONCLUSION:
Personally I would rather see that MySQL can support the function NOW() as a default value for date variables.

8 April 2009 Posted by | .NET & MySQL | , , , , | Leave a comment

.NET & MySQL – Part 6

MySQL Connection Strings

Software used:

Read more about connection strings at Connectionstrings

One way is to use the Application Configuration File.

  • Right click on the Solution Project Name and select Properties.
  • Select Settings.
    Name: Give it a name
    Type: Choose (Connection String)
    Scope: Application
    Value: You can manually type the values or click on the eclipses to open the dialog box to fill in it. E.g.: server=localhost;user id=jdoe;database=test;encrypt=true;

Application Config
Note:
Only use encrypt=true if SSL is enabled in the MySQL Server and the users SSL options are set.

I recommend that you do not store the password, through a log-in screen you can prompt the user for the password and construct the connection string together with the values of the app.config to connect to the database.

What is also important is that the file must be stored at a secure folder, which is only accessable by the PC Admin.
Typically this would be: C:\Document and Settings\Your User Name\Local Settings\Appication Data\Your Application Name\

Some more options:

  • Write it to the Windows’ registry
  • Write it to a custom file (e.g. XML File)

More suggestions:

  • Encryption/Decryption
    There are various ways you can encrypt the XML File (App.Config file is also in XML Format). For more information read the MSDN Documentation.
  • Hashing
    Passwords are Encrypted and never Decrypted, hence hashing. Once a password is Encrypted and stored in the database, you will never decrypt the retrieved password from the database. If you want to check if the password that the user entered matches, you encrypt the password that the user entered and compare it with the encrypted password stored in the database.

Conclusion
This post has briefly discussed various options regarding security, specifically looking at connection strings.
Besides the connection strings it is important to secure the MySQL Server as well as the Operating System.
I truly believe that no system is "Bullet Proof!" You can only make it more difficult…


Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server
.NET & MySQL Part 3 Install PHP on Windows XP IIS Server
.NET & MySQL Part 4 Setup MySQL Connection String for a Windows application using VB.NET.
.NET & MySQL Part 5 MySQL and OpenSSL.

1 April 2009 Posted by | .NET & MySQL | , , , | Leave a comment

.NET & MySQL Part 3

Install PHP on Windows XP IIS Server

Download guide (PDF)

Prerequisites: IIS (Internet Information Services available on XP CD)
Version: 5.2.9
File Name: php-5.2.9-1-win32-installer.msi
Download Location: http://www.php.net/get/php-5.2.9-1-win32-installer.msi/from/a/mirror
For the latest version check it out at http://www.php.net/downloads/

  1. Run php-5.2.9-1-win32-installer.msi and wait.
    Fig 1
  2. Click Next
    Fig 2
  3. Check I accept the terms in the License Agreement check box, and then click Next
    Fig 3
  4. Change the directory to the D:\PHP\ Click Next
    Note: Do not use recommended directory Program Files, you will run into problems.
    Please take note my root drive is the D Drive, usually it is the C drive.
    Fig 4
  5. Select IIS ISAPI module and click Next
    Fig 5
  6. Expand Extensions and Select MySQL and MySQLi, and then Click Next
    Note: You can select other extensions and/or options you may need.
    Fig 6
  7. Click Install
    Fig 7
  8. Wait
    Fig 8
  9. Click Finish
    Fig 9
  10. Configure IIS to work with PHP

  11. Open IIS Management Console
    Start | Control Panel | Performance and Maintenance | Administrative Tools | Internet Information Services
  12. Select Web Sites in the Left Panel
  13. Right Click your web site in the Right Panel and select Properties.
    Fig 10
  14. Select ISAPI Filters Tab Page
  15. Click Add
  16. Type PHP in the Filter Name Text box.
  17. Browse to the installation directory and select php5isapi.dll
    Fig 11
  18. Click OK
  19. Select Home Directory tab page.
  20. Select the Mappings tab page, and then click the Add button.
  21. Click the Browse button. Browse to the installation directory and change the Files of Type to Dynamic Link libraries (*.dll). Select the file php5isapi.dll and click the Open button
  22. In the Extension text box type .php
  23. In the Limit to: text box type GET, HEAD, POST
  24. Check the Script Engine check box.
  25. Check the Check that file exists check box.
    Fig 12
  26. Click OK
  27. Click OK
  28. Click OK
  29. Important REBOOT your PC before carrying on!
  30. Test PHP

    Test Installation
    Create a new text file called phpinfo.php and paste the following into it. Save it in your wwwroot directory (default is C:\Inetpub\wwwroot )

    <html>
    <head>
    <title>PHP Info<title>
    </head>
    <body>
     <p>This is an HTML line</p>
      <?php
        echo "This is a PHP Line";
      ?>
      <p>
      <?php
        phpinfo();
      ?>
      </p>
    </body>
    </html>

    Open http://localhost/phpinfo.php in a web browser. If you see something like the following, you have successfully installed PHP!
    Fig 13

    When you scroll down you should see the mysql and mysqli extensions.
    Fig 14

    Note: According to some sources mysqli is an improved version of mysql and support some stuff that mysql doesn’t. And mysqli has better security and you can do object oriented programming (OOP).

Happy Coding!

In the next post in this series, we will have a more in-depth look on how to connect a MySQL database in Visual Studio with VB.NET

Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server

Version 1.1

24 March 2009 Posted by | .NET & MySQL | , , , , , , | 2 Comments

.NET & MySQL Part 2

Install MySQL Server on Windows XP

Download guide here (PDF)
Prerequisites:
On the MySQL download page click on “Pick a mirror” for the latest Windows (x86) version (107.4MB). Registering is optional, so just click No thanks, just take me to the downloads! Select a mirror to start downloading.
Version: 5.1.32
File Name: mysql-5.1.32-win32.msi
Download Location: http://dev.mysql.com/downloads/mysql/

  1. Run mysql-5.1.32-win32.msi and wait.
    fig01
  2. Click Next >
    fig02
  3. Select Typical, and then click Next >
    fig03
  4. Click Install
    fig04
  5. Wait
    fig05
  6. Click Next >
    fig06
  7. Click Next >
    fig07
  8. Leave Configure the MySQL Server now option checked. Click Finish.
    fig08
  9. Click Next >
    fig09
  10. Select Standard Configuration and then click Next >
    fig10
  11. Leave Install As Windows Service option checked.
    Leave Launch the MySQL Server automatically option checked
    Also check Include Bin Directory in Windows PATH option and click Next >
    fig11
  12. Leave Modify Security Setting option checked
    Leave Create An Anonymous Account option UNCHECKED.
    Enter a root password. Make sure to remember this password. Click Next >
    fig12
  13. Click Execute
    fig13
  14. Click Finish
    fig14
  15. Open command prompt & type in the following command:
    mysqlshow -u root –p
    Enter the password you supplied earlier. You should see something similar like this figure.
    fig15

In the next post in this series, we will have a more in-depth look on how to configure PHP to work with Microsoft IIS – Internet Information Server 5.1.

Previous Post:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.

Version 1.1

18 March 2009 Posted by | .NET & MySQL | , , , | Leave a comment

.NET & MySQL Part 1

I’m currently working on a project which requires the use of Visual Studio 2008, MySQL Server, some PHP, and a webserver.
Herewith a list of software that is required, recommended or optional at this stage of the game.

Note: The developer machine is running on Windows XP SP3

    Note:

  • MDAC 2.8 is already part of Windows XP
    Recommended:

  • MySQL GUI Tools A single bundle including all GUI tools (MySQL Administrator, MySQL QueryBrowser and MySQL MigrationToolkit)
    Optional

  • phpMyAdmin 3.1.3 An alternative, if you don’t want to use MySQL Administrator
    Recommended:

  • Toad for MySQL A database development and administration tool.

In the next post we will have a more in-depth look on how to install some of the software mentioned above.

Version 1.2

17 March 2009 Posted by | .NET & MySQL | , , , | Leave a comment