Migrate gawk script to awk

[stagebi]

Hi,
I've a gawk script wich work fine with gawk but on the IBM AIX server I've just awk, the script doesn't work with awk, I've got this error :

$ awk -f awk_sql2.awk
syntax error The source line is 1. The function is parseCreate.
The error context is
<<< function parseCreate(t) >>> {
awk: The statement cannot be correctly parsed.
The source line is 1. The function is parseCreate.
syntax error The source line is 2. The function is parseCreate.

What I have to modify to get the script work with awk?

The script :

function parseCreate(t) {
   match(t, /^CREATE TABLE : ([^;[:space:]]+);([^[:space:]]+) AS$/, a)
   if ( 2 in a ) {
      Program = a[1]
      Table = a[2]
   }
}

function parseSelect(t) {
   match(t, /^SELECT : ([^;[:space:]]+);([^[:space:]]+)$/, a)
   if ( 2 in a ) {
      if ( a[1] == Program ) {
     delete Select
         split(a[2], Select, ",")
      }
   }
}

function parseFrom(t) {
   match(t, /^FROM : ([^;[:space:]]+);([^;]+)$/, a)
   if ( 2 in a ) {
      if ( a[1] == Program ) {
     split(a[2], b, ",")
         delete From
         for ( n in b ) {
            match(b[n], /([^[:space:]]+) ([^[:space:]]+)/, a)
            if (2 in a) {
               From[a[2]] = a[1]
            } else {
               From[0] = b[n]
            }
         }
      }
   }
}
   
BEGIN {
   Program = "MISSING"
}
   
/^CREATE/ { parseCreate($0) }

Program != "MISSING" {
   if ($0 ~ /^SELECT/) {
      parseSelect($0)
   }

   if ($0 ~ /^FROM/) {
      parseFrom($0)
      for (n in Select) {
         match(Select[n], /([^.]+)\.([^.]+)/, a)
         if ( 2 in a ) {
            print Program,";",Table,";",From[a[1]],";",a[2]
         } else {
            print Program,";",Table,";",From[0],";",Select[n]
         }
      }
      Program = "MISSING"
   }
}

Regards

 

[feherke]

Hi

The [tt]match()[/tt] function's third parameter is GNU extension. One of the most useful in my opinion. That is because I know no easy way to replace it.

Search for a GNU [tt]awk[/tt] manual, it should have a section GNU EXTENSIONS. You will have to do some workaround for each thing enumerated there and used in your script.

Feherke.

http://rootshell.be/~feherke/

 

[PHV]

Can't you install gawk on your AIX box ?

 

[stagebi]

Thanks for your response,
No I can't install gawk on this server, it's a production server I only can copy my script on it after testing on the testing server (where awk AND gawk are installed)
Is it very difficult to replace the match function? Nobody have already did that?

 

[PHV]

I'm afraid you have to play with the RSTART and RLENGTH variables ...

 

[stagebi]

I'm really a beginner in awk, I've just modify this script, I don't known awk...
What I have to do to replace match with RSTART and RLENGHT?

 

[feherke]

Hi

[tt][off-topic][/tt]
Then why not rewrite it in anything you know ? I would think to [tt]perl[/tt] first.
[tt][/off-topic][/tt]

Feherke.

http://rootshell.be/~feherke/

 

[stagebi]

I'm not really "programmer", actually I just have to test scripts and install it on the server.
Sometimes I have to do little modifications on scripts but I don't known any language well.
The purpose of this script is to have something like this on input :

CREATE TABLE : prg1;TAB1 AS
SELECT : prg1;COL1,COL2,COL3
FROM : prg1;TABLEFROM

CREATE TABLE : prg1;TAB2 AS
SELECT : prg1;ALIAS1.COL1,ALIAS2.COL2
FROM : prg1;TABLEFROM1 ALIAS1, TABLEFROM2 ALIAS2
[code]

and like this on output : 

[code]
prg1;TAB1;TABLEFROM;COL1
prg1;TAB1;TABLEFROM;COL2
prg1;TAB1;TABLEFROM;COL3

prg1;TAB2;TABLEFROM1;COL1
prg1;TAB2;TABLEFROM2;COL2
[code]

The gawk script work fine to do this and I'm not enought skills to do that in an another language by myself that's why I try to adapt this to the POSIX awk.
If somebody can to this in perl or C I'll very happy to use it :D

 

[feherke]

Hi

Try this :

BEGIN {
  FS=" *[:;,] *"
  OFS=";"
}

$1=="CREATE TABLE" {
  prg=$2
  tab=$3
  sub(/ AS/,"",tab)
}

$1=="SELECT" {
  for (i=3;i<=NF;i++) sel[i-2]=$i
  n=NF-2
}

$1=="FROM" {
  for (i=3;i<=NF;i++) if ($i~/ /) {
    t=a=$i
    sub(/ .*/,"",t)
    sub(/.* /,"",a)
    ali[a]=t
  } else ali[""]=$3
  for (i=1;i<=n;i++) {
    if (sel[i]~/\./) {
      t=sel[i]
      sub(/\..*/,"",t)
    } else t=""
    f=sel[i]
    sub(/.*\./,"",f)
    print prg,tab,ali[t],f
  }
  print ""
}

Feherke.

http://rootshell.be/~feherke/

 

[stagebi]

Thanks a lot! That's work for the data on exemple.
But I've tried with real data like this :

CREATE TABLE : programme1.out;TABLE1 AS
SELECT : programme1.out; DISTINCT PERS.ID_PERS
FROM : programme1.out;TAB_GRP GRP , TAB_PERS PERS
CREATE TABLE : programme1.out;TABLE2 AS
SELECT : programme1.out;ID_PERS,
FROM : programme1.out;TABLE_FROM1

and I have this on output :

;;ID_PERS1.out;TABLE1

;ID_PERSROM1ut;TABLE2
;TABLE_FROM1ut;TABLE2

 

[stagebi]

Hi!
Anybody know why the script doesn't work?
Is it because of . (dot) in the input file?

 

[Annihilannic]

Hmm.. what version of awk are you using, and under what operating system? The output I get is the following:

programme1.out;TABLE1;;ID_PERS

programme1.out;TABLE2;TABLE_FROM1;ID_PERS
programme1.out;TABLE2;TABLE_FROM1;

The reason the first section is missing something is because of the unexpected DISTINCT in the input, so you will need to handle that somehow.

The reason the second prints an extra line is because of the trailing comma on the select statement.

Annihilannic.

 

[stagebi]

Hi,
I'm using the awk version which in the IBM AIX UNIX 5.3 i'm using.

 

[Annihilannic]

I don't have access to an AIX 5.3 box any more, but I tried on AIX 6.1 and it works the same.

Maybe try using nawk instead of awk. On AIX 6.1 they are identical, but on older versions nawk may have more functionality than awk.

Annihilannic.