SQLite Wrapper Functions

BackupUser · Post by **BackupUser** » Tue Apr 01, 2003 2:54 pm

Restored from previous forum. Originally posted by blueb.

BTW I love the SQLite Dll and the great little database program that
it accesses. Thanks so much guys for bringing it to Purebasic.

I'll second that. El_Choni and ebs...thank you!

With 19 functions, you are able to do anything with your data.

What might be nice, is for someone to use PB and make a duplicate of the "EasySQLite" (it's a Delphi thing) program so that users can more easily see how things are done in PureBasic.

--blueb

BackupUser · Post by **BackupUser** » Tue Apr 01, 2003 3:41 pm

Restored from previous forum. Originally posted by Saboteur.

I get a crash when try "SQLiteData(r,c)" in a field with NULL value.
Can you confirm this?

Win98 Athlon 1200 256DDR

BackupUser · Post by **BackupUser** » Tue Apr 01, 2003 4:35 pm

Restored from previous forum. Originally posted by Manolo.

Originally posted by Saboteur

I get a crash when try "SQLiteData(r,c)" in a field with NULL value.
Can you confirm this?

Hi,
Not is possible that return a field with NULL. Is very simple. Read or not read, but previus you need other code, not only this:

For example:

Code: Select all

SQL.s = "SELECT Festivo, Motivo FROM Festivos WHERE Festivo LIKE 'd%'"


                Result = SQLiteGetTable(0, SQL)
                If Result = #SQLITE_OK ;if is OK continue
                Rows = SQLiteRows() ;Fila
                Cols = SQLiteCols() ;Columna
                cr.s=Chr(10)
                
               
                   ClearGadgetItemList(#Gadget_Festivos)  
                    For Row.l = 1 To Rows
                      Dim Festivo.s(Cols-1)
                      For Col.l = 0 To Cols-1
                       Festivo(Col)=SQLiteData(Row, Col)
                      Next
                      
                        Year=Val(Mid(Festivo(0),1,4))
                        Day=Val(Mid(Festivo(0),1,2)) : Month=Val(Mid(Festivo(0),3,2))
                        Festivo(0)=FormatDate("%dd-%mm-%yyyy", Date(Year, Month,Day,0,0,0))
                        Linea$=Festivo(0)+cr+Festivo(1)
             
                        AddGadgetItem(#Gadget_Festivos_ListIcon542,-1,Linea$)
             
                        Linea$=""
                  Next
           
              Else
                MessageRequester("SQLite Error", "Tabla sin Datos = "+Str(Result), #MB_IconError|#MB_OK)
              EndIf

Saludos,
Manolo

BackupUser · Post by **BackupUser** » Tue Apr 01, 2003 5:27 pm

Restored from previous forum. Originally posted by Saboteur.

Originally posted by Manolo

Hi,
Not is possible that return a field with NULL. Is very simple. Read or not read, but previus you need other code, not only this:

Saludos,
Manolo

OK, this is ok. But, have you tried to Insert only 'Festivo' and let 'Motivo' with a null value?
I can read first column, the second column crash at read.

Now, I am inserting empty strings to fix it.
Thanks for your answer...

o sea, gracias por tÃ³...

BackupUser · Post by **BackupUser** » Wed Apr 02, 2003 9:33 am

Restored from previous forum. Originally posted by El_Choni.

I'll take a look ASAP, I don't have PB right now.

El_Choni

BackupUser · Post by **BackupUser** » Fri Apr 04, 2003 9:30 pm

Restored from previous forum. Originally posted by Saboteur.

I had tried version 1.32, and there are no more problems with null values, it's OK...

but, had somebody tried to insert images (longer> 64k)? binary files need to be encoder, and there are a problem with long strings.

Win98 Athlon 1200 256DDR ATI RADEON 9000

BackupUser · Post by **BackupUser** » Fri Apr 04, 2003 9:57 pm

Restored from previous forum. Originally posted by El_Choni.

Well, of course you can't use strings>64 K, it's a known PB issue. But you can use allocated memory instead to uuencode your images and pass the MemoryID() to the SQLite function. Anyway, I would put the image path instead of the entire image in the database if I were you (much faster).

El_Choni

BackupUser · Post by **BackupUser** » Sat Apr 05, 2003 4:52 pm

Restored from previous forum. Originally posted by Saboteur.

Originally posted by El_Choni

Well, of course you can't use strings>64 K, it's a known PB issue. But you can use allocated memory instead to uuencode your images and pass the MemoryID() to the SQLite function. Anyway, I would put the image path instead of the entire image in the database if I were you (much faster).

El_Choni

Yes, I'm playing with database and want to know how handle binary files.

Actually I use this function:

Code: Select all

Procedure.s SQLiteField(row.l, col.s)  ; col is a string
  cadena.s=""
  For f=0 To SQLiteCols()-1
    If SQLiteData(0,f)=col
      cadena=SQLiteData(row,f)
      f=SQLiteCols()-1
    EndIf
  Next f
  ProcedureReturn cadena
EndProcedure

I change the database continuosly and fields change its order, with this procedure I can find a value by its NAME. Could be interesting adding it to your wrapper.

Hasta luego.

Win98 Athlon 1200 256DDR ATI RADEON 9000

BackupUser · Post by **BackupUser** » Sat Apr 05, 2003 4:57 pm

Restored from previous forum. Originally posted by El_Choni.

Good idea, gracias

UPDATE: added SQLiteField() for 1.33. If you need it now, e-mail me and I'll send you the lib (beta).

El_Choni

BackupUser · Post by **BackupUser** » Sat Apr 05, 2003 6:55 pm

Restored from previous forum. Originally posted by Manolo.

Originally posted by El_Choni

Good idea, gracias

El_Choni

I think that not is one good idea Choni, because this is very easy with sql directly, for example:

SELECT Clave, Nombre,Privilegio FROM identity WHERE Clave LIKE '"+MyPassword'

And not is neccesary call to function. If Result=OK you only need this code:

cadena=SQLiteData(1,1)or 1,2, etc without for, next and other "pamplinas"

Remember the last month and problems unnecesaries to build new functions for this. SQL solve the problem. And the old math is solve with easy SELECT's in SQL.

"Non fai falta pra nada meu amigo"
Buen fin de semana

Manolo

BackupUser · Post by **BackupUser** » Sat Apr 05, 2003 7:53 pm

Restored from previous forum. Originally posted by El_Choni.

You're right, Manolo. Anyway, too late: it's been already done and, since PB only links the functions used in an app, there's no harm in including it, is it?

GraciÃ±as anyway,

El_Choni

BackupUser · Post by **BackupUser** » Sat Apr 05, 2003 10:38 pm

Restored from previous forum. Originally posted by Saboteur.

@El_Choni

UPDATE: added SQLiteField() for 1.33. If you need it now, e-mail me and I'll send you the lib (beta).

Yaaa?? Already? In 2 hours? You are the Master...

@Manolo

I think that not is one good idea Choni, because this is very easy with sql directly, for example:
SELECT Clave, Nombre,Privilegio FROM identity WHERE Clave LIKE '"+MyPassword'
And not is neccesary call to function. If Result=OK you only need this code:
cadena=SQLiteData(1,1)or 1,2, etc without for, next and other "pamplinas"
Remember the last month and problems unnecesaries to build new functions for this. SQL solve the problem. And the old math is solve with easy SELECT's in SQL.

Yes, it is possible, but...
1. if you have a table with n fields, is more readable
----select * from table; than
----select field1, field2, field3 .... fieldN from table
2. in the code, is more readable too, and this is the first problem i've found. I don't remember the fields position, and need the database script.
----SQLiteField(1,"name"); than
----SQLiteData(1,3)
3. if you insert a field in a large table, well "select * from ...", you'll have to let the field at last position to avoid problems with writed code.

Anyways, I'm using that function already, it was a sugestion.

Win98 Athlon 1200 256DDR ATI RADEON 9000