Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Structuring an SQL program
Top Forums Programming Structuring an SQL program Post 302890807 by figaro on Friday 28th of February 2014 03:54:03 PM
Old 02-28-2014
The code would look something as follows:

Code:
use information_schema;

-- Missing Primary Keys
SELECT t01.table_name 
FROM (SELECT table_name, COUNT(*) FROM columns WHERE table_schema = @myDB GROUP BY table_name, column_key) t01 
GROUP BY t01.table_name 
HAVING COUNT(*) = 1;

etc etc etc

At some point the program continues with (or could continue with) the procedural code:

Code:
DROP PROCEDURE IF EXISTS RuleDupeRows;

DELIMITER //
CREATE PROCEDURE RuleDupeRows (IN myDB VARCHAR(32))
COMMENT 'Duplicate Rows in a Table'
BEGIN
  DECLARE 
  [ --- some more code here --- ]
  END WHILE tableLoop;
  CLOSE curMyTable;
END;
//
DELIMITER ;

The procedural code could also be held in a separate file or even multiple separate files. What is the best practice in these circumstances?

It is invoked as follows:

Code:
mysql < dbchecks.sql databasename > dbchecks.txt

Last edited by figaro; 02-28-2014 at 04:55 PM.. Reason: Line wrap
 

9 More Discussions You Might Find Interesting

1. Solaris

Executing MS-SQL stored procedures from Unix/C Program?

All, We are contemplating a port of an existing software product and would like to expend as little effort as possible. Our new database would be MS-SQL, and we would write stored procedures to perform common db operations. We'd like to call these stored procedures from C or C++ code running... (3 Replies)
Discussion started by: mparks
3 Replies

2. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

3. Shell Programming and Scripting

pro*c program for sql query

Hi all, I have sql query as follows. Please write a pro*c program for the following query. select sp1.cost_change ||','|| sp1.cost_change_desc ||','|| sp1.reason ||','|| to_char(sp1.active_date,'DD-MON-YYYY HH24:MI:SS') ||','|| sp1.status ||','|| sp1.cost_change_origin... (0 Replies)
Discussion started by: user71408
0 Replies

4. UNIX for Dummies Questions & Answers

PRo*C program for SQL queries using threading concept

Hi All, I have written 4 SQL queries. I want to write PRO*C program for this. I want to put these 4 queries in a single PR*C program using threading concept. Please guide me to write the pogram. the queries are as follows. 1. select * from head; 2. select * from details; 3. delete from head... (0 Replies)
Discussion started by: user71408
0 Replies

5. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

6. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

7. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

8. Shell Programming and Scripting

awk script to parse SQL from Pro*C program

Hello, I have a Pro*C program which contains SQL sentences, such as: .... Pro*C sentences .... /*SQL 1*/ EXEC SQL SELECT t1.field1, t1.field2 INTO :w_field FROM TABLE1 t1, TABLE1 t2 WHERE t1.field1 = t2.field1 AND t1.ID = :wl_id; .... Pro*C sentences .... /*SQL 1*/ EXEC... (11 Replies)
Discussion started by: mvalonso
11 Replies

9. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

LEARN ABOUT PHP

db2_foreign_keys

DB2_FOREIGN_KEYS(3)							 1						       DB2_FOREIGN_KEYS(3)

db2_foreign_keys - Returns a result set listing the foreign keys for a table

SYNOPSIS

       resource db2_foreign_keys (resource  $connection, string  $qualifier, string  $schema, string  $table-name)

DESCRIPTION

	Returns a result set listing the foreign keys for a table.

PARAMETERS

	      o $connection
		- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

	      o $qualifier
		- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

	      o $schema
		- The schema which contains the tables. If $schema is NULL, db2_foreign_keys(3) matches the schema for the current connection.

	      o $table-name
		- The name of the table.

RETURN VALUES

	Returns a statement resource with a result set containing rows describing the foreign keys for the specified table. The result set is com-
       posed of the following columns:

       +--------------+---------------------------------------------------+
       | Column name  | 						  |
       |	      | 						  |
       |	      | 		   Description			  |
       |	      | 						  |
       +--------------+---------------------------------------------------+
       | PKTABLE_CAT  | 						  |
       |	      | 						  |
       |	      |  Name of the catalog for the table containing the |
       |	      | primary key. The value is NULL if this table does |
       |	      | not have catalogs.				  |
       |	      | 						  |
       |PKTABLE_SCHEM | 						  |
       |	      | 						  |
       |	      |  Name of the schema for the table containing  the |
       |	      | primary key.					  |
       |	      | 						  |
       |PKTABLE_NAME  | 						  |
       |	      | 						  |
       |	      |   Name of the table containing the primary key.   |
       |	      | 						  |
       |PKCOLUMN_NAME | 						  |
       |	      | 						  |
       |	      |  Name of the column containing the primary key.   |
       |	      | 						  |
       | FKTABLE_CAT  | 						  |
       |	      | 						  |
       |	      |  Name of the catalog for the table containing the |
       |	      | foreign key. The value is NULL if this table does |
       |	      | not have catalogs.				  |
       |	      | 						  |
       |FKTABLE_SCHEM | 						  |
       |	      | 						  |
       |	      |  Name  of the schema for the table containing the |
       |	      | foreign key.					  |
       |	      | 						  |
       |FKTABLE_NAME  | 						  |
       |	      | 						  |
       |	      |   Name of the table containing the foreign key.   |
       |	      | 						  |
       |FKCOLUMN_NAME | 						  |
       |	      | 						  |
       |	      |  Name of the column containing the foreign key.   |
       |	      | 						  |
       |   KEY_SEQ    | 						  |
       |	      | 						  |
       |	      |   1-indexed position of the column in the key.	  |
       |	      | 						  |
       | UPDATE_RULE  | 						  |
       |	      | 						  |
       |	      |  Integer value representing the action applied to |
       |	      | the foreign key when the SQL operation is UPDATE. |
       |	      | 						  |
       | DELETE_RULE  | 						  |
       |	      | 						  |
       |	      |  Integer value representing the action applied to |
       |	      | the foreign key when the SQL operation is DELETE. |
       |	      | 						  |
       |   FK_NAME    | 						  |
       |	      | 						  |
       |	      | 	  The name of the foreign key.		  |
       |	      | 						  |
       |   PK_NAME    | 						  |
       |	      | 						  |
       |	      | 	  The name of the primary key.		  |
       |	      | 						  |
       |DEFERRABILITY | 						  |
       |	      | 						  |
       |	      |  An integer value representing whether	the  for- |
       |	      | eign key deferrability is SQL_INITIALLY_DEFERRED, |
       |	      | SQL_INITIALLY_IMMEDIATE, or SQL_NOT_DEFERRABLE.   |
       |	      | 						  |
       +--------------+---------------------------------------------------+
SEE ALSO

       db2_column_privileges(3),  db2_columns(3),  db2_primary_keys(3),   db2_procedure_columns(3),   db2_procedures(3),   db2_special_columns(3),
       db2_statistics(3), db2_table_privileges(3), db2_tables(3).

PHP Documentation Group 												       DB2_FOREIGN_KEYS(3)
All times are GMT -4. The time now is 02:08 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy