MySQL - SQL INSERT Stored Procedure

MySQL Stored Procedure - INSERT - Example

Source code to create and add "sql insert stored procedure" to catalog

The following example is for creating a simple insert stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like dbOrchestra.

DROP PROCEDURE IF EXISTS `sp_students_INSERT_byPK`
GO

CREATE PROCEDURE sp_students_INSERT_byPK
(
IN p_student_id INT(11) ,
IN p_password VARCHAR(15) ,
IN p_active_flg TINYINT(4) ,
IN p_lastname VARCHAR(30) ,
IN p_firstname VARCHAR(20) ,
IN p_gender_code VARCHAR(1) ,
IN p_is_on_staff TINYINT(4) ,
IN p_birth_dttm DATETIME
)
BEGIN

INSERT INTO students
(
student_id ,
password ,
active_flg ,
lastname ,
firstname ,
gender_code ,
is_on_staff ,
birth_dttm
)
VALUES
(
p_student_id ,
p_password ,
p_active_flg ,
p_lastname ,
p_firstname ,
p_gender_code ,
p_is_on_staff ,
p_birth_dttm
) ;
END

GO

You will note that just like in a INSERT statement you do not have to use all of the columns available when creating a stored procedure. You must however populate all columnar data associated with the PK (primary key), and columns associated with unique indexes (note: there are exceptions to this, but they will not addressed here), and columns defined in the ddl as "NOT NULL".

Executing the sql insert stored procedure
Execute insert stored procedure

To run the insert stored procedure you need to supply a value to the student_id variable as well as populate all required data. In this example I have included a cross section of columns for your reference, as well as the datatype associated with the columns in our SQL INSERT.

/***
IN p_student_id INT(11)
IN p_password VARCHAR(15)
IN p_active_flg TINYINT(4)
IN p_lastname VARCHAR(30)
IN p_firstname VARCHAR(20)
IN p_gender_code VARCHAR(1)
IN p_is_on_staff TINYINT(4)
IN p_birth_dttm DATETIME
***/

CALL sp_students_INSERT_byPK
(
25 ,
'mydogSpot1' ,
1 ,
'Bag' ,
'James' ,
'M' ,
1 ,
'1942-10-11'
)
GO

A few thing to point out regarding the code above. Datetime and character data is entered using single quoted string. Integer data is entered without quotes. I also included all columns that were defined as NOT NULL. I also included the password column which is part of a unique index. You will note that I did not include columns for some of the non-unique indexes (soc_sec_num, other_id_num, and driver_license_num). I did this intentionally to demonstrate that they are not required columns. Having said this, in the "real world" one would only put indexes on columns where the intent was to actually collect the data for that column. I just wanted to make a technical point here. The pragmatic point is that you would want to expose columns that are part of indexes.

What stored row looks like after the SQL INSERT

I want to call you attention to the fact that all columns that are not referenced in the query get set to null. Also, if the schema had default values defined these would also get stored for that column when the rows gets inserted.

p_password = 'mydogSpot1'
p_active_flg = '1'
p_lastname = 'Bag'
p_firstname = 'James'
p_gender_code = 'M'
p_birth_dttm = '1942-10-11 00:00:00'

C# - TextBox AppendText

C# - TextBox AppendText

This article demonstrates some reusable code that you can use to solve a problem related to newlines and TextBox control in its multiline mode. It can help really enhance the appearance and exactness of your Windows Forms program written in C#. The code is below and free for anyone to take and use.

Problem

In .NET, you are able to append text to a multiline TextBox with AppendText. This is great and it makes it easy to keep a log going of what's happening or events or turns or whatever. However, that will not append a newline or line feed to the end of the text, so when you call textBox1.AppendText again, the text will be on the same line.

///

/// Just a test method that shows the code being used.

///

private void Test()
{
for (int i = 0; i <>
{
textBox1.AppendText("Some text");
}

}

Solution

Let's explore some alternatives, and then I will show you the very best way that I have found, that solves the entire newline problem with TextBox controls in Windows Forms. First, let me show you what happens when you always append a newline to the TextBox. My first reaction was to change the code to this:

///

/// Another test method.

///

private void Test()
{
for (int i = 0; i <>
{
textBox1.AppendText("Some text" + Environment.NewLine);
}
}

That works fine, but it isn't as elegant in the code and it also has a small problem:
when the TextBox is appended to, there will be a blank line at the end. For
a scrolling log-style scenario, I don't want that blank line. This is what the two
calls would produce:

In one of my programs, that blank line caused a bit of a blemish on my UI. So I tried the following code. What I show next is the code and then what it will look like in your TextBox. As I show, it will put a blank line at the very start, which is very undesirable.

///

/// Another test.
///

private void Test()
{
for (int i = 0; i < style="font-weight: bold;">Best Solution

Nevermind--I whipped up a better solution and put it in the Windows form. The following code uses some conditional logic to test whether we need a newline on the TextBox append. This is better than AppendLine or any version of AppendText. What I show next is the new function, how you can call it, and then its output on the TextBox.

/// Append a line to the TextBox, and make sure the first and last
/// appends don't show extra space.

The string you want to show in the TextBox.
private void AppendTextBoxLine(string myStr)
{
if (textBox1.Text.Length > 0)
{
textBox1.AppendText(Environment.NewLine);
}
textBox1.AppendText(myStr);
}

///
/// Just a test method that shows the above code being used.
///
private void TestMethod()
{
for (int i = 0; i <>