[Gambas-user] Databases, please help me!

Sat Feb 6 11:52:50 CET 2010

Maybe this helps (it's part of a project i did)

<code>
' Gambas class file

rsD AS Result
rsK AS Result
rfield AS ResultField
ctrl AS Control
MColl AS NEW Collection
FHand AS File
p AS picture
Mandat AS String
PaTag AS String[]
Id AS Integer

PRIVATE CONST thumbSize AS Integer = 90
bigone AS Integer = 490

' we read the photo from the tag of the form
PUBLIC SUB Form_Show()
DIM tmp AS String
DIM sData AS String 

  IF ME.Tag THEN
    PaTag = Split(ME.Tag, "=")
    Mandat = PaTag[0]
    Id = Val(PaTag[1])
  END IF

  ME.text = Mglobal.U_AgenNom & " - Photo's du bien " & Mandat

  ' Set options for GridView columns
  GridViewThumbs.Columns.Count = 5
  GridViewThumbs.Columns[0].Width = 20
  GridViewThumbs.Columns[0].Text = ""
  GridViewThumbs.Columns[1].Width = thumbSize * 1
  GridViewThumbs.Columns[1].Text = "Thumb"
  GridViewThumbs.Columns[2].Width = 0
  GridViewThumbs.Columns[2].Text = "Id"
  GridViewThumbs.Columns[3].Width = 0
  GridViewThumbs.Columns[3].Text = "description"
  GridViewThumbs.Columns[4].Width = 0
  GridViewThumbs.Columns[4].Text = "filename"
  
  ' get um
  rsK = db.Exec("select Thumb, Id, description from Photos where
prop_num = " & Str(Id) & " ORDER BY sequence")
  IF rsK.available = TRUE THEN 
    FOR EACH rsK
      sData = rsK!Thumb.Data 
      ' write the photo to local file
      File.Save("~/TempPhot" & Str(rsK!Id) & ".jpg", sData)
      ' Fill with the image
      AddImage("~/TempPhot" & Str(rsK!Id) & ".jpg", Str(rsK!Id), rsK!
description) 
      KILL "~/TempPhot" & Str(rsK!Id) & ".jpg"
    NEXT
      ' Set main image to view - if we have one
    IF GridViewThumbs.Rows.Count > 0 THEN 
      GridViewThumbs.Row = 0
      SelectImage()
    END IF
  END IF 
END

PRIVATE FUNCTION AddImage(Path AS String, Id AS String, description AS
String) AS Boolean 
  DIM r AS Integer
  DIM p AS Picture
  ' See if we can load the picture
  p = CreateThumb(Path, "T")
  ' If we created the thumb then add it
  r = GridViewThumbs.Rows.Count
  GridViewThumbs.Rows.Count = r + 1
  GridViewThumbs[r, 1].Picture = p
  GridViewThumbs[r, 2].Text = Id
  GridViewThumbs[r, 3].Text = description
  
  GridViewThumbs.Rows.Height = thumbSize
  RETURN TRUE
CATCH 
  Message.Error(Error.Text)
  RETURN FALSE
END

' Create a thumb from the path to an image
' The size of the thumb is set by the const thumbSize, ,or Bigsize
PRIVATE FUNCTION CreateThumb(Path AS String, Wo AS String) AS Picture
  DIM img AS Image
  DIM scale AS Float
  ' Load an image becuase you can stretch and image - but not a picture
  img = Image.Load(Path)
  IF Wo = "T" THEN
    IF img.Width > thumbSize OR img.Height > thumbSize THEN 
      ' Calc factor to scale isotropic
      scale = Min(thumbSize / img.Width, thumbSize / img.Height)
      ' Stretch image and return a picture
      RETURN img.Stretch(img.Width * scale, img.Height * scale,
TRUE).Picture
    ELSE 
      RETURN img.Picture
    END IF
  ELSE ' we have a big one
    IF img.Width > bigone OR img.Height > bigone THEN 
      ' Calc factor to scale isotropic
      scale = Min(bigone / img.Width, bigone / img.Height)
      ' Stretch image and return a picture
      RETURN img.Stretch(img.Width * scale, img.Height * scale,
TRUE).Picture
    ELSE 
      RETURN img.Picture
    END IF
  
  END IF
END

' When the user clicks on a row make it the selected image
PUBLIC SUB GridViewThumbs_Click()
  SelectImage()
END

'''
''' Functions
'''

PRIVATE SUB SelectImage()
  DIM row AS Integer
  DIM i AS Integer
  DIM sData AS String
  DIM scale AS Float
  
  row = GridViewThumbs.row
  IF row < 0 THEN RETURN ' if it was empty !
  FOR i = 0 TO GridViewThumbs.Rows.Count - 1
    IF i = row THEN 
      ' Set marker for selected row
      GridViewThumbs[i, 0].Picture = Picture["Images/go-next.png"]
    ELSE 
      ' No marker on other rows
      GridViewThumbs[i, 0].Picture = NULL
    END IF
  NEXT
  ' Update full size image
  IF Mid(GridViewThumbs[row, 2].Text, 1, 1) = "=" THEN 
    sData = Mid(GridViewThumbs[row, 2].Text, 2)
    PictureBoxFullSize.Picture = CreateThumb(sData, "B")
  ELSE ' it's allready in the database 
    rsK = db.Exec("select Id, www from Photos where Id = " &
GridViewThumbs[row, 2].Text)
    IF rsK.available = TRUE THEN 
      sData = rsK!www.Data 
      ' write the photo to local file
      File.Save("~/TempPhot" & Str(rsK!Id) & ".jpg", sData)
      ' Fill with the image
      PictureBoxFullSize.Picture = Picture["~/TempPhot" & Str(rsK!Id) &
".jpg"]
      KILL "~/TempPhot" & Str(rsK!Id) & ".jpg"
    END IF
  END IF
  PictureBoxFullSize.Width = PictureBoxFullSize.Picture.Width
  PictureBoxFullSize.Height = PictureBoxFullSize.Picture.Height
  description.Y = PictureBoxFullSize.Height + 8 
  description.text = GridviewThumbs[row, 3].text 
  description.Tag = description.text
  Form_Resize
  
  CATCH
END

' Remove the selected image from the grid
PUBLIC SUB ToolButtonRemove_Click()
  DIM r, c AS Integer
  ' Make sure we have something in the grid to remove
  IF GridViewThumbs.Rows.Count = 0 THEN RETURN 
  FOR r = GridViewThumbs.Row TO GridViewThumbs.Rows.Count - 2
    FOR c = 0 TO GridViewThumbs.Columns.Count - 1
      GridViewThumbs[r, c].Picture = GridViewThumbs[r + 1, c].Picture
      GridViewThumbs[r, c].Text = GridViewThumbs[r + 1, c].Text
    NEXT
  NEXT
  GridViewThumbs.Rows.Count = GridViewThumbs.Rows.Count - 1
  GridViewThumbs.Rows.Height = thumbSize
  PictureBoxFullSize.Picture = NULL
  IF GridViewThumbs.Rows.Count > 0 THEN 
    IF GridViewThumbs.Row = GridViewThumbs.Rows.Count THEN 
      GridViewThumbs.Row = GridViewThumbs.Rows.Count - 1
    END IF
    SelectImage()
  END IF
END

' Move the selected image up the grid
PUBLIC SUB ToolButtonUp_Click()
 DIM r, c AS Integer
  DIM tempText AS String
  DIM tempPicture AS Picture
  r = GridViewThumbs.Row
  IF GridViewThumbs.Rows.Count = 0 OR r = 0 THEN RETURN 
  FOR c = 0 TO GridViewThumbs.Columns.Count - 1
    tempText = GridViewThumbs[r - 1, c].Text
    tempPicture = GridViewThumbs[r - 1, c].Picture
    GridViewThumbs[r - 1, c].Text = GridViewThumbs[r, c].Text
    GridViewThumbs[r - 1, c].Picture = GridViewThumbs[r, c].Picture
    GridViewThumbs[r, c].Text = tempText
    GridViewThumbs[r, c].Picture = tempPicture
  NEXT 
  GridViewThumbs.Row = r - 1
END

' Move the selected image down the grid
PUBLIC SUB ToolButtonDown_Click()
  DIM r, c AS Integer
  DIM tempText AS String
  DIM tempPicture AS Picture
  r = GridViewThumbs.Row
  IF GridViewThumbs.Rows.Count = 0 OR r = (GridViewThumbs.Rows.Count -
1) THEN RETURN 
  FOR c = 0 TO GridViewThumbs.Columns.Count - 1
    tempText = GridViewThumbs[r + 1, c].Text
    tempPicture = GridViewThumbs[r + 1, c].Picture
    GridViewThumbs[r + 1, c].Text = GridViewThumbs[r, c].Text
    GridViewThumbs[r + 1, c].Picture = GridViewThumbs[r, c].Picture
    GridViewThumbs[r, c].Text = tempText
    GridViewThumbs[r, c].Picture = tempPicture
  NEXT 
  GridViewThumbs.Row = r + 1
END

' Add a image to the GridView
PUBLIC SUB ToolButtonAdd_Click()
  DIM imageFile AS String
  Dialog.Filter = ["*.png", "Images png", "*.gif", "Gif images",
"*.jpg", "JPEG Images"]
  Dialog.Title = "Selectionner vos photos"
  IF Dialog.OpenFile(TRUE) THEN RETURN
  FOR EACH imageFile IN Dialog.Paths
    IF AddImage(imageFile, "=" & imageFile, "") THEN 
      ' if we were able to add then image then show it
      GridViewThumbs.Row = GridViewThumbs.Rows.Count - 1
    END IF
  NEXT
  SelectImage()

CATCH
  Message.Info(Error.Text)
END


PUBLIC SUB writeThumb(photoID AS Integer, thumb AS Picture)
'  DIM fileName AS String

'  fileName = "/tmp" &/ "thumb-" & photoID & ".jpg"
'  thumb.Save(fileName)
'  $conn.EXEC("UPDATE PhotoThumb SET thumb=LOAD_FILE('" & fileName & "')
WHERE photo_id=" & photoID)
'  KILL fileName
END
' Funktioniert natürlich nur wenn der Datenbank-Server auch Zugriff auf
die Datei hat. Sad 
 'OR OR /:
' TO put a file inside a blob field: 
 
' DIM sData AS String 
' sData = File.Load("/file/path") 
' MyResult!MyBlobField = sData 
 

PUBLIC SUB Close_Click()
' we cancel all, do nothing
  ME.CLOSE   
END

PUBLIC SUB ToolButton1_Click()
  message.Info("Vous pouvez enlever, ajouter et trier les images\nLe
sequence des images ici, sera celle de l'internet\naprès avoir fini le
travail, il suffit d'appuier sur 'Fermer'\net la nouvelle groupe
d'images sera transferer")
END

PUBLIC SUB description_LostFocus()
  IF description.Tag <> description.Text THEN 
    IF GridViewThumbs.Row = 0 THEN GridViewThumbs.Row = 1
    GridviewThumbs[GridviewThumbs.Row - 1, 3].Text = description.Text 
  END IF  
END

PUBLIC SUB Chercher_Click()
DIM Hstr AS File
DIM r, c AS Integer
DIM tmp AS String

' here we do our thing (update the data base and xfer les photos)  
' we first see if there are existing photos (Id), update there sequence
' then we add and transfert the new ones, and delete deleted ones
' is rather complex, so we do this asyngronously
' here we write a file with all data, and launch a shell script that
does it all
' to be sure we control this, we will write the script too

' we write lines in the form : Id or =filename|Prop_num|description
(with " translated)|sequence|Mandat|
  IF GridViewThumbs.Rows.Count = 0 THEN RETURN 
' we have something, we donnot allow to suppress all here !
  Hstr = OPEN USER.Home & "/rek_trans.txt" FOR CREATE 
  IF Hstr THEN 
    FOR r = 0 TO GridviewThumbs.Rows.Count - 1
      tmp = GridviewThumbs[r, 2].Text & "|" & Str(Id) & "|" & Replace
$(GridviewThumbs[r, 3].Text, "\"", "'") & "|" & Str(r) & "|"
      tmp = tmp & Mandat & "|"
      PRINT #Hstr, tmp 
    NEXT 
    CLOSE Hstr
    ' now we start up the other process
  SHELL User.Home & "/request/request_transfert.gambas " &
Mglobal.U_AgenRep
  END IF
'and finally we close
  ME.CLOSE 
END

PUBLIC SUB Form_Resize()
  PictureBoxFullSize.Move(PictureBoxFullSize.X, PictureBoxFullSize.Y,
ME.W - ((GridViewThumbs.W + 20) + 6), ME.H - 35)
  Panel2.Move(ME.W - (Panel2.W + 6), ME.Height - 27) 
  bigone = PictureBoxFullSize.W 

END

</code>

Steven


Le samedi 06 février 2010 à 11:02 +0100, M. Cs. a écrit :

> I'm repeating my question, since the documentation is quite obsolete in this
> topic:
> 
> How can I open a picture with Gambas2 ("/home/me/pic.jpg"), and put it into
> a sqlite database table as a blob?
> How can I use the picture stored in the blob to display as a Picture?
> 
> Is this solvable at all?
> 
> Csaba
> 
> P.S.: How can I join to the documentation writers?
> ------------------------------------------------------------------------------
> The Planet: dedicated and managed hosting, cloud storage, colocation
> Stay online with enterprise data centers and the best network in the business
> Choose flexible plans and management services without long-term contracts
> Personal 24x7 support from experience hosting pros just a phone call away.
> http://p.sf.net/sfu/theplanet-com
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user