Author Topic: SQLitening Error Code -13  (Read 194 times)

Fim

  • Code Warrior
  • **
  • Posts: 90
    • View Profile
    • Fims sajt
SQLitening Error Code -13
« 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

Fim

  • Code Warrior
  • **
  • Posts: 90
    • View Profile
    • Fims sajt
Re: SQLitening Error Code -13
« Reply #1 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.

cj

  • Master Geek
  • ****
  • Posts: 518
    • View Profile
Re: SQLitening Error Code -13
« Reply #2 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
« Last Edit: February 11, 2017, 11:31:45 AM by cj »