msql insert new record

[Josh Mellicker]

My recommendation would be to use a handler to property format the  
SQL statement, so you could write code like this:

jjSQLinsert "tasks","ownerID", currentUserID(), "projectID",  
tProjectID, "statusID", 1

where the first parameter is the table name, followed by ColumnName,  
Value pairs.



Then put something like this somewhere in the message path:


ON jjSQLinsert pMySQLtableName -- columnName, data, columnName, data

     put "INSERT INTO `tmySQLtableName` (theColumnNames) VALUES  
(theData)" into tSQL
     replace "tmySQLtableName" with pMySQLtableName in tSQL
     put (the paramcount - 1)/2 into tNumOfFlds
     put 2 into x
     REPEAT tNumOfFlds times
         IF x > 2 THEN put comma after tColNames
         IF x > 2 THEN put comma after tNewData
         put bq(param(x)) after tColNames
         put q(param(x+1)) after tNewData
         put x+2 into x
     END REPEAT
     replace "theColumnNames" with tColNames in tSQL
     replace "theData" with tNewData in tSQL

    -- at this point you have a properly formatted SQL INSERT statement

    -- but you still have to execute it ;-)

     addSQLRequest tSQL
     executeSQLQueue

    -- next we get the ID of the inserted record from the DB in case  
we need it (and we usually do):

     put revDataFromQuery("","", getConnectID(), "SELECT  
LAST_INSERT_ID()") into tLatestInsertID
     set the uLastInsertID of me to tLatestInsertID

END jjSQLinsert


This works with MySQL, not sure about other databases.



On Oct 16, 2006, at 5:12 PM, Robert Mann wrote:

>   To use the following
> INSERT INTO table_name (column1, column2,...)
> VALUES (value1, value2,....)
> what would be the proper why to write this?
>
>
>
>   get   "fcomp"
>   get it & ",record_id"
>   get it & ",tunerlogin_id"
>   get it & ",freb"
>   put it into colnames
>
>
>   get   " fld fcomp "
>   get it & ",fld newrecord_id"
>   get it & ",fld userid"
>   get it & ",fld freb"
>   put it into colvalues
>
>
>
> put "INSERT INTO rider "colnames" VALUES "colvalues"" into tSQL
>
>
> Thanks
> Robert Mann
>
>
> -----Original Message-----
> From: use-revolution-bounces at lists.runrev.com
> [mailto:use-revolution-bounces at lists.runrev.com]On Behalf Of Jim Ault
> Sent: Sunday, October 15, 2006 8:16 PM
> To: How to use Revolution
> Subject: Re: msql insert new record
>
>
> On 10/15/06 4:14 PM, "Robert Mann" <robmann at gp-racing.com> wrote:
>
>> have a update code that works great, with some held form this list
>>
>> put "UPDATE rider SET "&updateColValListOf123Pairs&" WHERE  
>> record_id = '"
> &
>> fld "record_id" & "'" into tSQL
>>
>> how would I change this to insert a new record
>>
>> put "INSERT INTO rider "&updateColValListOf123Pairs&" " into tSQL
>>
>> the above does not seem to work?
>>
> UPDATE   &  INSERT INTO  don't use the same format
> INSERT INTO does not use "="
>
> on of the many reference pages on the web
>  http://w3schools.com/sql/default.asp
> --------------------------------
> INSERT INTO table_name
> VALUES (value1, value2,....)
> --which puts the values into the first col, second, etc.
>
> You can also specify the columns for which you want to insert data:
> INSERT INTO table_name (column1, column2,...)
> VALUES (value1, value2,....)
> ---------------
> UPDATE table_name
> SET column_name = new_value
> WHERE column_name = some_value
>> put "UPDATE rider SET "&updateColValListOf123Pairs&" WHERE  
>> record_id = '"
> &
>> fld "record_id" & "'" into tSQL
>>
>
> Jim Ault
> Las Vegas
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution