10 More Discussions You Might Find Interesting
1. Programming
Dear Team
I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update )
Scenario
We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted... (1 Reply)
Discussion started by: Perlbaby
1 Replies
2. Shell Programming and Scripting
Hi folks,
I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex.
I have a bunch of update statements with all columns in a file which I need to convert into insert statements.
UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies
3. Programming
Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works:
UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` );
So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies
4. Programming
How to get the user name of the Operating system from an Informix 4gl program.
eg:-
-rw-r----- 1 gkuser srth1 292 Jul 27 19:28 u1.txt
i need to get gkuser as the result? (0 Replies)
Discussion started by: enriquegm82
0 Replies
5. Programming
Hi,
I am using INFORMIX 8 and trying to update column value by apending '09' to existing record .
update sample_data_table set Name = Name||22 where id = 90 and state = NY
This worked fine in oracle Database,But throwing error in informix .
I need output as Ravi22 for Ravi record in... (0 Replies)
Discussion started by: Perlbaby
0 Replies
6. Shell Programming and Scripting
Hello All,
Morning,
I am facing problem with my code while creating a log with name as current time stamp using perl. Here is the code.
#!/usr/bin/perl
my $time=localtime;
my ($day,$month,$date,$tm,$year)=split(/ /,$time);
my $stamp=$year."_".$month."_".$date;
my... (4 Replies)
Discussion started by: krsnadasa
4 Replies
7. Shell Programming and Scripting
Hi Dudes,
Can you please suggest me how to create a logfile to track the below script output ? Thanks
#!/bin/ksh
# backup the "std" I/P file descriptor
exec 5<&0
#echo "Proceed ?"
while read config_line; do
# backup the I/P file descriptor of "while" block
exec 6<&0
# restore the... (2 Replies)
Discussion started by: shirdi
2 Replies
8. UNIX for Advanced & Expert Users
Hi,
I have delimited file(|).
Sample data:
1|name|50009|DS24|0|12
2|name|30009|DS24|0|13
3|name|20409|DS24|0|14
4|name|20009|DS24|0|15
5|name|10009|DS24|0|16
I want to load this data into a oracle table (update and insert)
Please help me the commands and also... (1 Reply)
Discussion started by: unihp1
1 Replies
9. Shell Programming and Scripting
Hi ,
Can you guys please help as I have list of files xaa, xab, xac.........xza for eg in which to perform load the 1st (xaa) and insert into table, then only proceed for the 2nd , 3rd and so forth.
In other words, before 1st one finished, 2nd one shall not load and insert to table, and so... (0 Replies)
Discussion started by: rauphelhunter
0 Replies
10. UNIX for Dummies Questions & Answers
Can anyone please help me?
I need to find out if a Unix server running Solaris 2.4 and an Informix V5 Database is capable of CTI via a CSTA Link to a telephone system for screen popping, and what APIs for CTI are supported?
Any help or pointers in the right direction would be much appreciated.... (2 Replies)
Discussion started by: Dave34
2 Replies
Parse::Dia::SQL::Output::SQLite3(3pm) User Contributed Perl Documentation Parse::Dia::SQL::Output::SQLite3(3pm)
NAME
Parse::Dia::SQL::Output::SQLite3 - Create SQL for SQLite version 3.
SYNOPSIS
use Parse::Dia::SQL;
my $dia = Parse::Dia::SQL->new(file => 'foo.dia', db => 'sqlite3');
print $dia->get_sql();
DESCRIPTION
This sub-class creates SQL for the SQLite database version 3.
new
The constructor.
Object names in SQLite have no inherent limit. 60 has been arbitrarily chosen.
_get_create_table_sql
Generate create table statement for a single table using SQLite syntax:
Includes class comments before the table definition.
Includes autoupdate triggers based on the class comment.
autoupdate triggers
If the class comment includes a line like:
<autoupdate:foo/>
Then an 'after update' trigger is generated for this table which executes the statement foo for the updated row.
Examples of use include tracking record modification dates ("<autoupdate:dtModified=datetime('now')/">) or deriving a value from another
field ("<autoupdate:sSoundex=soundex(sName)/">)
get_schema_drop
Generate drop table statments for all tables using SQLite syntax:
drop table {foo} if exists
get_view_drop
Generate drop view statments for all tables using SQLite syntax:
drop view {foo} if exists
_get_fk_drop
Drop foreign key enforcement triggers using SQLite syntax:
drop trigger {foo} if exists
The automatically generated foreign key enforcement triggers are:
See "_get_create_association_sql" for more details.
constraint_name_bi_tr
constraint_name_bu_tr
constraint_name_buparent_tr
constraint_name_bdparent_tr
_get_drop_index_sql
drop index statement using SQLite syntax:
drop index {foo} if exists
get_permissions_create
SQLite doesn't support permissions, so supress this output.
get_permissions_drop
SQLite doesn't support permissions, so supress this output.
_get_create_association_sql
Create the foreign key enforcement triggers using SQLite syntax:
create trigger {fkname}[_bi_tr|_bu_tr|_bdparent_tr|_buparent_tr]
Because SQLite doesn't natively enforce foreign key constraints (see <http://www.sqlite.org/omitted.html>), we use triggers to emulate this
behaviour.
The trigger names are the default contraint name (something like child_table_fk_child_fkcolumn) with suffixes described below.
{constraint_name} is the name of the association, either specified or generated.
{child_table} is the name of the dependent or child table.
{child_fkcolumn} is the field in the dependent table that hold the foreign key.
{parent_table} is the name of the parent table.
{parent_key} is the key field of the parent table.
Before insert - Dependent Table
constraint_name_bi_tr
Before insert on the child table require that the parent key exists.
create trigger {constraint_name}_bi_tr before insert on {child_table}
for each row
begin
select
raise(abort, 'insert on table {child_table} violates foreign key constraint {constraint_name}')
where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
end;
Before update - Dependent Table
constraint_name_bu_tr
Before update on the child table require that the parent key exists.
create trigger {constraint_name}_bu_tr before update on {table_name}
for each row
begin
select raise(abort, 'update on table {child_table} violates foreign key constraint {constraint_name}')
where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
end;
Before update - Parent Table
constraint_name_buparent_tr
Before update on the primary key of the parent table ensure that there are no dependent child records. Note that cascading updates don't
work.
create trigger {constraint_name}_buparent_tr before update on {parent_table}
for each row when new.{parent_key} <> old.{parent_key}
begin
select raise(abort, 'update on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
end;
Before delete - Parent Table
constraint_name_bdparent_tr
The default behaviour can be modified through the contraint (in the multiplicity field) of the association.
Default (On Delete Restrict)
Before delete on the parent table ensure that there are no dependent child records.
create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
for each row
begin
select raise(abort, 'delete on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
end;
On Delete Cascade
Before delete on the parent table delete all dependent child records.
create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
for each row
begin
delete from {child_table} where {child_table}.{child_fkcolumn}=old.{parent_key};
end;
On Delete Set Null
Before delete on the parent table set the foreign key field(s) in all dependent child records to NULL.
create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
for each row
begin
update {child_table} set {child_table}.{child_fkcolumn}=null where {child_table}.{child_fkcolumn}=old.{parent_key};
end;
TODO
Things that might get added in future versions:
Mandatory constraints
The current foreign key triggers allow NULL in the child table. This might use a keyword in the multiplicity field (perhaps 'required') or
could check the 'not null' state of the child fkcolumn.
Views
Views haven't been tested. They might already work, but who knows...
Other stuff
Bugs etc
perl v5.14.2 2011-02-15 Parse::Dia::SQL::Output::SQLite3(3pm)
