free barcode generator source code in vb.net PROGRAMMING WITH SQL in Software

Generating Code 128B in Software PROGRAMMING WITH SQL

PROGRAMMING WITH SQL
Recognizing ANSI/AIM Code 128 In None
Using Barcode Control SDK for Software Control to generate, create, read, scan barcode image in Software applications.
Paint Code 128 Code Set C In None
Using Barcode drawer for Software Control to generate, create Code128 image in Software applications.
1. The program constructs a SQL statement as a string of text in one of its data areas (usually called a buffer). The statement can be almost any SQL statement that does not retrieve data.
Code 128A Scanner In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
Generate Code 128 Code Set B In C#.NET
Using Barcode generation for Visual Studio .NET Control to generate, create Code-128 image in .NET framework applications.
Figure 18-2.
Drawing Code 128B In Visual Studio .NET
Using Barcode drawer for ASP.NET Control to generate, create USS Code 128 image in ASP.NET applications.
Creating Code 128 In .NET Framework
Using Barcode drawer for .NET Control to generate, create ANSI/AIM Code 128 image in VS .NET applications.
The EXECUTE IMMEDIATE statement syntax diagram
Code 128C Generator In VB.NET
Using Barcode creation for Visual Studio .NET Control to generate, create ANSI/AIM Code 128 image in VS .NET applications.
Bar Code Creation In None
Using Barcode maker for Software Control to generate, create barcode image in Software applications.
SQL: The Complete Reference
Paint GTIN - 12 In None
Using Barcode creator for Software Control to generate, create GTIN - 12 image in Software applications.
Bar Code Encoder In None
Using Barcode generator for Software Control to generate, create bar code image in Software applications.
2. The program passes the SQL statement to the DBMS with the EXECUTE IMMEDIATE statement. 3. The DBMS executes the statement and sets the SQLCODE/SQLSTATE values to indicate the completion status, exactly as if the statement had been hard-coded using static SQL. Figure 18-3 shows a simple C program that follows these steps. The program prompts the user for a table name and a SQL search condition, and builds the text of a DELETE statement based on the user s responses. The program then uses the EXECUTE IMMEDIATE statement to execute the DELETE statement. This program cannot use a static SQL-embedded DELETE statement, because neither the table name nor the search condition are known until the user enters them at runtime. It must use dynamic SQL. If you run the program in Figure 18-3 with these inputs:
Code 128B Encoder In None
Using Barcode maker for Software Control to generate, create Code 128 Code Set C image in Software applications.
Print EAN / UCC - 14 In None
Using Barcode drawer for Software Control to generate, create EAN 128 image in Software applications.
Enter table name: staff Enter search condition: quota < 20000 Delete from staff successful.
Identcode Drawer In None
Using Barcode printer for Software Control to generate, create Identcode image in Software applications.
EAN128 Drawer In Java
Using Barcode creation for Java Control to generate, create UCC - 12 image in Java applications.
the program passes this statement text to the DBMS:
Painting 1D In Java
Using Barcode creation for Java Control to generate, create 1D Barcode image in Java applications.
Paint Bar Code In VS .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create bar code image in .NET applications.
delete from staff where quota < 20000
GS1 128 Generator In .NET Framework
Using Barcode creation for VS .NET Control to generate, create EAN 128 image in VS .NET applications.
Bar Code Scanner In Java
Using Barcode Control SDK for Java Control to generate, create, read, scan barcode image in Java applications.
If you run the program with these inputs:
Print Barcode In None
Using Barcode creation for Office Word Control to generate, create bar code image in Microsoft Word applications.
Barcode Encoder In Objective-C
Using Barcode printer for iPhone Control to generate, create barcode image in iPhone applications.
Enter table name: orders Enter search condition: cust = 2105 Delete from orders successful
the program passes this statement text to the DBMS:
delete from orders where cust = 2105
The EXECUTE IMMEDIATE statement thus gives the program great flexibility in the type of DELETE statement that it executes. The EXECUTE IMMEDIATE statement uses exactly one host variable the variable containing the entire SQL statement string. The statement string itself cannot include host variable references, but there s no need for them. Instead of using a static SQL statement with a host variable like this:
exec sql delete from orders where cust = :cust_num;
18:
Dynamic SQL *
main() { /* This program deletes rows from a user-specified table according to a user-specified search condition. */ exec sql include sqlca; exec sql begin declare section; char stmtbuf[301]; exec sql end declare section; char tblname[101]; char search_cond[101]; /* table name entered by user */ /* search condition entered by user */
/* SQL text to be executed */
/* Start building the DELETE statement in stmtbuf */ strcpy(stmtbuf,"delete from "); /* Prompt user for table name; add it to the DELETE statement text */ printf("Enter table name: "); gets(tblname); strcat(stmtbuf, tblname); /* Prompt user for search condition; add it to the text */ printf("Enter search condition:"); gets(search_cond); if (strlen(search_cond) > 0) { strcat(stmtbuf, " where "); strcat(stmtbuf, search_cond); } /* Now ask the DBMS to execute the statement */ exec sql execute immediate :stmtbuf; if (sqlca.sqlcode < 0) printf("SQL error: %ld\n", sqlca.sqlcode); else printf("Delete from %s successful.\n", tblname); exit();
PROGRAMMING WITH SQL
Figure 18-3.
Using the EXECUTE IMMEDIATE statement
SQL: The Complete Reference
a dynamic SQL program achieves the same effect by building the entire statement in a buffer and executing it:
sprintf(buffer, "delete from orders where cust = %d", cust_num) exec sql execute immediate :buffer;
The EXECUTE IMMEDIATE statement is the simplest form of dynamic SQL, but it is very versatile. You can use it to dynamically execute most DML statements, including INSERT, DELETE, UPDATE, COMMIT, and ROLLBACK. You can also use EXECUTE IMMEDIATE to dynamically execute most DDL statements, including the CREATE, DROP, GRANT, and REVOKE statements. The EXECUTE IMMEDIATE statement does have one significant limitation, however. You cannot use it to dynamically execute a SELECT statement, because it does not provide a mechanism to process the query results. Just as static SQL requires cursors and specialpurpose statements (DECLARE CURSOR, OPEN, FETCH, and CLOSE) for programmatic queries, dynamic SQL uses cursors and some new special-purpose statements to handle dynamic queries. The dynamic SQL features that support dynamic queries are discussed later in the section Dynamic Queries.
Two-Step Dynamic Execution
The EXECUTE IMMEDIATE statement provides one-step support for dynamic statement execution. As described previously, the DBMS goes through all five steps of Figure 18-1 for the dynamically executed statement. The overhead of this process can be very significant if your program executes many dynamic statements, and it s wasteful if the statements to be executed are identical or very similar. In practice, the EXECUTE IMMEDIATE statement should be used only for one-time statements that will be executed once by a program and then never executed again. To deal with the large overhead of the one-step approach, dynamic SQL offers an alternative, two-step method for executing SQL statements dynamically. In practice, this two-step approach, separating statement preparation and statement execution, is used for all SQL statements in a program that is executed more than once, and especially for those that are executed repeatedly, hundreds or thousands of times, in response to user interaction. Here is an overview of the two-step technique: 1. The program constructs a SQL statement string in a buffer, just as it does for the EXECUTE IMMEDIATE statement. A question mark ( ) can be substituted for a constant anywhere in the statement text to indicate that a value for the constant will be supplied later. The question mark is called a parameter marker. 2. The PREPARE statement asks the DBMS to parse, validate, and optimize the statement and to generate an application plan for it. This is Step 1 of the DBMS interaction. The DBMS sets the SQLCODE/SQLSTATE values to indicate any errors found in the statement and retains the application plan for later execution. Note that the DBMS does not execute the plan in response to the PREPARE statement.
18:
Dynamic SQL *
3. When the program wants to execute the previously prepared statement, it uses the EXECUTE statement and passes a value for each parameter marker to the DBMS. This is Step 2 of the DBMS interaction. The DBMS substitutes the parameter values, executes the previously generated application plan, and sets the SQLCODE/SQLSTATE values to indicate its completion status. 4. The program can use the EXECUTE statement repeatedly, supplying different parameter values each time the dynamic statement is executed. The DBMS can simply repeat Step 2 of the interaction, since the work in Step 1 has already been done, and the results of that work (the application plan for execution) will still be valid. Figure 18-4 shows a C program that uses these steps, which are labeled by the callouts in the figure. The program is a general-purpose table update program. It prompts the
main() { /* This is a general-purpose update program. It can be used for any update where a numeric column is to be updated in all rows where a second numeric column has a specified value. For example, you can use it to update quotas for selected salespeople or to update credit limits for selected customers. /* exec sql exec sql char float float exec sql char char char char include sqlca; begin declare section; stmtbuf[301] search_value; new_value; end declare section;
/* SQL text to be executed */ /* parameter value for searching */ /* parameter value for update */
tblname[31]; searchcol[31]; updatecol[31]; yes_no[31];
/* /* /* /*
table to be updated */ name of search column */ name of update column */ yes/no response from user */
Copyright © OnBarcode.com . All rights reserved.