Page 1 of 1
Changing SQLite Table Structure Online
Posted: Wed Dec 23, 2009 5:32 am
by sudip
Hello All,
We always need to change our existing table structure during creation or updradation of a project. If we use dbf it is very easy (I created one during Clipper days and it works fine till now. I you want I shall publish it again
). But, I don't have any function like this when we are using SQL table (especially using Sqlite).
Can anyone help me regarding this ?
Thanks in advance
With best regards.
Sudip
Re: Changing Table Structure Online
Posted: Wed Dec 23, 2009 1:34 pm
by arroya2
En Español:
Hola Sudip
Te agradeceré me indiques como hacerlo en DBF. Abrí un tema preguntándolo pero nadie me contestó.
Saludos
Rafael Pérez
In English by Google:
Hi Sudip
Thank you show me how to do it in DBF. I opened a topic asking it but nobody answered.
Greetings
Rafael Perez
Re: Changing Table Structure Online
Posted: Wed Dec 23, 2009 5:04 pm
by sudip
Hello Rafael,
I am very much happy to share my code with you all
Here is it :-
(Please remember, I should use new temporary table feature of Harbour in the following code.)
Code: Select all
#include "dbstruct.ch"
#include "minigui.ch"
FUNCTION dbCreaChk(fname, adbf)
local aStruct, option, lChange := .f., lNew := .f., i, newrec, oldrec
fname := upper(fname)
if !file(fname+".dbf")
set exclusive on
dbcreate(fname, adbf)
set exclusive off
return .t.
endif
use (fname)
aStruct = dbstruct()
use
if len(aStruct) != len(adbf)
lChange = .t.
else
i = 1
do while i <= len(aStruct) .and. !lNew .and. !lChange
if len(aStruct[i, DBS_NAME]) != len(adbf[i, DBS_NAME]) ;
.or. upper(aStruct[i, DBS_NAME]) != upper(adbf[i, DBS_NAME]) ;
.or. upper(aStruct[i, DBS_TYPE]) != upper(adbf[i, DBS_TYPE]) ;
.or. aStruct[i, DBS_LEN] != adbf[i, DBS_LEN] ;
.or. aStruct[i, DBS_DEC] != adbf[i, DBS_DEC]
lChange = .t.
endif
i++
enddo
endif
if lChange
if msgyesno(fname+" structure has been changed. Change";
+" the structure ?")
set exclusive on
use (fname)
pack
oldrec = reccount()
use
deletefile("settemp.dbf")
if renamefile(fname+".dbf", "settemp.dbf") != 0
msginfo("Cannot change file structure!",)
QUIT
ENDIF
dbcreate(fname, adbf)
use (fname)
append from settemp
newrec = reccount()
use
if newrec != oldrec
msginfo("Problem in creating file :"+fname+;
". You can get all records in the file SETTEMP.DBF")
quit
endif
deletefile("settemp.dbf")
set exclusive off
return .t.
else
msginfo(fname+" structure mismatch")
quit
endif
endif
return .f.
function NetSelect(cTable)
if select(cTable) = 0
use &cTable shared new
endif
select (cTable)
return nil
I shall be very happy if above code will be helpful
With best regards.
Sudip
Re: Changing Table Structure Online
Posted: Fri Dec 25, 2009 12:55 pm
by arroya2
En Español:
Hola Sudip.
Muchas gracias por compartir tu código.
Lo voy a poner en práctica enseguida.
Muy agradecido
Rafael Pérez
In English by Google:
Hi Sudip.
Thank you very much for sharing your code.
I'm going to put into practice right away.
Very been thankful
Rafael Perez
Re: Changing Table Structure Online
Posted: Fri Jan 01, 2010 9:15 pm
by sudip
Hi All,
I just created very simplified code to change Sqlite table structure online (from hints of Rathinagiri) using codes from Rathi and Grigori Filatov
Code: Select all
//Usage: AddField(mDb, "sale", "test", "text default 'testing'")
Function AddField(mDb, mTblNm, mFldNm, mFldDesc)
local lFound := (ascan(SQLITE_TABLES(mDb, mTblnm), {|x| upper(x[1]) == upper(mFldnm)}) > 0)
if !lFound
miscsql(mDb, "ALTER TABLE "+mTblNm+" ADD COLUMN "+mFldNm+" "+mFldDesc)
endif
return nil
// Following code is written by Grigory Filatov (Minigui\Samples\Advanced\Sqlite_2\Demo.prg)
*--------------------------------
FUNCTION SQLITE_COLUMNS( db, cTable )
*---------------------------------------------------------------------------
* Returns an 2-dimensional array with field names and types
*---------------------------------------------------------------------------
LOCAL aCType := { "SQLITE_INTEGER", "SQLITE_FLOAT", "SQLITE_TEXT", "SQLITE_BLOB", "SQLITE_NULL" }
LOCAL aFields := {}, cStatement := "SELECT * FROM " + cTable
LOCAL stmt, nCCount, nI, nCType
stmt := sqlite3_prepare( db, cStatement )
sqlite3_step( stmt )
nCCount := sqlite3_column_count( stmt )
IF nCCount > 0
FOR nI := 1 TO nCCount
nCType := sqlite3_column_type( stmt, nI )
AADD( aFields, { sqlite3_column_name( stmt, nI ), aCType[ nCType ] } )
NEXT nI
ENDIF
// taken from HMG300\SAMPLES\SQLITE\SQL1.PRG
// as per I remember, written by Rathinagiri
function miscsql(dbo1,qstr)
if empty(dbo1)
msgstop("Database Connection Error!")
return .f.
endif
sqlite3_exec(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
return .f.
endif
return .t.
Thanks a lot to Rathi and Grigory
With best regards.
Sudip
Re: Changing SQLite Table Structure Online
Posted: Sat Jan 02, 2010 2:27 am
by Rathinagiri
Thanks a lot Sudip.
Re: Changing SQLite Table Structure Online
Posted: Mon Jan 04, 2010 11:15 am
by sudip
Hello Friends,
My previous code has serious mistakes
Corrected code will be:
Code: Select all
//Usage: AddField(mDb, "sale", "test", "text default 'testing'")
Function AddField(mDb, mTblNm, mFldNm, mFldDesc)
local lFound := (ascan(SQLITE_COLUMNS(mDb, mTblnm), {|x| upper(x[1]) == upper(mFldnm)}) > 0)
if !lFound
miscsql(mDb, "ALTER TABLE "+mTblNm+" ADD COLUMN "+mFldNm+" "+mFldDesc)
endif
return nil
One thing more, if you are using SQLite Browser from
http://sqlitebrowser.sourceforge.net, today it deleted one table during adding one column
With best regards.
Sudip
Re: Changing SQLite Table Structure Online
Posted: Mon Jan 04, 2010 4:20 pm
by sudip
Now, I downloaded the latest version of SQLiteBrowser. They solved the problem
With best regards.
Sudip