RB & SQL

[Barney]

Art,

Here is one way of doing it.

//====Create Database======

 dim f as folderitem
 dim res as boolean
 Dim DB as vdatabase
  
  f=getsaveFolderItem("MyFileType", "DataBase1.vdb" ) //DB
  if f <> nil then 
   
    DB = new Vdatabase
    DB.Creator = "VALA"       // optional
    
    
    
    // //this is straight out of Valentine pdf's
    res = DB.Create( f, 1, 32 * 1024 )
    
    if(res = false) then
      MsgBox "Can't create database. Error: " + Str(DB.ErrNumber)
     end if


//===Open database create table and fields===

Dim db as vdatabase
Dim f as folderitem
dim res as boolean
Dim BO as VBaseObject
Dim fld as Vfield 
  
  f  = getopenfolderItem("any")
if f <> nil then  
db = new vdatabase
  
  res = db.open(f)
  
  if res = true then
    msgBox "dbbase Open"
  else
    msgBox "Sorry couldn't open"
    return
  end if
End if

  
  //create base object ( table )
  BO = Db.CreateBaseObject("Table1")

 //create fields
    fld = BO.createfield( "Id", 5) //VUshort
    fld = BO.createfield( "category", 10,30) //VString  ( 30 characters )
    fld = BO.createfield( "Comments", 26, 504) //Vvarchar
    fld = BO.createfield( "Cost_Price", 8) //VFloat
    fld = BO.createfield( "Delete", 2) //VBoolean
    fld = BO.createfield( "Purchase_Date", 12) //VDate
    fld = BO.createfield( "Qty", 4) //VShort

DB.flush


//===Add records====

Dim cur as vcursor
Dim err as boolean

cur = db.sqlselect("Select * from table1")  //DB is tucked away as a
property of application class etc
cur.field("id").setstring("1")
cur.field("category").setstring("Sports")
cur.field("Purchase_Date").setstring("01/02/2003")
err = cur.add()
db.flush


It pays to make DB a property somewhere like of an application class
then its there for good and you can refer to it throughout your project.

Hopefully no syntax errors in above but could be. Email any problems.

Barney

CUSTOMBASED - Database Systems
<http://www.custombased.com>




> Anyone please,
> 
> Is it possible to create an RB VDataBase as:
> 
> db = new VDataBase
> res = db.Create(ff,1,32*1024)    ff=folderitem
> 
> and then populate with tables using Sql as:
> 
> db = New VDatabase
> ok = db.Open(f)
> 
> s = "create table AntList "
> s = s + "("
> s = s + "AntIdx vvarchar not null unique, "
> ....
> s = s + ")"
> 
> err = db.SqlExecute(s)
> 
> db.Flush
> 
> and then use standard Sql INSERT INTO, UPDATE, etc.
> to add and extract data.
> 
> I can't seem to get the tables into the db using
> this procedure. I just wanted to know if it is
> my approach before spending more days on it.
> 
> It looks like I can do this from the TEST_
> routines. But, nothing I try gets the job done.
> 
> Thanks,
> Art
> 
> ps
> 
> It looks like (from TEST_) that different result
> codes are being generated for db.SqlExecute().
> Is there a list of these codes somewhere? Or what
> they should be for successful execution?
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina