SQLitening help

[Douglas McDonald]

Well I'm starting on SQLitening again. i see many samples but I'm looking for one that is very simple.

I think I can create the DB and a table ok but I'm having trouble reading the data out of the DB. I don't understand the setnumber suff at all. I see references to it all the time in the help file but I don't is it's actual use anyplace.

Does anyone has a small SIMPLE sample program that is a local DB only

1 create & open DB
2 create table and some fields
3 show how to add some data
4 show use of the slSel function to get the set number
5 show the use of slSelStr & slGetRow to get entire row into srting and loop through records
6 show a simple sql statment to get one field form a record
7 show how to add a record
8 show how to modify a record
9 show / explain slF,slFN,slFX slFNX

Again, I just don't get when to use or how to get or make the setnumber or when to use it.

Sorry but I don't know anything about databases. I've used SQLtools a little but that's it. I have read the SQLitening help file but it assumes you know all the basics already. I've studied the samples but again, they are for people that have a basic understanding of DB's

As I said I think I have created a new DB and a table with fields and put in data but I'm stuck on reading the data back out. I'd post the code but I forgot it at work, I can post tomorrow.

I really want to use SQLitening for this project it's perfect for it I think. It is just a simple parts inventory program, maybe 50k worth of parts. I the end I want to run it in client / server mode but for now I just want to be able to enter data, read it all back out into a listview and then be able to do searches for parts & stock.

I know this is a lot to ask. I wish there was a SQLitening for dummy's book.

One last thing, am I correct that SQLitening is just a "wrapper / interface" for PowerBasic to the SQLite3 database ?? or is it different all together. I assume its a "wrapper / interface" or why not just use SQLite3 directly?

Sorry I'm asking what must be stupid questions but all the documentation is directed to people that already know all this stuff.

So I'm asking for a dummies guide to SQLitening for dummies. As soon as I 'get it' / understand I'll kick myself for not understanding.

BTW is there a SQLite3 DB viewer? It would be nice to know if i really do have data in the table I created. Also is there a good book on SQL statements and how to use them or Maybe a good web site that has that info in a format one can understand?

thanks

Doug

[Jean-pierre Leroy]

Hi Doug,

You may have a look to this FireFly Project I made one year.

http://www.planetsquires.com/protect/forum/index.php?topic=2201.0

It illustrates some aspects of SQLitening you're asking for.

Hope that helps.
Jean-Pierre

[Rolf Brandt]

One last thing, am I correct that SQLitening is just a "wrapper / interface" for PowerBasic to the SQLite3 database ?? or is it different all together. I assume its a "wrapper / interface" or why not just use SQLite3 directly?

SQLitening is far more than a wrapper or interface for SQLite3 databases. It is a full Client/Server environment for SQLite3. With just one line of code (slConnect) you can change your application from a local single user program to a multiuser application with LAN (cable), WLAN, or even internet access. The server has a very small footprint, is (sq)litening fast, and needs almost no configuration.

If you use it locally only then it works as a wrapper and you could use the SQLite dll directly, but if there is a minimal chance that your application will go multiuser sometime I recommend to use SQLitening from the beginning.

BTW is there a SQLite3 DB viewer?

There are quite a few. One of the best is SQLite Expert. You can download the personal (free) version here:

http://www.sqliteexpert.com/download.html

I had put a while ago a SQLitening Database Manager project on the forum. I didn't finish it completely yet (time is an illusion...), so it is a bit faulty still. But it will show most of the routines you know. You can download the source code here.

http://www.planetsquires.com/protect/forum/index.php?topic=2133.0

P.S.:
I will create a very simple sample to illustrate the functions you are looking for.

[Douglas McDonald]

Thank you both very much. I'm sure once I get the general idea of it I'll have no problems. Rolf, looking forward to the sample. And thank you Jean-Pierre.

Doug

[Paul Squires]

Also, don't be shy to ask questions over on http://www.sqlitening.com/support/index.php as well.  Fred frequents that support forum all the time and usually has SQLitening answers very quickly.

[Douglas McDonald]

Ok, thank's Paul I'll be sure to ask questions there. Hopefully I'll get the hang of it soon. Made good progress today getting some of the basics down.

Doug

[Rolf Brandt]

Here I put a simple example:

SQLitening for Beginners
http://www.planetsquires.com/protect/forum/index.php?topic=2783.0

[Peter House]

This is a great forum with helpful, courteous people.

Everyone here should raise their right arm high and reach around and PAT YOURSELVES ON THE BACK!!!

Thanks to all who participate.

[Douglas McDonald]

Thank you all again, I think I have the basics down. I can create, fill and read a DB. There is a lot more to learn but its a start. Thank you all for the samples.

Seems I'll get to play with it all day today since there is a blizzard here in Tulsa, hope the power stays on.

Doug

[Douglas McDonald]

Rolf,

Thank you for your help and the sample. In your sample it looks like you based the DB index(row number off the listview index / rowID. There is no record number in the table you created unless I missed something.

Is there away to get and use the DB record number to index into the dB? I'm a bit lost on this point. In SQL tools i can get the row number (index) and do all kinds of stuff based on the row number.

I'm missing something basic here.

Next I'm having a problem searching my DB. I've done pretty good so far getting things to work but stuck here.

The table looks like this:

Code Select Expand

$fieldStr = "discription,pn,package,qty,suppler,volt,optvolts,ohms,optohms,amps,optamps,cap,optcap,power,optwatts,inductance,optind,tol,memo"


I think I'm doing the SQL statment wrong. I know its hard to tell without all the code.

Code Select Expand

Sub SearchBy()
   Local rec As String     
   Local Sql As String
   Local Discript As String  'discription
   Local sFor As String      'for what?
   Local eType As String     'volts,ohms,amps ect....
   Local sValue As String    'the value
   Local index As Long
   
   index = FF_ComboBox_GetCurSel( HWND_ENTRY_COMBOSEARCHBY )
   Discript = FF_ComboBox_GetText( HWND_ENTRY_COMBOSEARCHBY, index )
   index = FF_ComboBox_GetCurSel( HWND_ENTRY_COMBOWHERE )
   eType = FF_ComboBox_GetText( HWND_ENTRY_COMBOWHERE, index )
   
   If  Discript = "Discription" Then
      tmpFor$ = FF_TextBox_GetText( HWND_ENTRY_TXTFOR )
      tmpVal$ = FF_TextBox_GetText( HWND_ENTRY_TXTVAULE )
      If eType = "None" Then
         sql = "select * from parts where discription = " & tmpFor$
      Else
         sql = "select * from parts where discription = " & tmpFor$ & " and " & etype & "= " & tmpVal$
      End If   
   End If
   If  Discript = "PN" Then
      tmp$ = FF_TextBox_GetText( HWND_ENTRY_TXTFOR )
      sql = "select * from parts where pn = '" & tmp$  & "'"
   End If
   slExe Sql   
   slGetRow
   rec = slFN("discription") & "'" & slFN("pn") & "'" &  slFN("package") & "'" &  slFN("qty") & "'" &  slFN("suppler") & "'" &  slFN("volt") & "'" &  slFN("optvolts")_
   & "'" &  slFN("ohms") & "'" &  slFN("optohms") & "'" &  slFN("amps") & "'" &  slFN("optamps") & "'" &  slFN("cap")& "'" &  slFN("optcap") & "'" &  slFN("power") & "'" &  slFN("optwatts")_
   & "'" &  slFN("inductance") & "'" &  slFN("optind") & "'" &  slFN("tol")
   getOne rec
   EnteryFillScreen
End Sub

I attached the program. In the search area select PN from the first combo box, enter 18F13K50 in the "FOR" txt box, select "NONE" in the second combo box then click GO. I get an invalid set number -14

I still don't under stand the set number stuff I guess.

Sorry the code's not very organized. I've haven't had a chance to orginize it and there's been lots of moving form code around, was a few forms and now it just one and a popup dialog.

Thanks
Doug

[Rolf Brandt]

In your sample it looks like you based the DB index(row number off the listview index / rowID. There is no record number in the table you created unless I missed something.

No, RowId is an internal record number in SQLite databases.

"Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid." (http://www.sqlite.org/version3.html)

So if you create a column (field) with the name RecordNo and define it as "INTEGER PRIMARY KEY" this will also hold the rowid.

Is there away to get and use the DB record number to index into the dB? I'm a bit lost on this point. In SQL tools i can get the row number (index) and do all kinds of stuff based on the row number.

Sure. If you have not defined your own RecordNo column you can use the rowid. But you must call the rowid explicitely in your SELECT.
Example: "SELECT rowid,* FROM parts WHERE rowid = ... etc.

It's after midnight here right now. I am going to have a look into the code in the morning.

[Rolf Brandt]

I attached the program. In the search area select PN from the first combo box, enter 18F13K50 in the "FOR" txt box, select "NONE" in the second combo box then click GO. I get an invalid set number -14

You are getting error -14 because there is no set. This occurs because of this line in your SearchBy Sub;
   slExe Sql   
It should be:
   slSel Sql
That will retrieve the records.

You still will get 0 records. That leads us to another problem - your way of saving data. The problem is in the fillStructure function.

'"discription,pn,package,qty,suppler,volt,optvolts,ohms,optohms,amps,optamps,cap,optcap,power,optwatts,inductance,optind,tol"
Function = de.discription & $Nul & de.pn & $Nul & de.package & $Nul & Str$(de.qty) & $Nul & Str$(de.optSuppler) & $Nul & de.volts _
& $Nul & Str$(de.optvolts) & $Nul & de.ohms & $Nul & Str$(de.optOhms) & $Nul & de.amps & $Nul & Str$(de.optAmps) & $Nul & _
de.Cap & $Nul & Str$(de.optcap) & $Nul & de.power & $Nul & Str$(de.OptWatts) & $Nul & de.induct & $Nul & Str$(de.optInd) _
& $Nul '& Str$(de.tol) & de.memo

Your Type entryFieldsType structure assigns fixed field lengths to each field. This means if you save the value "18F13K50" to column (field) PN you are saving in reality "18F13K50                        ", the value + 24 spaces, since your definition is
pn As String  * 32.

You could prevent this by either trimming each field of the structure (Trim$(de.pn) or not using the struct at all, but saving from the textboxes. Saving the fields untrimmed would also result in unnecessary big databases.

'"discription,pn,package,qty,suppler,volt,optvolts,ohms,optohms,amps,optamps,cap,optcap,power,optwatts,inductance,optind,tol"
Function = de.discription & $Nul & de.pn & $Nul & de.package & $Nul & Str$(de.qty) & $Nul & Str$(de.optSuppler) & $Nul & de.volts _
& $Nul & Str$(de.optvolts) & $Nul & de.ohms & $Nul & Str$(de.optOhms) & $Nul & de.amps & $Nul & Str$(de.optAmps) & $Nul & _
de.Cap & $Nul & Str$(de.optcap) & $Nul & de.power & $Nul & Str$(de.OptWatts) & $Nul & de.induct & $Nul & Str$(de.optInd) _
& $Nul '& Str$(de.tol) & de.memo

You could use instead:
Function = Trim$(de.discription) & $Nul & Trim$(de.pn) & ...

I attached the project with the changes. I manually trimmed the field in your sample so it works now. I did not make any changes to your fillStructure function.

[Douglas McDonald]

Thank you Rolf. I understand the RowID better now. I had a feeling that the fixed length strings were causing problems that was going to be my next thing to try. I totally missed the slExe Sql error I would have looked for that for a long time.

I really do like using type structures but could just as easily just use an array or just use the text boxes directly as you suggest, it's just not as clean or readable. Yes I see the structure must go and I'll just work with strings and trim$ everything.

thank you very very much

Doug

[Rolf Brandt]

Glad I could help you.

[James Gant]

I just purchased FF3. I think this tool is going to save me a lot of time. I working a new project using Sqlite. SQLitening and FF3 are the tools I need. Small, Fast, Reliable

Here are a couple more Sqlite managers.

SQLiteSpy -  Ralf Junker site of excellent Delphi tools. It is free for personal use and donations are welcome. This tool is extremely fast.
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index

SQLite2009 Pro Enterprise Manager - Osen Kusnadi site of excellent ActiveX tools for VB6. Donations are welcome. It includes an great ODBC Driver.
This tool has an import option, from Access or Sql Server, faster than I have ever seen.
http://osenxpsuite.net/?xp=3

I am new to the forum and it seems everyone that works with anything PowerBasic is more than willing to help others. Thanks to all!