Databases
StackOverflow2013
Postsdbo
POPERL databases to file
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPERL databases to file
    primarykey
    data
    text
    <p>Currently I am working on my exam project and I require some assistance.</p> <p>The goal from this script is to select data from a MySQL database and put this in a file with a specific line length and even a specific column length. The required length for each field and more make-up is located in a different database. So far so good, so what I've done is I made two arrays: one for the make up (columns, DB1) and another one for the data (DB2). Now the problem is each line has to make 256 characters, there are those fillers which are located in the make up database but not in the data one, so at the point where the filler field comes up, everything messes up. I tried loads of things, but I can't figure out what to do, and since its my exam project, my whole study kinda relies on it.</p> <pre><code>$expsel-&gt;bind_columns(\$tabelnaam,\$huidige,\$id); while($expsel-&gt;fetch()) { $tbl = substr($tabelnaam, 0,4); $tblnr = substr($tabelnaam, 2,2); $i = 0; $exp_spec = $dbh-&gt;prepare("SELECT * FROM tblExportspecificatie WHERE tabelnaam = '".$tbl."' ORDER BY id"); $exp_spec-&gt;execute(); $exp_spec-&gt;bind_columns(\$id, \$tblnaam, \$vldnaam, \$vldlngte, \$ascii, \$telveld, \$tellen, \$keystring); while($exp_spec-&gt;fetch()){ if($i == 0){ @AoA = ([ $tbl, $vldnaam, $vldlngte, $ascii, $telveld, $tellen, $keystring ]); }else{ push @{ $AoA[$i] }, $tbl, $vldnaam, $vldlngte, $ascii, $telveld, $tellen, $keystring; } $i++; } $exp_spec-&gt;finish(); # Start regel wegschrijven aan het begin van de nieuwe tabel reeks # printf FILE "10".$tblnr.pad2str(252, 1, "").chr(10); $tbl_data = $dba-&gt;prepare("SELECT * FROM ".$tbl." LIMIT 5"); $tbl_data-&gt;execute(); $regels = $tbl_data-&gt;rows; my @array; while ( my @arr = $tbl_data-&gt;fetchrow_array() ) { push @array,\@arr; } for($s = 0; $s &lt; $regels; $s++){ for($x = 0; $x &lt; $i; $x++){ if($x == 0){ if($AoA[0][1] eq "F0101" || $AoA[0][1] eq "F6115"){ printf FILE $tblnr.pad2str(4, 0, $gemeentecode).pad2str(8, $AoA[0][3], $array[$s][$x]); }else{ printf FILE $tblnr.pad2str($AoA[0][2], $AoA[0][3], $array[$s][$x]); } }else{ printf FILE pad2str($AoA[$x][2], $AoA[$x][3], $array[$s][$x]); } } printf FILE "--".chr(10); } printf FILE chr(10); print Dumper @AoA; print Dumper @array; @$AoA = 0; } </code></pre> <p>And here are the dumps for the arrays</p> <pre><code>$VAR1 = [ 'st31', 'F0120', '9', '0', '0', '0', '' ]; $VAR2 = [ 'st31', 'F1110', '24', '1', '0', '0', '' ]; $VAR3 = [ 'st31', 'F1120', '5', '0', '0', '0', '' ]; $VAR4 = [ 'st31', 'F1130', '1', '1', '0', '0', '' ]; $VAR5 = [ 'st31', 'F1140', '4', '1', '0', '0', '' ]; $VAR6 = [ 'st31', 'F1150', '2', '1', '0', '0', '' ]; $VAR7 = [ 'st31', 'F1160', '6', '1', '0', '0', '' ]; $VAR8 = [ 'st31', 'F1020', '40', '1', '0', '0', '' ]; $VAR9 = [ 'st31', 'F1310', '40', '1', '0', '0', '' ]; $VAR10 = [ 'st31', 'F8110', '1', '1', '0', '0', '' ]; $VAR11 = [ 'st31', 'F8120', '8', '2', '0', '0', '' ]; $VAR12 = [ 'st31', 'F8130', '8', '2', '0', '0', '' ]; $VAR13 = [ 'st31', 'F1170', '40', '1', '0', '0', '' ]; $VAR14 = [ 'st31', 'F0121', '10', '0', '0', '0', '' ]; $VAR15 = [ 'st31', 'F0130', '8', '0', '0', '0', '' ]; $VAR16 = [ 'st31', 'FILLER', '4', '1', '0', '0', '' ]; $VAR17 = [ 'st31', 'F0140', '10', '0', '0', '0', '' ]; $VAR18 = [ 'st31', 'F0220', '2', '1', '0', '0', '' ]; $VAR19 = [ 'st31', 'FILLER', '1', '1', '0', '0', '' ]; $VAR20 = [ 'st31', 'F0410', '1', '1', '0', '0', '' ]; $VAR21 = [ 'st31', 'F0310', '8', '2', '0', '0', '' ]; $VAR22 = [ 'st31', 'F0810', '8', '2', '0', '0', '' ]; $VAR23 = [ 'st31', 'F0811', '1', '1', '0', '0', '' ]; $VAR24 = [ 'st31', 'FILLER', '5', '1', '0', '0', '' ]; $VAR25 = [ 'st31', 'F1010', '1', '1', '0', '0', '' ]; $VAR26 = [ 'st31', 'FILLER', '7', '1', '0', '0', '' ]; $VAR1 = [ '170805955', 'Waterlelie ', '16', undef, undef, undef, '3434VK', 'Nieuwegein ', undef, 'I', '2010-01-01', '2011-01-01', undef, '356000000', '0', '2147483647', undef, 'V', '1946-10-24', '0000-00-00', 'A', 'W' ]; </code></pre> <p>So I think the problem is because the column array has more data then the data one, so at the point of the filler, the counter messes up... here's some more debug data:</p> <pre><code>&lt;&lt;DEBUG&gt;&gt; L-&gt; 9 AS-&gt; 0, DATA-&gt; 170805955 &lt;&lt;DEBUG&gt;&gt; L-&gt; 24 AS-&gt; 1, DATA-&gt; Waterlelie &lt;&lt;DEBUG&gt;&gt; L-&gt; 5 AS-&gt; 0, DATA-&gt; 16 &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 4 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 2 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 6 AS-&gt; 1, DATA-&gt; 3434VK &lt;&lt;DEBUG&gt;&gt; L-&gt; 40 AS-&gt; 1, DATA-&gt; Nieuwegein &lt;&lt;DEBUG&gt;&gt; L-&gt; 40 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; I &lt;&lt;DEBUG&gt;&gt; L-&gt; 8 AS-&gt; 2, DATA-&gt; 2010-01-01 &lt;&lt;DEBUG&gt;&gt; L-&gt; 8 AS-&gt; 2, DATA-&gt; 2011-01-01 &lt;&lt;DEBUG&gt;&gt; L-&gt; 40 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 10 AS-&gt; 0, DATA-&gt; 356000000 &lt;&lt;DEBUG&gt;&gt; L-&gt; 8 AS-&gt; 0, DATA-&gt; 0 &lt;FILLER&gt; &lt;-- things are getting messy! &lt;&lt;DEBUG&gt;&gt; L-&gt; 4 AS-&gt; 1, DATA-&gt; 2147483647 &lt;&lt;DEBUG&gt;&gt; L-&gt; 10 AS-&gt; 0, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 2 AS-&gt; 1, DATA-&gt; V &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; 1946-10-24 &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; 0000-00-00 &lt;&lt;DEBUG&gt;&gt; L-&gt; 8 AS-&gt; 2, DATA-&gt; A &lt;&lt;DEBUG&gt;&gt; L-&gt; 8 AS-&gt; 2, DATA-&gt; W &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 5 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 1 AS-&gt; 1, DATA-&gt; &lt;&lt;DEBUG&gt;&gt; L-&gt; 7 AS-&gt; 1, DATA-&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGreg Hewgill
    1. USDelano de Rooij
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPERL databases to file
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor \@AoA, you don't need a special case to initialize the array. You can `push` onto an empty array. Your bind_column is useless, if you aren't going to *use* the variables for any particular thing.
      singulars
      1. POPERL databases to file
      1. USAxeman
    2. COWhy don't you just put the formatting definitions from the tblExportspecificatie table into a hash and use `print FILE sprintf` to format the output? Say you want some field to use 10 characters width, `my $width = 10; my $type = 's'; printf("%${width}${type}", 'flesk');` prints "flesk" with 5 leading spaces, where $width and $type are pulled from the definitions table.
      singulars
      1. POPERL databases to file
      1. USflesk
    3. COHey there @flesk, thanks for your comment first of all! Is there anyway you could help me out some more, i am more of a PHP guy, but the school specifically asked for PERL. I do understeand your solution, currently i am using sprintf to fix it. The function pad2str is doing that.. So the problem now is, at some part the filler shows up and the counter messes up.
      singulars
      1. POPERL databases to file
      1. USDelano de Rooij
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload