Perl/SQL statements

Hi,

I want to do is execute a INSERT, DELETE and UPDATE statements. Can you please provide me
example or pointer to documentation?

Can this work? Do I anything more?

my $qry = "INSERT INTO TABLE VALUES (?,?,?)";

my $dbh = CMS::Utils::dbConnect(TWC::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry);

my $rv = $sth->execute(value1,value2,value3);

thanks

Find more posts tagged with

Comments

jbonifaci

That should work, assuming that your database connection is being established correctly, your SQL is correct and your values are valid values for the SQL.

~Jeff

Nicholas

Well n good if you can try and post if some problems(errors). btw, your way looks correct(as Jeff suggested).

Raj_iw

Hi,

Does this need a COMMIT? Does not INSERT and does not give me an error either. Here is my test SQL. This is the SQL Server database. Does SQl Server
accept the date I pass?

my $value1 ="test";

my $qry_insert = "INSERT INTO TABLE_X
(IFILENAME,AUTHOR,DEPTHEAD_NAME,DEPTHEAD_EMAIL,MODIFY_DATE,TEAMSITE_URL,WEBSERVER_URL,EXPIRATION_DATE,EDITOR_NAME,EDITOR_EMAIL,LAST_EMAILSENT) VALUES (?,?,?,?,?,?,?,?,?,?,?)";

my $dbh = CMS::Utils::dbConnect(CMS::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry_insert);

my $rv = $sth->execute($value1,$value1,$value1,$value1,'02/02/2005',$value1,$value1,'02/02/2005',$value1,$value1,'02/02/2005');

Edited by Raj_iw on 09/15/05 11:09 AM (server time).

Raj_iw

Hi,

It turns out that SQL server does not like dates being passed as '02/02/2005 thru' execute() function. Same with passing as a variable.
Any ideas how do I pass the date???

Following do not work.

my $rv = $sth->execute('02/02/2005')

or

my $rv = $sth->execute($expdt);

akshathp

were you able to verify if the connection is being established or not?

Akshat Pramod Sharma

jbonifaci

In your SQL, put single quotes around the ? for the date, i.e. '?'. You will need to do the same thing for varchars and text, i.e.:

('?', '?', '?', '?', '?', ...)

~Jeff

Raj_iw

hI,

I tried with '?' for dates and varchars, Did not seem to help. I am passiing date a variable. It does not INSERT and does not give any error either,
I am planning to run a SQL trace.

Also I do I get the error from SQL server returned for the error checking??

Edited by Raj_iw on 09/20/05 11:33 AM (server time).

Raj_iw

Yes, the connection is fine. thanks

jbonifaci

Please post your exact code. Also, the only way you will see an error is if you try to capture it, which I assume you are not doing. Database errors are captured in $DBI::errstr. If your connect, prepare, execute or fetch fail, check $DBI::errstr for what the error was.

~Jeff

Raj_iw

Hi,

Here is the code, What else should I add to this?? thanks

my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, EXPIRATION_DATE) VALUES (?,?)";

print EXPLOG "SQL:= $qry_insert\n";

my $dbh = TWC::Utils::dbConnect(TWC::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry_insert);

my $rv = $sth->execute($dcrPath,$expDate);

jbonifaci

As I said before, you need to add single quotes around text, varchar and date values:
my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, EXPIRATION_DATE) VALUES ('?','?')";~Jeff

Raj_iw

Hi Jeff,

I meant provide $DBI::errstr related syntax around my following code since do not find it in docs, thanks

my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, EXPIRATION_DATE) VALUES (?,?)";

print EXPLOG "SQL:= $qry_insert\n";

my $dbh = TWC::Utils::dbConnect(TWC::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry_insert);

my $rv = $sth->execute($dcrPath,$expDate);

Edited by Raj_iw on 09/21/05 07:29 AM (server time).

jbonifaci

Actually, it is very well documented in the perldoc for the module you are using. perldoc DBI.

~Jeff

Raj_iw

Coming back to date issue with perl and SQL server. I using single quotes, that does not seem to help. Has anybody handled inserting dates
into SQL server database using PERL. Please give me samples if you have.

THANKS

jbonifaci

What error(s) are you getting? I insert dates using the DBI module all the time and have no problems. If you check for errors when connect, preparing and executing, you will likely see an error message that would make this problem easy to solve.

~Jeff

Raj_iw

Hi,

I am getting following error for following code:
----------
An error was detected during the execution of this task:
Can't execute statement: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)(DBD: st_execute/SQLExecute err=-1)
______________

my $test_dt = "9/21/2005";

my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, MODIFY_DATE,) VALUES (?,?))"; # I tried this with '?' single quotes too.

print EXPLOG "SQL:= $qry_insert\n";

my $dbh = TWC::Utils::dbConnect(TWC::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry_insert) or die "Can't prepare statement: $DBI::errstr";

my $rv = $sth->execute($dcrPath,$test_dt) or die "Can't execute statement: $DBI::errstr";

jbonifaci

I see a few things wrong with your query now. Change it to look like this and tell me what happens:
my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, MODIFY_DATE) VALUES ('?','?')";You had an extra comma after MODIFY_DATE, you had an extra ) after your value list and you left out the single quotes.

~Jeff

Raj_iw

Jeff,

That was (extra comma) was just a type. I have right code in my script file. I was wondering about the following error, as if it does not
like date format.

An error was detected during the execution of this task:
Can't execute statement: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)(DBD: st_execute/SQLExecute err=-1)

thanks

jbonifaci

Also, what is the table definition for CMS_TABLE. Does it only have 2 columns? I assume that FILENAME is VARCHAR and MODIFY_DATE is DATETIME?

Raj_iw

Jeff,

For the simplification I had included only 2 fields, following is all the code. This table has 3 datetime fields and all others VARCHARs. if I plug getdate()
it works but if I use $test_dt it gives me the error. I have also tried with single quotes ('?','?','?','?',getdate(),'?','?','?','?','?',getdate())";

my $test_dt = "9/21/2005";

my $qry_insert = "INSERT INTO CMS_TABLE (FILENAME, AUTHOR, DEPTHEAD_NAME, DEPTHEAD_EMAIL, MODIFY_DATE, TEAMSITE_URL, WEBSERVER_URL, EXPIRATION_DATE,EDITOR_NAME, EDITOR_EMAIL, LAST_EMAILSENT) VALUES (?,?,?,?,getdate(),?,?,?,?,?,getdate())";

print EXPLOG "SQL:= $qry_insert\n";

my $dbh = TWC::Utils::dbConnect(TWC::Utils::getDBConnectionInfo());
my $sth = $dbh->prepare($qry_insert) or die "Can't prepare statement: $DBI::errstr";

my $rv = $sth->execute($dcrPath,$author_n,$edepth,$edepth_e,$teamsiteURL,$webserverURL,$test_dt,$author_n,$author_email) or die "Can't execute statement: $DBI::errstr";
print EXPLOG "Return:= $rv\n";

jbonifaci

Actually, I was on crack with adding the quotes around it. When building normal SQL, you would need this, but when using bind parameters you don't need them, or can't use them even. If you hard code the date, your date is fine in that format, but it appears that when using bind parameters, your date needs to be in the format 'YYYY-MM-DD". Change your code back to just ? and then just change the date to 'YYYY-MM-DD' and it should work fine. Sorry for misleading you.

~Jeff

mike_s

When inserting a value into a date field I've always use the following - to_date('01/01/2001', 'MM/DD/YYYY') to convert the string to a date format that is expected.

so you might want to try this:

my $dateFormat = 'MM/DD/YYYY'; # or whatever format your $expDate date string is in
my $rv = $sth->execute($dcrPath,to_date($expDate,$dateFormat));

Raj_iw

Jeff,

You are right YYYY-MM-DD did work. Now I need to convert my date format i.e. MM/DD/YYYY in SQL server. THANKS

Mike,

The to_date format is a Oracle function, is it not? I am looking for equivalent of that in SQL Server DB.

mike_s

Sorry, I missed the part about it being SQL Server.