10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
i wan't to pass a shellscript variable to a sql file.
a.sql
select $field from dual;
the way i am calling this is through sqlplus
field_name="sysdate"
sqlplus -s username/password@hostname:port/servicename <<EOF
@a.sql $field_name
EOF (4 Replies)
Discussion started by: reignangel2003
4 Replies
2. HP-UX
Can anyone help me in solving this ?
p=`date`
e=`echo $p | awk '{print $2,$3}'`
# echo $p
Wed Aug 4 12:00:08 IST 2013
but when I am echoing the value of e it is giving me with one space. As shown below:
# echo $e
Aug 4
I need this value to be exact as found in... (6 Replies)
Discussion started by: Kits
6 Replies
3. Shell Programming and Scripting
The following is my script :
#!/bin/bash
echo "please give app_instance_id"
read app_instance_id
echo "id is $app_instance_id"
export app_id=app_instance_id
sqlplus -s nnviewer/lookup@//nasolora008.enterprisenet.org:1521/LOAD3 @test.sql<<EOF
SPOOL /home/tibco/MCH/Data/qa/raak/name.xls... (4 Replies)
Discussion started by: raakeshr
4 Replies
4. Shell Programming and Scripting
Hi,
I am reading file records inside a while loop,
and want to update the record when certain condition is met.
How can I update a file while being read?
I want to avoid using temporary files, copy, rename, ...
while IFS=',' read -r f1 f2
do
function(f1,f2)
if
then
<add... (1 Reply)
Discussion started by: ysrini
1 Replies
5. Shell Programming and Scripting
Hi,
I have problem with the following code. My IF block is not executed. And I see "syntax error near unexpected token `)'" error for line "EOF" in the stats_function().
but when I comment the IF block I don't see this error.
Kindly help me with this issue.
clean_function()
{... (10 Replies)
Discussion started by: babom
10 Replies
6. Shell Programming and Scripting
Hi all,
I'm trying to run an sql inside a loop which looks like this
#!bin/ksh
while IFS=, read var1 var2
do
sqlplus -s ${USERNAME}/${PASSWORD}@${ORACLE_SID} << EOF
insert into ${TABLE}
(
appt_date
)
values
(
'${var1 }'
);
... (6 Replies)
Discussion started by: ryukishin_17
6 Replies
7. Shell Programming and Scripting
Hi Guys,
I like to pass a variable to a sql file in a unix script..
I tried a below code..
var=200903
db2 -vf test.sql 200903
test.sql is as below.
select * from db2.users where quarter = $1;
Please tell me where i go wrong..
Thanks in advance,
Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies
8. Shell Programming and Scripting
Please help. I got these error. I'm try to pass variable extract from data-file.txt to sql file(select.sql).
cat: cannot open select
cat: cannot open *
cat: cannot open from
cat: cannot open user
cat: cannot open where
cat: cannot open name=$list;
#!/bin/bash
list=`sed q... (3 Replies)
Discussion started by: killboy
3 Replies
9. Shell Programming and Scripting
Hi Gurus
I have a file called /tmp/CMDB which looks like this
serial: 0623AN1208
hostname: server1
model: x4100
assetID: 1234
I am writing a for loop that will go through this file line by line creating a variable of itself. Using the first iteration of the loop (i.e. the first line) as... (6 Replies)
Discussion started by: hcclnoodles
6 Replies
10. Shell Programming and Scripting
This is what I tried:
vara=${varb}_count
(( vara += 1 ))
Thanks for help (4 Replies)
Discussion started by: pa3be
4 Replies
SQL::Abstract::Limit(3pm) User Contributed Perl Documentation SQL::Abstract::Limit(3pm)
NAME
SQL::Abstract::Limit - portable LIMIT emulation
SYNOPSIS
use SQL::Abstract::Limit;
my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );;
# or autodetect from a DBI $dbh:
my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
# or from a Class::DBI class:
my $sql = SQL::Abstract::Limit->new( limit_dialect => 'My::CDBI::App' );
# or object:
my $obj = My::CDBI::App->retrieve( $id );
my $sql = SQL::Abstract::Limit->new( limit_dialect => $obj );
# generate SQL:
my ( $stmt, @bind ) = $sql->select( $table, @fields, \%where, @order, $limit, $offset );
# Then, use these in your DBI statements
my $sth = $dbh->prepare( $stmt );
$sth->execute( @bind );
# Just generate the WHERE clause (only available for some syntaxes)
my ( $stmt, @bind ) = $sql->where( \%where, @order, $limit, $offset );
DESCRIPTION
Portability layer for LIMIT emulation.
new( case => 'lower', cmp => 'like', logic => 'and', convert => 'upper', limit_dialect => 'Top' )
All settings are optional.
limit_dialect
Sets the default syntax model to use for emulating a "LIMIT $rows OFFSET $offset" clause. Default setting is "GenericSubQ". You
can still pass other syntax settings in method calls, this just sets the default. Possible values are:
LimitOffset PostgreSQL, SQLite
LimitXY MySQL, MaxDB, anything that uses SQL::Statement
LimitYX SQLite (optional)
RowsTo InterBase/FireBird
Top SQL/Server, MS Access
RowNum Oracle
FetchFirst DB2
Skip Informix
GenericSubQ Sybase, plus any databases not recognised by this module
$dbh a DBI database handle
CDBI subclass
CDBI object
other DBI-based thing
The first group are implemented by appending a short clause to the end of the statement. The second group require more
intricate wrapping of the original statement in subselects.
You can pass a DBI database handle, and the module will figure out which dialect to use.
You can pass a Class::DBI subclass or object, and the module will find the $dbh and use it to find the dialect.
Anything else based on DBI can be easily added by locating the $dbh. Patches or suggestions welcome.
Other options are described in SQL::Abstract.
select( $table, @fields, $where, [ @order, [ $rows, [ $offset ], [ $dialect ] ] ] )
Same as "SQL::Abstract::select", but accepts additional $rows, $offset and $dialect parameters.
The $order parameter is required if $rows is specified.
The $fields parameter is required, but can be set to "undef", '' or '*' (all these get set to '*').
The $where parameter is also required. It can be a hashref or an arrayref, or "undef".
where( [ $where, [ @order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
Same as "SQL::Abstract::where", but accepts additional $rows, $offset and $dialect parameters.
Some SQL dialects support syntaxes that can be applied as simple phrases tacked on to the end of the WHERE clause. These are:
LimitOffset
LimitXY
LimitYX
RowsTo
This method returns a modified WHERE clause, if the limit syntax is set to one of these options (either in the call to "where" or in
the constructor), and if $rows is passed in.
Dies via "croak" if you try to use it for other syntaxes.
$order is required if $rows is set.
$where is required if any other parameters are specified. It can be a hashref or an arrayref, or "undef".
Returns a regular "WHERE" clause if no limits are set.
insert
update
delete
values
generate
See SQL::Abstract for these methods.
"update" and "delete" are not provided with any "LIMIT" emulation in this release, and no support is planned at the moment. But patches
would be welcome.
Limit emulation
The following dialects are available for emulating the LIMIT clause. In each case, $sql represents the SQL statement generated by
"SQL::Abstract::select", minus the ORDER BY clause, e.g.
SELECT foo, bar FROM my_table WHERE some_conditions
$sql_after_select represents $sql with the leading "SELECT" keyword removed.
"order_cols_up" represents the sort column(s) and direction(s) specified in the "order" parameter.
"order_cols_down" represents the opposite sort.
"$last = $rows + $offset"
LimitOffset
Syntax
$sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
or
$sql ORDER BY order_cols_up LIMIT $rows
if "$offset == 0".
Databases
PostgreSQL
SQLite
LimitXY
Syntax
$sql ORDER BY order_cols_up LIMIT $offset, $rows
or
$sql ORDER BY order_cols_up LIMIT $rows
if "$offset == 0".
Databases
MySQL
LimitYX
Syntax
$sql ORDER BY order_cols_up LIMIT $rows, $offset
or
$sql ORDER BY order_cols_up LIMIT $rows
if "$offset == 0".
Databases
SQLite understands this syntax, or LimitOffset. If autodetecting the
dialect, it will be set to LimitOffset.
RowsTo
Syntax
$sql ORDER BY order_cols_up ROWS $offset TO $last
Databases
InterBase
FireBird
Top
Syntax
SELECT * FROM
(
SELECT TOP $rows * FROM
(
SELECT TOP $last $sql_after_select
ORDER BY order_cols_up
) AS foo
ORDER BY order_cols_down
) AS bar
ORDER BY order_cols_up
Databases
SQL/Server
MS Access
RowNum
Syntax Oracle numbers rows from 1, not zero, so here $offset has been incremented by 1.
SELECT * FROM
(
SELECT A.*, ROWNUM r FROM
(
$sql ORDER BY order_cols_up
) A
WHERE ROWNUM <= $last
) B
WHERE r >= $offset
Databases
Oracle
FetchFirst
Syntax
SELECT * FROM (
SELECT * FROM (
$sql
ORDER BY order_cols_up
FETCH FIRST $last ROWS ONLY
) foo
ORDER BY order_cols_down
FETCH FIRST $rows ROWS ONLY
) bar
ORDER BY order_cols_up
Databases
IBM DB2
GenericSubQ
When all else fails, this should work for many databases, but it is probably fairly slow.
This method relies on having a column with unique values as the first column in the "SELECT" clause (i.e. the first column in the
"@fields" parameter). The results will be sorted by that unique column, so any $order parameter is ignored, unless it matches the
unique column, in which case the direction of the sort is honoured.
Syntax
SELECT field_list FROM $table X WHERE where_clause AND
(
SELECT COUNT(*) FROM $table WHERE $pk > X.$pk
)
BETWEEN $offset AND $last
ORDER BY $pk $asc_desc
$pk is the first column in "field_list".
$asc_desc is the opposite direction to that specified in the method call. So if you want the final results sorted "ASC", say
so, and it gets flipped internally, but the results come out as you'd expect. I think.
The "BETWEEN $offset AND $last" clause is replaced with "< $rows" if <$offset == 0>.
Databases
Sybase Anything not otherwise known to this module.
Skip
Syntax
select skip 5 limit 5 * from customer
which will take rows 6 through 10 in the select.
Databases
Informix
SUBCLASSING
You can create your own syntax by making a subclass that provides an "emulate_limit" method. This might be useful if you are using stored
procedures to provide more efficient paging.
emulate_limit( $self, $sql, $order, $rows, $offset )
$sql
This is the SQL statement built by SQL::Abstract, but without the ORDER BY clause, e.g.
SELECT foo, bar FROM my_table WHERE conditions
or just
WHERE conditions
if calling "where" instead of "select".
$order
The "order" parameter passed to the "select" or "where" call. You can get an "ORDER BY" clause from this by calling
my $order_by = $self->_order_by( $order );
You can get a pair of "ORDER BY" clauses that sort in opposite directions by saying
my ( $up, $down ) = $self->_order_directions( $order );
The method should return a suitably modified SQL statement.
AUTO-DETECTING THE DIALECT
The $dialect parameter that can be passed to the constructor or to the "select" and "where" methods can be a number of things. The module
will attempt to determine the appropriate syntax to use.
Supported $dialect things are:
dialect name (e.g. LimitOffset, RowsTo, Top etc.)
database moniker (e.g. Oracle, SQLite etc.)
DBI database handle
Class::DBI subclass or object
CAVEATS
Paging results sets is a complicated undertaking, with several competing factors to take into account. This module does not magically give
you the optimum paging solution for your situation. It gives you a solution that may be good enough in many situations. But if your tables
are large, the SQL generated here will often not be efficient. Or if your queries involve joins or other complications, you will probably
need to look elsewhere.
But if your tables aren't too huge, and your queries straightforward, you can just plug this module in and move on to your next task.
ACKNOWLEDGEMENTS
Thanks to Aaron Johnson for the Top syntax model (SQL/Server and MS Access).
Thanks to Emanuele Zeppieri for the IBM DB2 syntax model.
Thanks to Paul Falbe for the Informix implementation.
TODO
Find more syntaxes to implement.
Test the syntaxes against real databases. I only have access to MySQL. Reports of success or failure would be great.
DEPENDENCIES
SQL::Abstract, DBI::Const::GetInfoType, Carp.
SEE ALSO
DBIx::SQLEngine, DBIx::SearchBuilder, DBIx::RecordSet.
BUGS
Please report all bugs via the CPAN Request Tracker at http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.
COPYRIGHT AND LICENSE
Copyright 2004 by David Baird.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
AUTHOR
David Baird, "cpan@riverside-cms.co.uk"
HOW IS IT DONE ELSEWHERE
A few CPAN modules do this for a few databases, but the most comprehensive seem to be DBIx::SQLEngine, DBIx::SearchBuilder and
DBIx::RecordSet.
Have a look in the source code for my notes on how these modules tackle similar problems.
perl v5.12.4 2011-09-17 SQL::Abstract::Limit(3pm)