MySQL: Problem with writing to database

[edljr]

Hi all,

I have an application which is nearly complete except for one critical part -- saving data to the MySQL database. I have had great success in opening, reading, and closing the database. After scouring this forum, and trying many different iterations, I would now like to ask for help.

The line of code below works perfectly fine, but is not helpful:

Code: Select all

revExecuteSQL dbID, "INSERT INTO k1 VALUES(NULL, 'task', '0002', '222,333', 'Get a life dude')"

This next block of code does not work, nor does it give me an error:

Code: Select all

# Update Database Records
      repeat with x =1 to lastPostIt
         put empty into dbRecord
         put "INSERT INTO " & dbTable & " VALUES(:1,:2,:3,:4,:5)" into dbSQL
         put dbArray[x,1] into dbRecord[1]
         put dbArray[x,2] into dbRecord[2]
         put dbArray[x,3] into dbRecord[3]
         put dbArray[x,4] into dbRecord[4]
         put dbArray[x,5] into dbRecord[5]
         revExecuteSQL dbID, dbSQL, "dbRecord"
      end repeat

Notes on the code:
(1) "lastPostIt" is a global variable indicating how many objects there are (one record per object).
(2) dbTable is a global variable.
(3) dbArray is global and its contents are based on what was read from the db -- the contents are valid (tested).

Any help would be greatly appreciated. I am not sure if it matters, but I am using Rev Studio 3.5.0 (Build 860) and MySQL 5.0.67-community (on the iRev server.

Thanks much,
Ed

[chris9610]

You might try this:

Code: Select all

         put dbArray[x,1] into tcol1
         put dbArray[x,2] into tcol2
         put dbArray[x,3] into tcol3
         put dbArray[x,4] into tcol4
         put dbArray[x,5] into tcol5
         revExecuteSQL (dbID, dbSQL, tcol1,tcol2,tcol3,tcol4,tcol5)

I was not aware you could use an array variable for the row data to insert.

But I am new to RunREv

[edljr]

Chris,

Thanks for the post. Unfortunately, that code does not work either. I previously tried with individual variables as well as the array. Both should work, I just do not know why they do not.

Since I am not getting a syntax error, I wonder if it has something to do with the data I am trying to pass. I played around with double/single quotes, but no combination worked. The only time I was able to write to the db using the revExecuteSQL statement was without using variables.

Code: Select all

revExecuteSQL dbID, "INSERT INTO k1 VALUES(NULL, 'task', '0002', '222,333', 'Get a life dude')"

If only I would have purchased Enterprise instead of Studio, I could have used one of the silver support incidents.

Ed

[AndyP]

Take a look at this posting.

http://forums.runrev.com/phpBB2/viewtop ... =variables

[edljr]

Andy,

Thanks for the link. My error was the lack of quotes around the array name.

Code: Select all

revExecuteSQL dbID, dbSQL, "dbData"

It is unbelievable how simple some errors are...the little ones cause the biggest headaches.

Thanks again,
Ed
= very happy

[chris9610]

That is correct

This also works:

Code: Select all

         put dbArray[x,1] into tcol1
         put dbArray[x,2] into tcol2
         put dbArray[x,3] into tcol3
         put dbArray[x,4] into tcol4
         put dbArray[x,5] into tcol5
         revExecuteSQL "dbID", "dbSQL", "tcol1","tcol2","tcol3","tcol4","tcol5"

Life is good

[Mark]

Hi edljr,

From your original post:

revExecuteSQL dbID, dbSQL, "dbRecord"

I see that the array name includes quotes. Are you sure that the quotes were the problem?

Best,

Mark

[edljr]

Hi Mark,

I was trying so many different things, I guess I cannot be completely sure. Having not touched SQL for so many years, that could have been the source of my problem just as easily as Transcript syntax.

Thanks,
Ed