How to call a user defined function from awk?

Question

I am trying to connect to a db and run a query this is my script

#!/bin/bash
awk '
/----FINAL----/ { f=1 }                     
f=="" || /^--.*--$/ { print; next }   
f { b=b (b==""?"":ORS) $0               
    if(b~/;$/) {                        
        result=dbconnect($b) 
        print result                       #dosomething
        b=""                           
    }
}' sql1.sql

function dbconnect(b)
{
/opt/lampp/bin/mysql -u root myncr <<QUERY_INPUT
$b
QUERY_INPUT

if [ "$?" -eq 0 ];
 then
    return true;
else
    return false;
fi
}

The error it shows is dbconnect never defined; I have tried to define the function inside awk but it doesn't work.

My SQL file looks like this:

---select command------
select * from mytes;
select * from mytest;
select * from mytest;

---insert command------
--INSERT INTO `mytest`(`qlid`, `name`, `email`) VALUES ('nj20302','nancy','nancy@gmail.com');

--update---
UPDATE `mytest` 
SET `name`='james' 
WHERE qlid='jm20322';

----FINAL----
;
select * from mytest;
select * from mytest;

---select command------
---select command------
select * from mytes;
select * from mytest;
select * from mytest;
select * from  mytest;

UPDATE `mytest` 
SET `name`='wendy' 
WHERE qlid='wp50322';

I don't want to use system("./dbconnect.sh " $b)

Answer 1

It may be possible to do most of this within an Awk script...

#!/usr/bin/awk -f


function db_connect(results, quarry) {
  # ... Runs "quarry" though "mysql -u root myncr"
  #     Saves/appends standard out "results" array
  _results_index = 0
  if (length(results) >= 0) {
    _results_index = length(results)
  }

  cmd = "/opt/lampp/bin/mysql -u root myncr " quarry " 2>/dev/null"
  while (( cmd | getline _results_line ) > 0) {
    results[_results_index] = _results_line
    _results_index++
  }
  close(cmd)
}


BEGIN {
  #... do the parsing of commands from file things
  #    then reset and refill variable for results
          delete quarry_results
          db_connect(quarry_results, $b)
          # ... do something with results maybe
          for {i = 0; i < length(quarry_results); i++} {
            print quarry_results[i]
          }
  # ...
}

Above isn't complete but intended as an example of additions that may enable the OP's code to run mostly within one scripting language.

Wasn't certain if having the results buffer appended to or reset on each pass was wanted, so I left in the option for either.

One big downside of doing things this way is that Awk generally doesn't have any viability to standard error, though that too is possible within Awk by following one of the available answers at Unix StackExchage -- GNU Awk ERRNO not set on command failure

Answer 2

I'm afraid melpomene has it correct, you cannot call bash functions from the awk program (well, not with a parameter that changes anyway).

However, here's a little example program that shows a different strategy:

function dbfunction() { 
  echo Processing "'$1'" 
}

awk ' {print $0} ' | while
  read dbcommand  
do
  dbfunction "$dbcommand"
done

So the general idea is that every line of the awk output is executed in the following while loop. This avoids the system call and keeps your awk program performant. You do your text processing in the awk script and you do your SQL stuff from bash.

Of course, there are problems with the loop I wrote for you. First of all, if a command is more than one line things won't work so well ( since the read command reads up to the end of the line ).

I've "tested" a little. Here is a sample run with your inputs. Remember, you'll need to change your input so all SQL statements appear on a single line:

function dbfunction() { 
  echo Executing "'$1'"
}

awk '
/----FINAL----/ { f=1 }                     
f=="" || /^--.*--$/ {  next }   
f { b=b (b==""?"":ORS) $0               
    if(b~/;$/) {                        
        print b  # do something
        b=""                           
    }
}' sql1.sql | while 
  read sql
do
  dbfunction "$sql"
done

And here is the execution:

Executing ';'
Executing 'select *from mytest;'
Executing 'select *from'
Executing 'mytest;'
Executing 'select *from mytes;'
Executing 'select *from mytest;'
Executing 'select *from mytest;'
Executing 'select *from'
Executing 'mytest;'
Executing 'UPDATE `mytest` SET `name`='wendy''
Executing 'WHERE qlid='wp50322';'