[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

read from files

im using perl-PostgreSQL(PgPP)

i have a file contain below info.

text file
----------

studentID age unit credit

1234      12      math      10
1235      11      math      10
1234      12      science      10


My purpose is to read this file and write data in the file to database.

below is my code


my code is in code snippet

i tried to print sql and i get result as follow

insert into testing.table (studentid,age,unit credit) values (?,?,?,?)

how to get the value instead of ?
where did i do wrong. My database is PostgresSQL PgPP


$column="";
$value    ="";
$lineNumber=0;
 
open(Text file)
while text line is not empty do
begin
chop(text line)
$lineNumber++;
LineData equal to split(tab, text line)
If file is valid then
$i=1;
foreach $fieldName (@LineData) {                                         
if (length($fieldName)>0) {                     
$column.=$tableColumns{uc($fieldName)}.",";
$value.="?,";
$column{uc($fieldName)}=$i;
$i++;
}
}
end
 
 $sql="INSERT INTO $database.".$tablename."($column) VALUES ($value)";
 print $sql;

Open in new window

0
tankergoblin
Asked:
tankergoblin
  • 9
  • 5
  • 3
  • +1
1 Solution
 
Adam314Commented:
This question appears to be duplicated at least twice:
  http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_24336393.html
  http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_24331018.html


Is there a reason you keep repeating the question, instead of working through it once?
I've been working with you in one question, and it looks like there are some others.  Note that the experts here are volunteers - they are not paid or employeed by experts-exchange, and it can sometimes take some time to get a response - experts are from all over the world, it may be night time for them during your day time.  If, after some time, you aren't getting a response, you can click the "Request attention" link, and an additional e-mail will be sent out.
0
 
oleberCommented:
This code as the correct idea. But without seen it, I cann't comment it.

You are supposed to use the ? 'palce holder' for safety reasons.

Now what is your questions ???
0
 
tankergoblinAuthor Commented:
Adam314:
thanks for your effort and i appreciate it
but i need the answer urgently.
hope more ppl can help me on this.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tankergoblinAuthor Commented:
my question is i get "?"

in

insert into testing.table (studentid,age,unit credit) values (?,?,?,?)

suppose it should be
insert into testing.table (studentid,age,unit credit) values (1234,12, math,10)

0
 
tankergoblinAuthor Commented:
Adam314:
feel free to work on the problem.
i know experts here are volunteer that why i hope i can give more points to you if you can solve my problem.
cause it took me quite some time to solve it.
pls help

further more i think this solution worth 1500 point
so if the answer is correct i willing to give 1500 point
0
 
oleberCommented:
if you are using DBI, just do

$dbh->do($sql, undef, (1234,12, "math",10));

this will be safer and cleaner.
0
 
vikaskhoriaCommented:
0
 
tankergoblinAuthor Commented:
if you are using DBI, just do

$dbh->do($sql, undef, (1234,12, "math",10));

this will be safer and cleaner

what do you mean
1234,12, "math",10
is a variable
it can be 1255,0,"none",1

so by putting a constant, is a good idea?
0
 
oleberCommented:
for variables do like:
$dbh->do($sql, undef, ($var1, $var2, $var3, $var4));

this will allow DataBase optimizations since the query will be always the some.

But more important, it will avoid "SQL injection".
http://en.wikipedia.org/wiki/SQL_injection
0
 
Adam314Commented:
It is against EE policy to ask a question more than once, to offer more than 500 points for a given question.  So I think this question should be deleted, and you should work with your existing questions.

I think the confusion here is how the DBI module works.  The way this module works is you create a generic SQL statement without the data in it.  You put a question mark in place of the actual data.  You then prepare this statement.  So far, nothing has actually happened to your database.  Then you execute that statement, providing the actual data.  The DBI module will take care of replacing the question marks from the generic SQL statement with the real data.
0
 
tankergoblinAuthor Commented:
if i use $dbh->do($sql, undef, ($var1, $var2, $var3, $var4));
i need to declare or assign value for $var1 what is the difference??
example $var1 = "one";
say if i have 100 data
then i need 100 $var1?
0
 
tankergoblinAuthor Commented:
I think the confusion here is how the DBI module works
what you suggest me to do?

You put a question mark in place of the actual data.
say i  have 100 data in my file,,, i cannot put actual data to my sql...
i need a var that can read all data in my file

now i write my data to postgresql database i dont know why ? doest work
last it work properly in oracle..

0
 
Adam314Commented:
>>what you suggest me to do?
You should read the DBI documentation, specifically the section on placeholders and bind values.
http://search.cpan.org/~timb/DBI-1.607/DBI.pm#Placeholders_and_Bind_Values

>>i need a var that can read all data in my file
At any one time, the var only needs to hold data for 1 record.  Your program will read one record from your text file, then write one record to the database.  After that, your program can forget about that data.  There is no need to hold all 100 records in your program.

I provided a program that did this in post 24181935 of question http://www.experts-exchange.com/Q_24336393.html

Here is that program again, with comments
#the DBI module is what you use to interface to the database
use DBI;
 
#NOTE: You need to define $dsn, $user, and $pass here
#The $dsn contains the connection information needed to connect to your database
#The $user and $pass are the username and password
my $dbh=DBI->connect($dsn, $user, $pass);
 
#This will open the file
open(my $in, "<filename.txt") or die "Could not open file: $!\n";
 
#This will read the first line of the file (which contains the header)
#It will split the line on tab, and save each of those fields as an 
#element in the array @header
my @header = split(/\t/, <$in>);
 
#This will prepare a SQL statement to insert data to your database
#The field names will be the upper case of what is in your text file
#The values will be question mark placeholders
#The placeholders will be filled in with actual data later
my $sth=$dbh->prepare(
  "INSERT INTO $database.$tablename "
 ."(" . join(", ", map({uc($_)},@header) . ")"
 ." VALUES "
 ."(" . join(", ", map({"?"},@header) . ")"
 );
 
#This will continue reading your file as long as there is
#more data to read
#This assumes that each line is 1 record
while(<$in>) {
    #This will split each line on tab
    #then execute the already prepared statement
    #Here, each question mark is replaced with an actual value
    #The DBI module takes care of all the work for this
    $sth->execute(split(/\t/, $_));
}
 
#This closes the file
close($in);

Open in new window

0
 
tankergoblinAuthor Commented:
i notice that my problem now is not with DBI cause i try to print $lineNumber++;
and it return only 2 line, it should return 5.

also my open file does not have $in
i do as follow open(FILE);

also i think my DBI has no problem because i have try it using data .
i can connect to my database,
i can insert , select and delete.
When come to code above i only able to read question mark

$column.=$tableColumns{uc($fieldName)}.",";
$value.="?,";
print $value
i also try to print $value and it give me ?,?,?,?
how to solve

0
 
Adam314Commented:
>>also my open file does not have $in
>>i do as follow open(FILE);
This is the problem.  Try running the code as I posted it.  
0
 
tankergoblinAuthor Commented:
can i know why it is a problem as i can open the file...
0
 
Adam314Commented:
This code i gave:
    open(my $in, "<filename.txt") or die "Could not open file: $!\n";
will open the file named filename.txt, and associate it to the file handle $in.  If it fails, it'll give you an error message letting you know, and it'll tell you why.

This code you tried:
    open(FILE);
Will try to open a file referred to by a non-lexical variable $FILE, and associate it to the file handle FILE.
Problem 1: $FILE isn't defined anywhere else in the code
Problem 2: Later on in the code, the program reads from the $in file handle,
    which doesn't exist in this code
Problem 3: If it fails, you aren't given any indication that it failed, or why.

0
 
tankergoblinAuthor Commented:
i prefer to use open(FILE)
if can i dont want to have big change in my code because i have around 2000 line if i change it to $in that mean i need to redo everything

the code was work well with oracle now i migrate it to postgresql problem happen

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now