PERL DBI question

[rwlan]

I'm having trouble with a DBI script that has worked for years and years and now that I've transferred it to a new ISP--is dying whenever it gets to an INSERT INTO TABLE sql statement. This is kind of bizarre, because the script also contains a SELECT FROM sql statement that executes just fine. (So this isn't a chmod, or connection string problem)

While I'm pretty sure this is a dbase permissions issue with my ISP (who insists that it isn't) I just wanted to check to make certain that there wasn't some new change in the DBI module that would make code like the following die:

$mysql = "INSERT INTO TABLE database.table (field1,field2) values ('value1','value2')";

$dbh->do($mysql)
or die "cannot do: " . $dbh->errstr();

And yes, I have a connection string that connects, and the name of the database and table are correct.

 

[icrf]

Did you change databases/versions with the ISP change? In SQL I've worked with, there's no literal "TABLE" in an insert statement, just the specific table reference. Maybe your old ISP's database allowed this (or it's just a typo)?

What is the error that DBI returns when the statement fails? Can you update to this table?

________________________________________
Andrew

 

[rwlan]

icrf wrote: "In SQL I've worked with, there's no literal "TABLE" in an insert statement, just the specific table reference."

well course. Let me clarify so that you know that I actually know SQL:

my SQL statement is:

$mysql = "INSERT INTO TABLE database.table (field1,field2) values ('value1','value2')";

where:
database is the name of a specific database;
table is a name of a table in the databse,
field1 and field 2 are defined fields in the table
value1 and value2 are appropriate values for the fields assigned in the statement -- ie text values since they are quoted.

also, just so someone doesn't email me and suggest that my problem is that there is no connection string shown in this code example -- I also actually know what that is, too and there is one that is actually connecting to the database

I don't believe my problem is that I used the word "table" in my code, thanks

I'm trying to determine if there has been some change in PERL DBI that would change the syntax of the do statement. If not, then this problem is probably a user permissions problem and something my ISP needs to fix for me instead of telling me I don't know how to program and they are not interested in helping me

 

[tchatzi]

First of all when you use dbi to connect to a database, you also provide the driver with the name of the database, so you don't need to rewrite it in your Insert or Select or whatever query you make.

Second you could have post the error so we can see what is causing the problem.

Third, add a backslash '\' your single quotes [blue](only if the values you are inserting are variables with a '$' at the front)[/blue]. Otherwise don't use the '\'

And finally add a ';' to the end of your query.[blue](Before the double quotes " )[/blue] (Don't you use one when you make a query? Yes you do. Otherwise the query is not complited!)

So your connection and query should look like this

my $dbh = DBI->connect('DBI:mysql:my_database','foo','foo') 
          || die "Connect Error: $DBI::errstr";
my $sth = $dbh->do("INSERT into tablename (field1,field2) values ([blue]\[/blue]'value1[blue]\[/blue]',[blue]\[/blue]'value2[blue]\[/blue]')[blue];[/blue]")
       || die "Do Insert Error: $DBI::errstr";

``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[tchatzi]

Typo -> change [red]complited[/red] to [blue]completed[/blue]


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[stevexff]

rwlan

We aren't questioning your SQL competence. Just the use of the word TABLE in

$mysql = "INSERT INTO [red]TABLE[/red] database.table (field1,field2) values ('value1','value2')";

I've never seen it before, and (sadly) I go back as far as DB2 v1.3.

MySql doesn't seem to support it either, from looking at

http://dev.mysql.com/doc/mysql/en/insert.html

anyway. Try removing it and see if it works.

 

[MikeLacey]

Keyword TABLE in an INSERT statement is optional - I think that's a red-herring unless the database type has changed and it's not supported in the new database of course.

icrf's key question was about the error message returned when your script "dies" - that's almost certainly key.

Mike

You cannot really appreciate Dilbert unless you've read it in the
original Klingon.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884