Recent Posts

Pages: [1] 2 3 ... 10
1
You've got Questions? We've got Answers! / Re: SQL Update Question
« Last post by D. Wilson on Today at 02:10:10 PM »
Add the ifnull to sqlstatement worked like a charm.

I want to thank those who jumped in and helped solve this. I spent hours trying to 'find' then answer on my own.
2
You've got Questions? We've got Answers! / Re: SQL Update Question (using NULL)
« Last post by cj on December 15, 2017, 08:46:40 PM »
Insight: Get rid of NULL because they are not needed
slexe  "update t1 set Onhand = 0  where OnHand is null"

Bern has given complete answer

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slopen "sample.db3"
 slexe  "drop table if exists t1"
 slexe  "create table if not exists t1(Code unique,OnHand integer)"
 slexe  "insert into t1 values('code1',0)"
 slexe  "insert into t1 values('code2',0)"
 slexe  "insert into t1 values('code3',null)"
 slexe  "update t1 set OnHand = OnHand + 1 where code = 'code1'"
 slexe  "update t1 set Onhand = OnHand + 2 where code = 'code2'"
 slexe  "update t1 set Onhand = ifnull(Onhand,0) + 3 where code = 'code3'"
 DIM s() AS STRING
 slselary "select * from t1",s(),"Q9"
 ? JOIN$(s(),$CR),%MB_SYSTEMMODAL
END FUNCTION         
       
3
You've got Questions? We've got Answers! / Re: SQL Update Question
« Last post by Bern Ertl on December 15, 2017, 05:17:03 PM »
To update all cases:

Update INVENTORY set qty_on_hand = ifnull(qty_on_hand,0) + 5 Where Itemcode ='Item Code'

http://www.sqlite.org/lang_corefunc.html#ifnull

To filter out NULL cases:

Update INVENTORY set qty_on_hand = qty_on_hand + 5 Where Itemcode ='Item Code' AND qty_on_hand IS NOT NULL
4
You've got Questions? We've got Answers! / Re: SQL Update Question
« Last post by D. Wilson on December 15, 2017, 04:26:41 PM »
I did a work around and added and additional sqlstatement checking for isnull. But if there is an easier way I would appreciate any insights.
5
You've got Questions? We've got Answers! / SQL Update Question
« Last post by D. Wilson on December 15, 2017, 03:42:26 PM »
I know sqlite is typeless in column types. I am stumped. I would like to update the value of a field however. If the field is null it will not update the field. For Example:
                               Update INVENTORY set qty_on_hand = qty_on_hand + 5 Where Itemcode ='Item Code'

                         If the original value is 10. The server would update it to 15.
                         If the original value is null (no value in the field). The server does not apply the change.


Any help and insights would be greatly appreciated.

6
Cj,
Se Post #1
/Fim
7
You've got Questions? We've got Answers! / Re: slRunProc
« Last post by cj on December 07, 2017, 11:24:43 AM »
That really, really helped.
Thank you!
8
cj - I answered your questions in the new thread.  :)
9
You've got Questions? We've got Answers! / Re: slRunProc
« Last post by Bern Ertl on December 07, 2017, 11:03:27 AM »
slRunProc can call server side procedures.  This allows you to write client code that tells the server to do stuff.

I have two libraries for my app:  SQLiteningProcsT.DLL and SQLiteningProcsP.DLL.  The "T" is my "temporary" library.  I call functions in it as needed and do not use the "u" modchar.  The "P" is my "permanent" library.  I load this library at program start and unload it at program end (using "u" and "U" modchars).

Functions in the "T" library can be called with slRunProc as needed.  SQLitening loads, executes and unloads the library.

Functions in the "P" library can be called as needed.  SQLitening just executes the functions (the library is already loaded)

I have a special function in my "P" library ("ICF") to initialize "SQLite custom functions".  SQLite custom functions are functions that can be used within SQL statements (as long as the underlying function for the custom code is available - ie. loaded).  So, client app needs to load the "P" library and keep it loaded (using "u" or "L" modchars) so the underlying code for the SQLite custom functions are available.  I provided a full example of a "P" library with the time stamp code here (substitute "P" for "[letter]":  http://sqlitening.com/support/index.php?topic=3539.msg18054#msg18054

~~~

Does each function have to be registered?

Functions in "T" libraries, that are called with slRunProc in client code do not need to be registered.  SQLite custom functions that are used in SQL statements must be "registered" with sqlite3_create_function().  I created a single function in my "P" library to "register" all my SQLite custom functions, but you could use separate registration functions if you wanted to.  Either way, the "P" library needs be loaded when you attempt to execute an SQL statement that uses one of your SQLite custom functions.

Are all functions unloaded at once with a single call?

You don't really "unload functions".  You unload the ("P") library.  All SQLite custom functions defined with the library that you unload will then not work any more (SQL statement failures so have fun debugging your SQL code if you weren't expecting it).

If client crashes does this cause the server to need to be restarted  (see note at bottom of slRunProc in docs.)

As I understand it, if client app perma loads a "P" library and then crashes, the server won't unload the library.  This doesn't really hurt anything AFAIK.  Maybe it causes the server to tie up whatever resources are used when it loads and maintains a linked (DLL) library, but if your "P" library only contains functions/code that really need to be "permanent", it should be a pretty small footprint.

slRunProc (rsProcName String, blParm1 Long, blParm2 Long, bsParm3 String, bsParm4 String, [rsModChars String]) Long
Are all the parameters explained somewhere?  The first and last is in slRunProc


blParm1, blParm2, bsParm3 and bsParm4 are for you to use and define as needed.  They are passed on to your function in the "P" or "T" library.

10
Created a new thread on slRunProc  http://sqlitening.com/support/index.php?topic=9691.0
Pages: [1] 2 3 ... 10