Recent Posts

Pages: [1] 2 3 ... 10
1
You've got Questions? We've got Answers! / Re: Field Reports - Success Stories
« Last post by Frank W. Kelley on February 27, 2017, 04:04:35 PM »
SQLite/Lightning has been my go-to DB solution for so long now, I have difficulty remembering exactly what I used previously.

At the moment, I'm finishing up an application that mimics a radio station, allowing a user to program his own "station" on his PC (for those who have always felt they can do a better job of programming than the guys actually on the radio). Perhaps my 30 years in radio broadcasting had something to do with this idea. The program uses two MCI controls to allow overlapping of music. Each song can be edited to apply custom mix points and tempo-matching, just like on the radio. (By the way, I'm listening to "my" oldies station as I write this.)

The important point from this board's perspective is that under PBWin 10 and SQLite, the application can import song files (mp3 or wav), parsing the file names in the format "title_artist.wav", or just by title if there is no underscore or dash in the file name. Moreover, it also updates an existing database, recognizing when new songs have been added to the song source directory and deleting any that have been removed. And it keeps tabs on artists, so the listener can prevent the same artist repeating too often.

Oh, it does all this for a library of 500+ songs in 0.03274 seconds (time varies from run to run, but always under 0.04 seconds).

The power of PBWin's ARRAY command and SQLite makes it possible. Truly a remarkable combination.
2
You've got Questions? We've got Answers! / Re: SQLitening Error Code -13
« Last post by cj on February 11, 2017, 11:25:39 AM »
Duplicate column names can be used.  Had to do some reading about the modchars of slSel.
slSel "SELECT * FROM VOR, MTR",0,"D"


'negative error codes are from SQLitening
'%SQLitening_InvalidColumnNameNumber = -13 - Invalid column name or number


slSel

ModChars:


·    C = Will first do a slCloseSet(rlSetNumber). This will prevent error 14 (%SQLitening_InvalidSetNumber) but should be used with caution. Omiting set number or passing set number of zero will do the same thing.
·    D  = Allow duplicate column names. Not recommended if using slFN or slFNX.  because you will always get the first value returned.  SQLite does not normally return qualified column names.  SQLite will return C1 twice if you Select T1.C1, T2.C1. So the solution is to alias one of them with the As clause as follows Select T1.C1, T2.C1 as C1Again. There is a Pragma called "full_column_names" which forces SQLite to return qualified names, but does not seem to work if you Select *. Read up on it and use if you like.  I like using an alias because it is less code and more clear.


Here are 3 examples.
The last example shows how to allow duplicate column names using slSel with the "D" modchar.


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG


  'No duplicate column names so your statement valid if each column name is used instead of *
  REDIM sArray(0) AS STRING
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe "drop table if exists t2"
  slexe "create table t1(c1)"      'c1 unique column
  slexe "create table t2(c2)"      'c2 unique column
  slexe "insert into t1 values('one')"
  slexe "insert into t2 values('two')"
  DIM sArray() AS STRING
  slselAry "select c1,c2 from t1,t2",sArray(),"Q9"
  ? JOIN$(sArray(),$CR),,"Unique columns"



' Duplicate column names so AS used to make the column unique
  slexe "drop table if exists t1"
  slexe "drop table if exists t2"
  slexe "create table t1(c1)"
  slexe "create table t2(c1)"      'c1 is not unique
  slexe "insert into t1 values('one')"
  slexe "insert into t2 values('two')"
  REDIM sArray(0)
  slselAry "select t1.c1 AS hi,t2.c1 from t1,t2",sArray(),"Q9"
  ? JOIN$(sArray(),$CR),,"AS used"


  ' D modchar with slSel  Not recommended if using slFN or slFNX.  because you will always get the first value returned
  LOCAL c,cols AS LONG, sResult AS STRING
  slexe "drop table if exists t1"
  slexe "drop table if exists t2"
  slexe "create table t1(c1)"
  slexe "create table t2(c1)"      'c1 is not unique
  slexe "insert into t1 values('one')"
  slexe "insert into t2 values('two')"

  slSel "select * from t1,t2",0,"D"
  cols = slGetColumnCount
  IF cols THEN
    'add column names
    FOR c = 1 TO cols
      sResult+= slGetColumnName(c) + $TAB
    NEXT
    ASC(sResult,LEN(sResult)) = 13 'last comma to $CR
    DO WHILE slGetRow
      FOR c = 1 TO cols
        sResult+=slf(c)+ $TAB
      NEXT
      ASC(sResult,LEN(sResult)) = 13 'last comma to $CR
    LOOP
    ? sResult,, "D modchar with slSel
  END IF

END FUNCTION
3
You've got Questions? We've got Answers! / Re: SQLitening Error Code -13
« Last post by Fim on February 11, 2017, 11:23:50 AM »
* cannot be used with multiple tables, but the columns needed can be listed.
That solv the problem.
Thanks a lot.
/Fim W.
4
You've got Questions? We've got Answers! / SQLitening Error Code -13
« Last post by Fim on February 11, 2017, 07:21:37 AM »
CJ,
You inspired me to use other forms of JOIN.
This works ok:
SELECT * FROM VOR;
even this
SELECT * FROM MTR;
But with this, I get -13
SELECT * FROM VOR, MTR;
Is it an error in SQLitening or something else?
/Fim W
5
You've got Questions? We've got Answers! / Re: Missing records with JOIN
« Last post by cj on February 10, 2017, 02:18:15 PM »
CASE may give all results and add WHERE and GROUP BY may get what you want?

person   t1   t2   t3   result

cj   soap   nope   roap   neither   
cj   soap   nope   soap   t3   
cj   soap   grope   roap   neither   
cj   soap   grope   soap   t3   
cj   soap   soap   roap   t2   
cj   soap   soap   soap   all   


drop table if exists table1;
drop table if exists table2;
drop table if exists table3;
create table if not exists table1(person,t1);
create table if not exists table2(t2);
create table if not exists table3(t3);

insert into table1 values('cj','soap');
insert into table1 values('fin','pine');

insert into table2 values('nope');
insert into table2 values('grope');
insert into table2 values('soap');

insert into table3 values('roap');
insert into table3 values('soap');

select person,t1,t2,t3,
   case
     when t1=t2 and t1=t3 then 'all'
     when t1=t2           then 't2'
     when t1=t3           then 't3'
     else                 'neither'
   end as result
from table1,table2,table3 where person = 'cj'

6
You've got Questions? We've got Answers! / Re: Missing records with JOIN
« Last post by Fim on February 09, 2017, 11:55:45 AM »
I will test it.
/Fim W.
7
You've got Questions? We've got Answers! / Re: Missing records with JOIN
« Last post by Jean-Pierre LEROY on February 09, 2017, 10:00:51 AM »
Try LEFT JOIN ART instead of JOIN ART
8
You've got Questions? We've got Answers! / Missing records with JOIN
« Last post by Fim on February 09, 2017, 06:50:36 AM »
I have 3 tables MTR, VOR and ART  use this SQL-stament for reading records from table MTR supplemented with som data from table VOR and ART:
SELECT MTR.EK_KVANT, MTR.FK_KVANT, MTR.ORDERNR, MTR.OPNR, ART.ARTBEN, VOR.ARTNR, MTR.RESVECKA FROM MTR JOIN VOR ON VOR.ORDERNR = MTR.ORDERNR JOIN ART ON ART.ARTNR = VOR.ARTNR WHERE MTR.ARTNR = '2030-3360' AND SLUTUTTAGMARK = '' AND ABS(EK_KVANT) < ABS(FK_KVANT);

For some records i table VOR  there is no matching record i ART.
In that case, I do not get the record from MTR.

Can I use another form of the SQL-statament so I receive all records in MTR even vhen there is no matching record in ART?

/Fim W.
9
General Board / Re: PowerBASIC has been sold!
« Last post by Fredrick Ughimi on February 07, 2017, 07:30:10 PM »
Great news! Hope everything works out well for the new owners. 
10
You've got Questions? We've got Answers! / Re: Field Reports - Success Stories
« Last post by Fredrick Ughimi on February 07, 2017, 04:53:20 AM »
Hello All,

I have had and still having success with SQLitening. I developed all my business applications with SQLitening. Over the years I have successfully developed applications such as:

1. Hospital Management Information Software (Mega-Net HospitalPro)

2. Church Management Information Software (Mega-Net ChurchPro)

3. Enterprise Resource Planning Software (Mega-Net SuitePro)

4. Hotel Management Information Software (Mega-Net GuestsPro)

5. School Management Information Software (Mega-Net SchoolPro)

There are others for Pilgrimage, Anthropometry, Pharmacy, Gymnasium, etc that I don't market any more.

My gratitude and regards to everyone  that has made SQLitening possible. I also dough my hat to this awesome forum for all the assistance.

Best regards,
Pages: [1] 2 3 ... 10