Stuck on native database functions

[Terence Heaford]

Just tried this method

revExecuteSQL myID, "insert into mytable values(:1,:2,:1)", "valueX",”valueY"

It did not work

put merge("[[quote]][[tUniqueID]][[quote]],[[quote]][[tDate]][[quote]],[[quote]][[tType]][[quote]],[[quote]][[tDescription]][[quote]],[[quote]][[tAmount]][[quote]],[[quote]][[tBalance]][[quote]],[[quote]][[tCategory]][[quote]]")into tValues

put ":1,:2,:3,:4,:5,:6,:7" into tPlaceHolders

put merge("INSERT INTO [[tAccountName]] VALUES([[tPlaceHolders]])") into tSQL

put tSQL & " " & tValues

revExecuteSQL dbGetID(),tSQL,tValues


Not sure what to try next


All the best

Terry

> On 7 Jul 2021, at 08:28, Terence Heaford <lheaford at icloud.com> wrote:
> 
> I have now tried your method with the tValues array directly and by deconstructing the array as below and it does not work. I have a recollection of not being able to get this to work with an array which is why I did the way I did.
> 
> 
> All the best
> 
> Terry
> 
> put q(tAccountName) into tAccountName
> 
> put q(tValues["uniqueID"]) into tUniqueID
> 
> put q(tValues["date"]) into tDate
> 
> put q(tValues["type"]) into tType
> 
> put q(tValues["description"]) into tDescription
> 
> put q(tValues["amount"]) into tAmount
> 
> put q(tValues["balance"]) into tBalance
> 
> put q(tValues["category"]) into tCategory
> 
> put merge("[[tUniqueID]],[[tDate]],[[tType]],[[tDescription]],[[tAmount]],[[tBalance]],[[tCategory]]”) into tValues
> 
> 
> put ":1,:2,:3,:4,:5,:6,:7" into tPlaceHolders
> 
> put merge("INSERT INTO [[tAccountName]] VALUES([[tPlaceHolders]])") into tSQL
> 
> put tSQL & " " & tValues
> 
> revExecuteSQL dbGetID(),tSQL,tValues
> 
> 
> 
> 
> 
>> On 7 Jul 2021, at 07:52, Terence Heaford via use-livecode <use-livecode at lists.runrev.com <mailto:use-livecode at lists.runrev.com>> wrote:
>> 
>> Here’s an example I have been using, not sure whether it will help:
>> 
>> function dbAddTransaction tAccountName,tValues
>> 
>> put q(tAccountName) into tAccountName
>> 
>> put q(tValues["uniqueID"]) into tUniqueID
>> 
>> put q(tValues["date"]) into tDate
>> 
>> put q(tValues["type"]) into tType
>> 
>> put q(tValues["description"]) into tDescription
>> 
>> put q(tValues["amount"]) into tAmount
>> 
>> put q(tValues["balance"]) into tBalance
>> 
>> put q(tValues["category"]) into tCategory
>> 
>> put "uniqueID,date,type,description,amount,balance,category" into tFields
>> 
>> put merge("[[tUniqueID]],[[tDate]],[[tType]],[[tDescription]],[[tAmount]],[[tBalance]],[[tCategory]]")into tValues
>> 
>> put merge("INSERT INTO [[tAccountName]] ([[tFields]]) VALUES([[tValues]])") into tSQL
>> 
>> revExecuteSQL dbGetID(), tSQL
>> 
>> put the result into tErr
>> 
>> return tErr
>> 
>> end dbAddTransaction
>> 
>> -------------------------
>> 
>> function dbEscapeSqlite tText
>> 
>> replace "'" with "''" in tText
>> 
>> return tText
>> 
>> end dbEscapeSqlite
>> 
>> -------------------
>> 
>> function q tText
>> 
>> return "'" & dbEscapeSqlite(tText) & "'"
>> 
>> end q
>> 
>> -------------------
>> 
>> 
>> 
>> 
>>> On 7 Jul 2021, at 00:06, Bob Sneidar via use-livecode <use-livecode at lists.runrev.com <mailto:use-livecode at lists.runrev.com>> wrote:
>>> 
>>> Hi all. 
>>> 
>>> I assemble an SQL statement that looks like this: 
>>> 
>>> insert into ObjectProperties (recordid,rect,controlname,owner,visible) VALUES (:1,:2,:3,:4,:5)
>>> 
>>> I then execute this statement: 
>>> 
>>> revExecuteSQL tDBID, tInsertSQL, aRecordData
>>> 
>>> I get records but with no data in them. I checked aRecordData and it has all 5 columns as keys and data in each of them. 
>>> 
>>> I am at a complete loss. I was sure this was working before, now it is not. 
>>> 
>>> Bob S
>>> 
>>> 
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com <mailto:use-livecode at lists.runrev.com>
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com <mailto:use-livecode at lists.runrev.com>
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>