Structuring an SQL program


 
Thread Tools Search this Thread
Top Forums Programming Structuring an SQL program
# 1  
Old 02-28-2014
Structuring an SQL program

I have a number of queries which are to perform 50+ validations on a database. Some are simple and are one liners, for example whether a primary key is present on a table. Others are more complex and require procedural programming to accomplish, such as whether a column exists which has NULL-values only. All are implemented in pure SQL and have been tested on MySQL 5.5 only so far.

I was wondering if there is a best practice how to program this. I want a single command that performs all validations in sequence. Do I put all code in a single sql-file? Or is it better to have one main file which has the one liners in it and then all the procedural programs in a separate file? Or one main file and the procedural programs in their own files?
# 2  
Old 02-28-2014
I suggest including samples of what you are talking about. It's not clear what you mean by procedural programming. Are you running a number of sql commands and driving them shell script? As far as best practice, I would be concerned about performance issues like total execution time, are you impacting other queries, etc. Post examples.
# 3  
Old 02-28-2014
Stored procedures are also a possibility.
# 4  
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
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

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. 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

7. 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

8. 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

9. 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
Login or Register to Ask a Question