RB & SQL

[Art Peters]

Thanks alot Barney,
I owe you a cool one.
Art


On Wednesday, February 26, 2003 4:12 AM, Barney <barney at custombased.com> wrote:
>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?
>>