Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"

{| Border=1

{| Border=1

|-

|-

|Hello everyone,  

|Hello everyone,  

|-

|-

|Welcome to the tutorial on ''' 'Connecting to a MySQL Database' '''

|Welcome to the tutorial on ''' 'Connecting to a MySQL Database' '''

|-

|-

|In this tutorial, we will look at  

|In this tutorial, we will look at  

|-

|-

|Configuring '''MySQL''' server properties  

|Configuring '''MySQL''' server properties  

|-

|-

|Starting the '''MySQL''' server  

|Starting the '''MySQL''' server  

|-

|-

|Creating and connecting to the '''database'''

|Creating and connecting to the '''database'''

|-

|-

|Creating '''database''' tables, under which we will explore two methods:  

|Creating '''database''' tables, under which we will explore two methods:  

|-

|-

|using the sql editor,  

|using the sql editor,  

|-

|-

|using the create table dialogue and, finally,  

|using the create table dialogue and, finally,  

|-

|-

| Running an '''SQL''' script  

| Running an '''SQL''' script  

|-

|-

|For this demonstration, I am using the '''Linux''' Operating System '''Ubuntu v12.04,'''  

|For this demonstration, I am using the '''Linux''' Operating System '''Ubuntu v12.04,'''  

|-

|-

|and ''' Netbeans IDE v7.1.1'''

|and ''' Netbeans IDE v7.1.1'''

|-

|-

|You also need, the ''' Java Development Kit (JDK) v6'''

|You also need, the ''' Java Development Kit (JDK) v6'''

|-

|-

|and ''' MySQL database''' server  

|and ''' MySQL database''' server  

|-

|-

|To learn this tutorial, basic understanding of ''' database''' management is necessary.  

|To learn this tutorial, basic understanding of ''' database''' management is necessary.  

|-

|-

|To know more, watch ''' PHPandMySQL''' spoken tutorials on the link shown.  

|To know more, watch ''' PHPandMySQL''' spoken tutorials on the link shown.  

|-

|-

|Other standard programming terminologies have been used in this tutorial.  

|Other standard programming terminologies have been used in this tutorial.  

|-

|-

|This tutorial demonstrates how to setup a connection to a ''' MySQL database''' from the ''' Netbeans IDE.'''

|This tutorial demonstrates how to setup a connection to a ''' MySQL database''' from the ''' Netbeans IDE.'''

|-

|-

|Once connected, we will work with '''MySQL''' in the ''' IDE's Database ''' Explorer.  

|Once connected, we will work with '''MySQL''' in the ''' IDE's Database ''' Explorer.  

|-

|-

|Let us switch to the ''' IDE''' now.  

|Let us switch to the ''' IDE''' now.  

|-

|-

| ''' Netbeans IDE''' comes bundled with support for the ''' MySQL RDBMS.'''  

| ''' Netbeans IDE''' comes bundled with support for the ''' MySQL RDBMS.'''  

|-

|-

|Before you access ''' MySQL''' database server in '''Netbeans,''' you must configure the '''MySQL server''' properties.  

|Before you access ''' MySQL''' database server in '''Netbeans,''' you must configure the '''MySQL server''' properties.  

|-

|-

|Right-click the ''' Databases''' node in the '''Services''' window.  

|Right-click the ''' Databases''' node in the '''Services''' window.  

|-

|-

|Choose ''' Register MySQL Server''' to open the '''MySQL server properties''' dialogue box.  

|Choose ''' Register MySQL Server''' to open the '''MySQL server properties''' dialogue box.  

|-

|-

|Confirm that the server host name and the port are correct.  

|Confirm that the server host name and the port are correct.  

|-

|-

|Notice that the '''IDE''' enters '''localhost''' as the default server host name.  

|Notice that the '''IDE''' enters '''localhost''' as the default server host name.  

|-

|-

|3306 is the default server port number.  

|3306 is the default server port number.  

|-

|-

|Enter the '''Administrator Username''' if not displayed

|Enter the '''Administrator Username''' if not displayed

|-

|-

|On my system, the '''Administrator username''' is '''root'''

|On my system, the '''Administrator username''' is '''root'''

|-

|-

|Enter the '''Administrator''' password.  

|Enter the '''Administrator''' password.  

|-

|-

| On my system, the password is blank.  

| On my system, the password is blank.  

|-

|-

|Click the ''' Admin Properties''' tab at the top of the dialog box.  

|Click the ''' Admin Properties''' tab at the top of the dialog box.  

|-

|-

|This allows you to enter information for controlling the '''MySQL server.'''  

|This allows you to enter information for controlling the '''MySQL server.'''  

|-

|-

|In the ''' Path/URL to admin tool:''' field,  

|In the ''' Path/URL to admin tool:''' field,  

|-

|-

|type or browse to the location of your ''' MySQL Administration''' application.  

|type or browse to the location of your ''' MySQL Administration''' application.  

|-

|-

|On my system, the location to the tool is ''' /usr/bin/mysqladmin'''

|On my system, the location to the tool is ''' /usr/bin/mysqladmin'''

|-

|-

|Type any arguments for the admin tool in the '''Arguments''' field.  

|Type any arguments for the admin tool in the '''Arguments''' field.  

|-

|-

|This can also be left blank.  

|This can also be left blank.  

|-

|-

|In the '''Path to start command: ''' field  

|In the '''Path to start command: ''' field  

|-

|-

|type or browse to the location of the ''' MySQL''' start command.  

|type or browse to the location of the ''' MySQL''' start command.  

|-

|-

|On my system it is ''' /usr/bin/mysqld_safe'''

|On my system it is ''' /usr/bin/mysqld_safe'''

|-

|-

|Type any arguments for the start command in the '''Arguments''' field.  

|Type any arguments for the start command in the '''Arguments''' field.  

|-

|-

| Here, I will type ''' -u space root space  start'''

| Here, I will type ''' -u space root space  start'''

|-

|-

|In the '''Path to stop command: '''

|In the '''Path to stop command: '''

|-

|-

|type or browse to the location of the '''MySQL''' stop command.  

|type or browse to the location of the '''MySQL''' stop command.  

|-

|-

|  This is usually the path to ''mysqladmin''' in the '''bin''' folder of the '''MySQL''' installation directory.  

|  This is usually the path to ''mysqladmin''' in the '''bin''' folder of the '''MySQL''' installation directory.  

|-

|-

   

   

|On my system this is ''' /usr/bin/mysqladmin'''

|On my system this is ''' /usr/bin/mysqladmin'''

|-

|-

|If the command is '''mysqladmin,''' in the '''Arguments''' field, type '''-u space root space stop.'''  

|If the command is '''mysqladmin,''' in the '''Arguments''' field, type '''-u space root space stop.'''  

|-

|-

|When finished, the '''Admin Properties''' tab should resemble what is shown on the screen.  

|When finished, the '''Admin Properties''' tab should resemble what is shown on the screen.  

|-

|-

   

   

|Click '''OK. '''

|Click '''OK. '''

|-

|-

|First ensure that the '''MySQL database''' server is running on your machine.  

|First ensure that the '''MySQL database''' server is running on your machine.  

|-

|-

|The '''MySQL''' server node in the '''Service''' window indicates whether the '''MySQL database''' server is connected.  

|The '''MySQL''' server node in the '''Service''' window indicates whether the '''MySQL database''' server is connected.  

|-

|-

|After making sure that it is running, right-click the '''Databases >> MySQL server''' node and choose '''Connect. '''

|After making sure that it is running, right-click the '''Databases >> MySQL server''' node and choose '''Connect. '''

|-

|-

|When expanded, the '''MySQL''' server node displays all the available '''MySQL databases.'''  

|When expanded, the '''MySQL''' server node displays all the available '''MySQL databases.'''  

|-

|-

|A common way of interacting with '''databases''' is through an '''SQL Editor. '''

|A common way of interacting with '''databases''' is through an '''SQL Editor. '''

|-

|-

| '''Netbeans''' has a built-in '''SQL Editor''' for this purpose.  

| '''Netbeans''' has a built-in '''SQL Editor''' for this purpose.  

|-

|-

|You can access this by right-clicking on the connection node.  

|You can access this by right-clicking on the connection node.  

|-

|-

| Let us now create a new '''database''' instance using the '''SQL Editor'''.

| Let us now create a new '''database''' instance using the '''SQL Editor'''.

|-

|-

|In the '''Services''' window, right-click the '''MySQL''' server node and choose '''Create Database'''.  

|In the '''Services''' window, right-click the '''MySQL''' server node and choose '''Create Database'''.  

|-

|-

|In the '''Create Database dialogue''' , type the name of the new '''database. '''

|In the '''Create Database dialogue''' , type the name of the new '''database. '''

|-

|-

|I will name this '''mynewdatabase.'''

|I will name this '''mynewdatabase.'''

|-

|-

|You can also grant full access to a given user.  

|You can also grant full access to a given user.  

|-

|-

|By default, only the admin user has the permissions to perform certain commands.  

|By default, only the admin user has the permissions to perform certain commands.  

|-

|-

|The drop-down list allows you to assign these permissions to a specified user.  

|The drop-down list allows you to assign these permissions to a specified user.  

|-

|-

|It is a good practice to grant users most permissions, except to drop tables.  

|It is a good practice to grant users most permissions, except to drop tables.  

|-

|-

|And allow users to modify only those '''databases''' that are created by their application.  

|And allow users to modify only those '''databases''' that are created by their application.  

|-

|-

|But for now, we will leave the checkbox unselected.  

|But for now, we will leave the checkbox unselected.  

|-

|-

|Click '''OK. '''

|Click '''OK. '''

|-

|-

|Let us now create tables, populate them with data, and modify the data maintained in tables.  

|Let us now create tables, populate them with data, and modify the data maintained in tables.  

|-

|-

| '''mynewdatabase''' is currently empty.  

| '''mynewdatabase''' is currently empty.  

|-

|-

|Let us explore the first method to input data in the tables.  

|Let us explore the first method to input data in the tables.  

|-

|-

|In the '''Database''' explorer, expand the '''mynewdatabase''' connection node.  

|In the '''Database''' explorer, expand the '''mynewdatabase''' connection node.  

|-

|-

|There are three sub folders:  

|There are three sub folders:  

|-

|-

|''' Tables, Views'''  and '''Procedures. '''  

|''' Tables, Views'''  and '''Procedures. '''  

|-

|-

|Right-click the '''Tables''' folder and choose '''Execute Command. '''

|Right-click the '''Tables''' folder and choose '''Execute Command. '''

|-

|-

|A blank canvas opens in the ''' SQL Editor''' in the main window.  

|A blank canvas opens in the ''' SQL Editor''' in the main window.  

|-

|-

|Let us type a simple query in this ''' SQL''' editor.  

|Let us type a simple query in this ''' SQL''' editor.  

|-

|-

|I have now typed a simple query in the '''SQL''' editor.  

|I have now typed a simple query in the '''SQL''' editor.  

|-

|-

|This is a table definition for the ''' Counselor''' table we are about to create.  

|This is a table definition for the ''' Counselor''' table we are about to create.  

|-

|-

|To execute this query, either right-click the '''Run SQL''' icon in the task bar at the top.   

|To execute this query, either right-click the '''Run SQL''' icon in the task bar at the top.   

|-

|-

|Or right-click within the '''SQL Editor''' and choose '''Run Statement.'''

|Or right-click within the '''SQL Editor''' and choose '''Run Statement.'''

|-

|-

   

   

|The '''IDE''' generates the '''Counselor''' table in the '''database. '''

|The '''IDE''' generates the '''Counselor''' table in the '''database. '''

|-

|-

   

   

|You can see this message in the '''Output''' window,  

|You can see this message in the '''Output''' window,  

|-

|-

|which says that the command was executed successfully,  

|which says that the command was executed successfully,  

|-

|-

|To verify these changes, right-click the '''Tables''' node in the '''Database''' Explorer.  

|To verify these changes, right-click the '''Tables''' node in the '''Database''' Explorer.  

|-

|-

|Choose '''Refresh.'''

|Choose '''Refresh.'''

|-

|-

|This updates the current status of the specified '''database. '''

|This updates the current status of the specified '''database. '''

|-

|-

|The new '''Counselor''' table now displays under the '''Tables''' option.  

|The new '''Counselor''' table now displays under the '''Tables''' option.  

|-

|-

|If you expand the table node, you can see the columns that you created.  

|If you expand the table node, you can see the columns that you created.  

|-

|-

|Let us now explore the next method to input data in the tables,  

|Let us now explore the next method to input data in the tables,  

|-

|-

|i.e. Using the Create Table Dialog  

|i.e. Using the Create Table Dialog  

|-

|-

|In the '''Database''' Explorer, right-click the '''Tables''' node, and choose '''Create Table. '''

|In the '''Database''' Explorer, right-click the '''Tables''' node, and choose '''Create Table. '''

|-

|-

|The '''Create Table dialogue''' opens.  

|The '''Create Table dialogue''' opens.  

|-

|-

|In the '''Table''' name text field, type '''Subject'''

|In the '''Table''' name text field, type '''Subject'''

|-

|-

|Click on '''Add Column '''

|Click on '''Add Column '''

|-

|-

|In the '''Add Column dialogue''', type '''id''' in the '''Name''' field.  

|In the '''Add Column dialogue''', type '''id''' in the '''Name''' field.  

|-

|-

|Choose '''SMALLINT''' for data-type from the '''Type''' drop-down menu.  

|Choose '''SMALLINT''' for data-type from the '''Type''' drop-down menu.  

|-

|-

|Select the '''Primary Key''' checkbox in the '''Add Column dialog''' box.  

|Select the '''Primary Key''' checkbox in the '''Add Column dialog''' box.  

|-

|-

|This is to specify the primary key for your table.  

|This is to specify the primary key for your table.  

|-

|-

|Note that when you select the '''Key''' check box, the '''Index''' and '''Unique''' check boxes are automatically selected;  

|Note that when you select the '''Key''' check box, the '''Index''' and '''Unique''' check boxes are automatically selected;  

|-

|-

|Also the '''Null''' check box is deselected.  

|Also the '''Null''' check box is deselected.  

|-

|-

|This is because primary keys are used to identify a unique row in the '''database. '''

|This is because primary keys are used to identify a unique row in the '''database. '''

|-

|-

|Click OK.  

|Click OK.  

|-

|-

|Repeat this procedure to add the remaining columns, as shown on the screen.  

|Repeat this procedure to add the remaining columns, as shown on the screen.  

|-

|-

|We have now created a table named '''Subject''' that will hold data for '''Name, Description,''' and '''Counselor ID'''

|We have now created a table named '''Subject''' that will hold data for '''Name, Description,''' and '''Counselor ID'''

|-

|-

|Click '''OK. '''

|Click '''OK. '''

|-

|-

|By running '''SQL''' queries on a '''database''', we can add, modify and delete data maintained in '''database''' structures.  

|By running '''SQL''' queries on a '''database''', we can add, modify and delete data maintained in '''database''' structures.  

|-

|-

|Let us add a new record to the '''Counselor''' table.  

|Let us add a new record to the '''Counselor''' table.  

|-

|-

|Choose '''Execute Command''' from the '''Tables''' node context menu.  

|Choose '''Execute Command''' from the '''Tables''' node context menu.  

|-

|-

|A new SQL Editor opens in the main window.  

|A new SQL Editor opens in the main window.  

|-

|-

|In the '''SQL Editor''', let us type a simple query:  

|In the '''SQL Editor''', let us type a simple query:  

|-

|-

| To execute this query, right-click within the source editor, and choose '''Run Statement'''

| To execute this query, right-click within the source editor, and choose '''Run Statement'''

|-

|-

|Let us now verify if the new record has been added to the table.  

|Let us now verify if the new record has been added to the table.  

|-

|-

|Right-click the '''Counselor''' table, and choose '''View Data. '''

|Right-click the '''Counselor''' table, and choose '''View Data. '''

|-

|-

|A new '''SQL Editor''' opens in the main window.  

|A new '''SQL Editor''' opens in the main window.  

|-

|-

|A query to select all data from the table is automatically generated.  

|A query to select all data from the table is automatically generated.  

|-

|-

|The results of this statement are displayed in a table view below the workspace.  

|The results of this statement are displayed in a table view below the workspace.  

|-

|-

|Note that a new row has been added with the data we just supplied.  

|Note that a new row has been added with the data we just supplied.  

|-

|-

|We can also run an external '''SQL''' script directly in the '''IDE. '''

|We can also run an external '''SQL''' script directly in the '''IDE. '''

|-

|-

|I have a '''SQL''' query here for demonstrative purposes.  

|I have a '''SQL''' query here for demonstrative purposes.  

|-

|-

|This script creates two tables similar to the ones we have just created.  

|This script creates two tables similar to the ones we have just created.  

|-

|-

|i.e. '''Counselor''' and '''Subject'''

|i.e. '''Counselor''' and '''Subject'''

|-

|-

|Because the script overwrites these tables,  

|Because the script overwrites these tables,  

|-

|-

|we will delete these two tables if they already exist.  

|we will delete these two tables if they already exist.  

|-

|-

|To delete tables, right-click on the '''Counselor''' table  

|To delete tables, right-click on the '''Counselor''' table  

|-

|-

|and choose '''Delete.'''

|and choose '''Delete.'''

|-

|-

|Click '''Yes''' in the '''Confirm Object Deletion''' dialogue box.  

|Click '''Yes''' in the '''Confirm Object Deletion''' dialogue box.  

|-

|-

|Repeat the same for the '''Subject''' table  

|Repeat the same for the '''Subject''' table  

|-

|-

|Now, open the existing '''SQL''' query file from your system.  

|Now, open the existing '''SQL''' query file from your system.  

|-

|-

|From the '''File ''' menu, choose '''Open File.'''

|From the '''File ''' menu, choose '''Open File.'''

|-

|-

|Browse to the location containing this file.  

|Browse to the location containing this file.  

|-

|-

|The script automatically opens in the '''SQL''' editor.  

|The script automatically opens in the '''SQL''' editor.  

|-

|-

|Make sure the connection to '''mynewdatabase''' is selected.  

|Make sure the connection to '''mynewdatabase''' is selected.  

|-

|-

|Check this from the connection drop-down in the toolbar at the top of the editor.  

|Check this from the connection drop-down in the toolbar at the top of the editor.  

|-

|-

|Click the '''Run SQL'''  button in the task bar.  

|Click the '''Run SQL'''  button in the task bar.  

|-

|-

|And the script is executed against the selected '''database. '''

|And the script is executed against the selected '''database. '''

|-

|-

|Right-click the '''mynewdatabase''' connection node and choose '''Refresh. '''

|Right-click the '''mynewdatabase''' connection node and choose '''Refresh. '''

|-

|-

|This updates the '''database ''' component to the current status of the specified '''database. '''

|This updates the '''database ''' component to the current status of the specified '''database. '''

|-

|-

|Right-click on any of these tables now and choose '''View Data.'''

|Right-click on any of these tables now and choose '''View Data.'''

|-

|-

|And below the workspace, you can see the data contained in the new tables.  

|And below the workspace, you can see the data contained in the new tables.  

|-

|-

|In this tutorial you learnt to,  

|In this tutorial you learnt to,  

|-

|-

|configure '''MySQL''' on your computer  

|configure '''MySQL''' on your computer  

|-

|-

|set up a connection to the '''database ''' server from the '''IDE '''

|set up a connection to the '''database ''' server from the '''IDE '''

|-

|-

|create, delete, modify data and  

|create, delete, modify data and  

|-

|-

|run '''SQL''' queries  

|run '''SQL''' queries  

|-

|-

|As an assignment,  

|As an assignment,  

|-

|-

|Create another database instance with tables  

|Create another database instance with tables  

|-

|-

|Populate these tables with necessary data to maintain your personal book library  

|Populate these tables with necessary data to maintain your personal book library  

|-

|-

|And run these SQL statements to view data  

|And run these SQL statements to view data  

|-

|-

|I have created a similar '''database''' which maintains details of my personal movie library.  

|I have created a similar '''database''' which maintains details of my personal movie library.  

|-

|-

|Your assignment should resemble this.  

|Your assignment should resemble this.  

|-

|-

|Watch the video available at the link shown on the screen.  

|Watch the video available at the link shown on the screen.  

|-

|-

|It summarizes the Spoken Tutorial project.  

|It summarizes the Spoken Tutorial project.  

|-

|-

|If you do not have good bandwidth, you can download and watch it.

|If you do not have good bandwidth, you can download and watch it.

|-

|-

|The Spoken Tutorial project team conduct workshops using Spoken Tutorials.  

|The Spoken Tutorial project team conduct workshops using Spoken Tutorials.  

|-

|-

|Gives certificates to those who pass an online test.  

|Gives certificates to those who pass an online test.  

|-

|-

|For more details please write to contact@spoken-tutorial.org

|For more details please write to contact@spoken-tutorial.org

|-

|-

|Spoken Tutorial Project is a part of the Talk to a Teacher Project

|Spoken Tutorial Project is a part of the Talk to a Teacher Project

|-

|-

| It is Supported by the National Mission on education through ICT, MHRD, Government  of India

| It is Supported by the National Mission on education through ICT, MHRD, Government  of India

|-

|-

|More information on this mission is available at link provided here

|More information on this mission is available at link provided here

|-

|-

|This tutorial has been  contributed by  IT for Change  

|This tutorial has been  contributed by  IT for Change  

|-

|-

|Thank you

|Thank you