dBLib

[Terence Heaford]

Thanks,

I’m sure the author IS smart enough. ;)

Also, tryng to increase your workload.

I have been using an existing stack of mine to evaluate dBLib which totals columns like this:

put merge("select sum(amountColumn) as totalValue from amountColumn where amountColumn < 0 and date >= [[tStartDate]] and date <= [[tEndDate]]") into tSQL

There does not appear to be anyway to do this (sum) in dbLib other than by selecting the records and doing the addition in LC.

Is that something that could be added?

dbSum “myColumn”

I have found the "select sum” method to be quicker than using the LC method.

also,

could

dbWhere "date >=", tStartDate
dbWhere "date <=", tEndDate

be implemented by adding to dbLib a dbBetween function?

dbBetween “date”,tStartDate,tEndDate
dbBetween “date”,tNumA,tNumB


All the best

Terry





On 12 Sep 2014, at 16:09, Andre Garzia <andre at andregarzia.com> wrote:

> Terence,
> 
> The author did this because sometimes he is not that smart. I've spoken to
> the author and he will do an update changing the behaviour. He explained to
> me that when the library was built that sounded like a good idea.
> 
> ;-)
> 
> 
> On Thu, Sep 11, 2014 at 6:26 AM, Terence Heaford <t.heaford at btinternet.com>
> wrote:
> 
>> May have found the answer in the following handler in dbLib.
>> 
>> I have commented out an “if” test that seems to overwrite the contents of
>> the column dbWhere when you try and add another condition.
>> 
>> Why the author did this, I am not sure, would anyone care to hazard a
>> guess?
>> 
>> Thanks
>> 
>> Terry
>> 
>> 
>> command dbWhere pColumn, pValue, pConcatenationOperator
>>   if the number of words in pColumn > 1 then
>>      put word 2 of pColumn into tOperator
>>      put word 1 of pColumn into pColumn
>>   else
>>      put "=" into tOperator
>>   end if
>> 
>>   if pValue is "null" then
>>      put "IS" into tOperator
>>      put "NULL" into pValue
>>   end if
>> 
>>   if pValue is "not null" then
>>      put "IS NOT" into tOperator
>>      put "NULL" into pValue
>>   end if
>> 
>>   if pConcatenationOperator is empty then
>>      put "AND" into pConcatenationOperator
>>   end if
>> 
>> 
>> 
>>   --if dbA["where columns"][pColumn] is empty then
>>      if dbA["where"] is empty then
>>         put "WHERE" && pColumn && tOperator && placeholder(pValue) before
>> dbA["where"]
>>      else
>>         put " " & pConcatenationOperator  && pColumn && tOperator &&
>> placeholder(pValue) after dbA["where"]
>>      end if
>>   --else
>>      --replace dbA["where columns"][pColumn] with (pColumn && tOperator
>> && placeholder(pValue)) in dbA["where"]
>>   --end if
>> 
>>   set the itemdel to space
>>   put item -3 to -1 of dbA["where"]  into dbA["where columns"][pColumn]
>> end dbWhere
>> 
>> 
>> 
>> 
>> 
>> On 11 Sep 2014, at 10:03, Terence Heaford <t.heaford at btinternet.com>
>> wrote:
>> 
>>> Thought I’d give this plugin a try:
>>> 
>>> convert tStartDate from short system date to seconds
>>> convert tEndDate from short system date to seconds
>>> 
>>> dbResetQuery
>>> 
>>> dbWhere "category", pItemName
>>> 
>>> dbWhere "date >=", tStartDate
>>> 
>>> dbWhere "date <=", tEndDate
>>> 
>>> dbOrderBy “date"
>>> 
>>> put dbGet(sq(tTableName)) into tTranData
>>> 
>>> tStartDate and tEndDate are in seconds.
>>> 
>>> The SQL generated is
>>> 
>>> SELECT * FROM ‘my table' WHERE category = :1 AND date <= :3  ORDER BY
>> date
>>> 
>>> Why is it not including tStartDate?
>>> 
>>> Can anyone help?
>>> 
>>> Thanks
>>> 
>>> Terry
>>> _______________________________________________
>>> use-livecode mailing list
>>> 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
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> 
> 
> 
> -- 
> http://www.andregarzia.com -- All We Do Is Code.
> http://fon.nu -- minimalist url shortening service.
> _______________________________________________
> use-livecode mailing list
> 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