SQLitening Support Forum

Here is a small snip from one of my programs.  The return values are all good, but the data is NEVER entered into the table even though it says it did.

    CategoryList$ = "'" + CategoryList$ & "'"
    KeyWords$     = "NULL"
    AuthorID$     = "'0'"
    Copywrite$    = "NULL"
    CompilerID$   = "'0'"
    Win32Version$ = "NULL"
    Comments$     = "NULL"
    SQL$ = "INSERT INTO ClipMaster VALUES (NULL,'" & ClipName$ & "','" & ClipPath$ & "'," & _
            CategoryList$ & "," & _ 
            KeyWords$     & "," & _
            AuthorID$     & "," & _
            Copywrite$    & "," & _
            CompilerID$   & "," & _
            Win32Version$ & "," & _
            Comments$     & ");"
    e& = slEXE(SQL$,"E")
    ClipID& = slGetInsertID

? SQL$ & $CRLF & "ClipID:" & STR$(ClipID&) & $CRLF & "Cat: " & CategoryList$


Here is what I get from the print line above:

Quote
INSERT INTO ClipMaster VALUES(NULL,"!aaatest","\\Undefined\","5;6;9;11","NULL","0","NULL","0","NULL","NULL");
ClipID: 986
Cat: "5;6;9;11"

Now, an interesting thing is that if CategoryList$ is null, the record is created just fine.  If this variable contains anything at all, the actual data is not written to the file even though I get return value of 0 and a valid record number from slGetInsertID.  I've tried changing the name of the variable, adding apostrophes, replacing "NULL" with just "", just about everything I can think of, but the results are always the same.

I'm stumped.  Any ideas?

I don't recall if I asked this before.  I didn't see anything after doing a quick search:

Depending on the number of records (Rows) in a table, the following snippet can be used to quickly calculate this number:

SQL$ = "SELECT COUNT(*) FROM MyTable;"
slSEL(SQL$,1)
slGetRow(1)
NumberOfRows& = VAL(slF(1,1))
slCloseSet(1)


The SELECT command uses the parameter COUNT to get the total number of records in 'MyTable'  We get the actual value by getting the value from the first 'column' in the record set (slF(1))

If your table contains more than 20 thousand records, this process may take a few seconds to execute.  Is there another way to calculate the number of rows in a table?

Due to a crashed copy of Win2000 Pro, the SQLitening.com server was out of commission since late Wednesday.  I'm relieved to report that the server has been replaced and is now running Windows Server O/S.... much more stable and hopefully a bit faster too.

Way back when, I was getting a GPF on the slGetInsertID command.  I thought at the time that it was caused by my using the Beta of PB-5.  However, I'm getting the GPF again in the following code:

' JPRO_COMPILER = PB/CC
' JPRO_COMPILETEMP = false

#COMPILE EXE
#INCLUDE "SQLitening.Inc"

FUNCTION PBMAIN() AS LONG
     LOCAL FamilyID AS QUAD
    
     f& = FREEFILE
     OPEN "members.csv" FOR INPUT AS #f&
     '---=== Skip Headings Row ===---
     INPUT #f&,FamilyName$, FirstName$, PhType$, Phone$, EMail$, DOB$, Anniv$, Login$, PW$
    
     e& = slOpen("agape.db3")
     IF e& <> 0 THEN
        ?slGetError
        a$ = WAITKEY$
        EXIT FUNCTION
     END IF
    
    
     LastName$ = ""
     MemberID& = 0
    
     DO
       IF EOF(f&) THEN EXIT DO
      
       INPUT #f&,FamilyName$, FirstName$, PhType$, Phone$, EMail$, DOB$, Anniv$, Login$, PW$
       '
       IF FamilyName$ <> LastName$ THEN
          LastName$ = FamilyName$
          MemberID& = -1
          FamilyID  = 0
         
          IF LEN(Phone$) = 7 THEN Phone$ = "320" + Phone$
         
          SQL$ = "INSERT INTO Families VALUES (NULL,'" & _
                    FamilyName$ & "','" + Phone$ + "',0,0,0,'" + Login$ + "','" + PW$ +"',NULL);"
          e& = slEXE(SQL$,"E")
          IF e& <> 0 THEN
             ?slGetError :? SQL$
             EXIT DO
          END IF
          '
          FamilyID = slGetInsertID  '<<<----- GETTING GPF HERE
          '
          ?:?FORMAT$(FamilyID) & " - " & FamilyName$,
       END IF
      
       INCR MemberID&
       SQL$ = "INSERT INTO Members VALUES (" & FORMAT$(FamilyID) & "," & FORMAT$(MemberID&) & "," & _
              "'" & FirstName$ & "',"  & _
              "'" & DOB$       & "'," & _
              "'" & Anniv$     & "'," & _
              "'" & PhType$    & "'," & _
              "320," & Phone$  & ", " & _
              "'" & EMail$     & "'," & _
              "0,0,0,0,"       & ", " & _
              "'" & Login$     & "'," & _
              "'" & PW$        & "'," & _
              "NULL,NULL);"
       e& = slEXE(SQL$,"E")
       IF e& <> 0 THEN
          ?slGetError & $CRLF & SQL$
          EXIT DO
       END IF
       ?FirstName$ & ", ";            
     LOOP                  
    
     ?:?:?"Done ";
     a$ = WAITKEY$
    
     CLOSE #f&
     slClose
END FUNCTION


It looks to me like this should work, but I can duplicate this easily.  Any time I use the slGetInsertID I get the GPF.

This is driving me nuts!!!

I have the following CGI application, its basically a modified version of one that has been running for years using Tsunami.

...

    e& = slOpen(dbname$,"E")
    IF e& THEN
       LogError (1,"Unable to open database",slGetError,e&,"dir_summary")
       msg("Unable to access the database. Please try Later")
       EXIT FUNCTION
    ELSE
       WriteCGI "Connected to local host" & STR$(e&)
    END IF
    '
    SQL$ = "SELECT * FROM Families ORDER BY FamilyName COLLATE NOCASE"
    e& = slSEL(SQL$,%FamilySet,"E")
    IF e& THEN
       LogError (1,SQL$,slGetError,e&,"dir_summary")
       msg("Unable to access the database. Please try Later")
       EXIT FUNCTION
    ELSE
       WriteCGI "<br>Select Successful:" & STR$(e&) & "<br>%FamilySet =" & STR$(%FamilySet) & "<p>"
    END IF
   
    slGetRow(%FamilySet)
    x$ = slF(1)
    WriteCGI "Value: " & x$
    slCloseSet(%FamilySet)
    slClose
    EXIT FUNCTION
...

I have some points in here for debugging, basically the WriteCGI lines.  I know the CGI stuff is working properly.

The problem is that my server is timing out, regardless of how long I set the time-out for. (I even tried with 5 minutes!!!).  The first two debug lines return as you'd expect:

Quote
Connected to local host 0
Select Successful: 0
%FamilySet = 1

The last one, that should print out after reading the database, never shows up.  The app simply 'hangs' when doing the first slGetRow and eventually times out.

I can run a query on database using the SQLite3 utility app and all 43 records are there.  I'm completely stumped on this.  I don't even know where to look for debugging.

Any ideas?

I am creating a web page displaying data from a table.  There are more records in the table than I can put on a single page.  The way CGI works, you can't "keep the file open" and pick up where you left off.  However, I can 'save' the last record that was displayed.

Is there any easy way to do a SELECT *, starting at a specific row?

The only alternative I can see now is to always SELECT * and then compare the result set in the DO WHILE loop until the matching 'starting row#' is located.

Is it legal to create a table in a database that is 'attached'?  The following code is generating an Error 1 and I don't see why.

FUNCTION Open_SQLManager_Database() AS LONG
'==========================================================
'Definition for the SQL Manager Database
'----------------------------------------------------------
     SQLMgrDB$ = gPath & "sqlmgr.db3"
     IF DIR$(SQLMgrDB$) <> "" THEN
        slAttach(SQLMgrDB$,"SQLMgr","E2")
     ELSE
        e& = slOPEN SQLMgrDB$,"C"
        e& = slEXE(SQL$,"E")
        IF e& <> 0 THEN
           mText$ = "Failed to Create sqlmgr database"
           mTitle$= "SQLite Returned:" & STR$(E&)
           EZ_MSGBOX($MAIN,mText$,mTitle$,"OK")
           FUNCTION = -1
           EXIT FUNCTION
        END IF
     END IF
     '---See that the proper table exists---
     T$ = slGetTableNames("'" & SQLMgrDB$ & "'")
     IF T$ = "" OR INSTR(T$,"tbldef") = 0 THEN
        SQL$ = "CREATE TABLE tbldef (" & _
               "database_name   text NOT NULL, " & _
               "table_name      text NOT NULL, " & _
               "Column_number   integer NOT NULL, " & _
               "column_format   text, " & _
               "column_maxlen   text, " & _
               "column_case     text, " & _
               "column_alias    text, " & _
               "column_comment  text, " & _
               "PRIMARY KEY (database_name, table_name, column_number))"
        e& = slEXE(SQL$,"E")
        IF e& THEN
           mText$ = "Failed to Create Definition Table" & $CRLF & "Stmnt: " & SQL$
           mTitle$= "SQLite Returned:" & STR$(e&)
           EZ_MSGBOX($MAIN,mText$,mTitle$,"OK")
           FUNCTION = -2
        END IF
     END IF
END FUNCTION


I know you can send any SQL command via slEXE but getting the information back sometimes confuses me (nothing new, I know )

In this example:

Quote
sql$ = "PRAGMA table_info("idtags");"
e& = slEXE(Sql$,"E")
IF e& THEN
    ?slGetError
    EXIT SUB
END IF

How would I get the actual data returned by this command?

Hi Fred,

I've been writing documentation for some time now on the www.SQLitening.com/Wiki pages.  I have one question for you (now, probably lots more to come over the next few weeks )

In slConnect, there are some additional rsModChars options:

• u  = Do not pass user name.
  
• c  = Do not pass computer name.
  
• i  = Do not pass IP address
  
  My docs don't show any of these being passed any place.  Where do these parameters fit in?  I assume they are passed by default unless one sets one or more of the three options above.
  
  Where these "do not" parameters added for security reasons?

I have been keeping notes and writing my own documentation (with the intent of publishing it for all) for a little while now.  Paul had mentioned the idea of creating a Wiki for documentation which sounded like a good idea to me.  So.......

I decided to create an 'unofficial' SQLitening web site.  I don't want to  compete with the discussions here in anyway, but I wanted a site that would allow me to build a documentation platform easily.  The site is: www.SQLitening.com with the primary purpose of being the "SQLitening Documentation Project".  I did install a forum along with the Wiki, but I did so mainly because forums are rather 'standard' affair for web sites.  Feel free to post there if you like, but for at least the time being, I consider this site to be the primary discussion. 

Of course, if Paul (or Fred) would prefer to move these discussions and/or the downloading to SQLitening.com, I would be more than happy to do that.

If anyone would like access to add/modify the pages on the Wiki, please drop me an email and I will create an account for you.  I would be most appreciative for any and all help, but I didn't want to leave the Wiki wide open for just anyone to modify.

Of course, all comments welcome.

I can issue the following select command in SQLite3.exe (the command line utility) and get the results I'm looking for, but in SQLitening, it throws an error -13.  Since the error is <0, I'm assuming that the issue is not with SQLite, but within the implementation of slSEL?

     SQL$ = "SELECT " & _
            "   Families.FamilyID," & _
            "   Members.FamilyID," & _
            "   Families.FamilyName," & _
            "   Members.FirstName," & _
            "   Members.MemberType," & _
            "   Members.EMail," & _
            "   Members.Phone," & _
            "   Families.Home_Phone" & _
            " FROM" & _
            "  Families" & _
            "  INNER JOIN Members ON (Families.FamilyID=Members.FamilyID)" & _
            " ORDER BY" & _
            "   Families.FamilyName," & _
            "   Members.MemberType"
     
     e& = slSEL(SQL$,%rSet1)


I am 110% sure all of the table and column names are typed correctly so I'm at a loss as to why this doesn't work.  I'm using the last v5 release.

The error states "Invalid column name or number.  So I exported the columns using SQLitening's slGetTableColumnNames  just to match what it sees with the command above.  The results are:

---Families---
FamilyID
FamilyName
LoginName
PW
Home_Phone
Anniversary
Photo
ShowInDir
IsActive
AgreeTOS
IsApproved
CreateDate
ChangeDate

---Members---
FamilyID
MemberID
MemberType
FirstName
DOB
PhType
Phone
EMail
AFCMember
Baptised
AcctStatus
Photo


Using the following code:
=================================
s$ = slGetTableColumnNames("families")
REPLACE $NUL WITH $CRLF IN s$
f& = FREEFILE
OPEN "columns.txt" FOR APPEND AS #f&
PRINT #f&, "": PRINT #f&, "---Families---": PRINT #f&, ""
PRINT #f&, s$
s$ = slGetTableColumnNames("members")
REPLACE $NUL WITH $CRLF IN s$     
PRINT #f&, "": PRINT #f&, "---Members---": PRINT #f&, ""
PRINT #f&, s$
CLOSE #f&

So it really seems like a bug in SQLitening to me. 

Ok, so I'm not a very good lyricists.....

I'm looking at ways to search a number of different fields in my database.  The traditional method is to read the field data and do an INSTR (or RegExpr if you're so inclined).  BUT, I'm wondering if the WHERE field LIKE clause could be used instead.  I can't find a lot of documentation on the LIKE clause though.

Any opinions on the best way to search for specific test in a SQL database?

Also, is there an option with the WHERE clause to specific "Not Null"?  So for example, if I wanted to get every row of data except those where a specific field was blank.

Generally speaking, if one tries to close a normal file that is not open, nothing major happens.  However, I've spent quite a bit of time trying to track down a problem where closing a record set that didn't need to be closed caused the whole application to fail. 

It would be nice if slCoseSet would either issue a silent error (with the "E" option), or just simply move on if the set is already closed.  I can't think of any reason why this should be considered an "error", at least not one to bail out an entire application over.

TIA

Is there a maximum length for a field?

I am trying to store a (potentially) large amount of text (think, source code) in a field.  I've tried plain 'TEXT' and 'BLOB' as the data type, but while no errors are generated when INSERTing the record, nothing is being saved.

Fred,

Before I go digging into the source, any idea why this would trip an error 1?


     slOpen dbName,"C"
     '
     '---See if the table exists---
     '
     TL$ = slGetTableNames(dbName) 


The slGetTableNames call returns Error 1: Unrecognized Token ":"

Ok, I'm trying to get my mind around the basics of SQLightning but I'm stumbling on some (I'm sure) very basic processes.

First, I'm not sure if I should use the 'slBuildInsertOrUpdate' function until I have a better handle on how these things work directly, but for now, that's what I'm using.

I have two tables that are identical in structure.  Column 1 is an AUTOINCREMENT INT primary key.  Column 2 is a simple string.  I'm using the following command to create the table which works just fine.

Quote
SQL$ = "CREATE TABLE 'iCompanies' ( 'companyID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'CompanyName' varchar NOT NULL );"

Now, I'm reading sequentially through a Tsunami database whose records contain various Comapany Names. (These are duplicated throughout the database).  What I'm trying to do populate the SQLighting Table with unique company names.  My assumption is that the Key (INT field) is automatically created for me whenever I insert a record.  Therefore, I am using this code to 'Insert or Update' this table:

Quote
slExe slBuildInsertOrUpdate("iCompanies", "0" & $NUL & InsCo$)

where InsCo$ is the company name, might or might not be unique as far as the SQL table is concerned.

On the very first execution of this statement, I get the error message:

Quote
19 = PRIMARY KEY MUST BE UNIQUE
Statement = Insert into iCompanies values (0,'Sepctra')

I also tried

Quote
slExe slBuildInsertOrUpdate("iCompanies", $NUL & InsCo$)

But that returns a 'parameter mismatch' (Statement = Insert into iCompanies values (",'Sepctra')

So what is the proper way to insert to a table with an autoincrement int key value?