how to create a database ?

Discussion in 'Questions (Windows Mobile)' started by gjoisa, Feb 13, 2008.

  1. gjoisa

    gjoisa Active Member Licensed User

    Hi I am writing an astrology program for which i need a database containing latitude longitude informations . Required fields are :
    1. Name of the Place eg:Mumbai
    2. Latitude (Degrees , minutes , North or South)eg:18:58 N
    3. Longitude (degrees , Minutes , West or East)eg:72:50 E
    4. Timezone (Hours and minutes)eg:+5:30
    How to create it ?
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    For small - medium databases I recommend you to use the Table control.
    You can create a CSV (comma separated file) with any text editor and then load it to the table with Table.LoadCSV.

    For large databases you should use the SQL library.
     
  3. BjornF

    BjornF Active Member Licensed User

    Hi Gjoisa,

    the easiest way to do it is to use a comma delimited file (for small databases).

    Put a table-control on the form you will be using and then create the table by adding columns to it e.g.:
    table1.AddCol (cString, "PlaceName", 0) and so on for each column you want to add (the syntax is under "addcol", under "table" in the helpfile)

    Once you have created the columns in the table you can fill it with data (see "cell" under "table" in the helpfile)

    Finally you save it with e.g.
    table1.SaveCSV, e.g. Table1.SaveCSV ("Astrology.csv", ",", True)
    once again the syntax is in the helpfile. The resulting file can be read by any text editor e.g. notepad.

    I assure you, it is very straightforward - to my mind that is what makes it so nice to work with Basic4ppc :)

    Good luck ! / Björn

    Edit: As always Erel succeeds in answering first - but this time your comment is shorter than mine ! :)
     
  4. Stellaferox

    Stellaferox Active Member Licensed User

    Just a question that pops into mind: is there a way to create a CSV-database, using the FileWrite method? I know I can write a comma after each entry but in the textmode each entry takes up a line. I want to see the data after one another in textmode (as if opened by a texteditor). Is that possible?
     
  5. BjornF

    BjornF Active Member Licensed User

    Hi Stellarefox,

    I think so, these lines write 10 lines in three columns, separated by a comma. Is that what you were asking for?

    FileOpen(c1,AppPath&"\test.csv",cWrite)
    For i=0 To 10
    FileWrite(c1,"This is record "&i&" col 1, This is record "&i&" col 2, This is record "&i&" col 3")
    Next
    FileClose(c1)

    all the best / Björn
     
  6. gjoisa

    gjoisa Active Member Licensed User

    can you tell me with example to create database with SQL ?
     
  7. BjornF

    BjornF Active Member Licensed User

    Dear Gjoisa,

    there is actually quite a good example in the helpfile, but here is a something I have used myself:

    con.New1
    cmd.New1("",con.Value)
    con.Open("Data Source = " & DataF)

    TableFields="Cat text, Title text, Note text, Deadline text"
    Txt="create table if not exists MainTable (" & TableFields & ")"
    cmd.CommandText=Txt
    cmd.ExecuteNonQuery
    cmd.CommandText="create table if not exists CatTable (Dummy text, Vis integer, Cat text)"
    cmd.ExecuteNonQuery

    It tries to open DataF. If the file does not exist or the database doesn't exist then it creates a database with two tables (MainTable and CatTable), the first with four fields and the second with three fields (the "text" is put in there to give the type of field). The database is opened with filename DataF.

    But may I suggest that you have a look at the helpfile - it is really quite helpful ;)

    all the best / Björn
     
  8. Stellaferox

    Stellaferox Active Member Licensed User

    Hi Bjorn,

    That is the problem, the FileWrite only takes one parameter at the time and moves on to the next line with a CRLF.
    Marc
     
  9. BjornF

    BjornF Active Member Licensed User

    Hi Marc,

    but if you write it all together (e.g. "this is col1, this is col2, this is col3") then you will end up with three columns if you open this as a database.

    If this isn't what you were asking about then I'm afraid I didn't understand the question, could you explain further?

    all the best / Björn
     
  10. Stellaferox

    Stellaferox Active Member Licensed User

    Yes you're right there, but I want to read the file using the SplitString feature on the comma's and it won't look "into" the string if there are any comma's. I want to separate the entries with comma's in one long line actually, just as CSV files.
    marc
     
  11. BjornF

    BjornF Active Member Licensed User

    Hmm, that is more difficult. I think that is beyond the level of my competence, sorry :(

    Björn
     
  12. Stellaferox

    Stellaferox Active Member Licensed User

    No problem, you've been helpful.
    Anyone else?
     
  13. agraham

    agraham Expert Licensed User

    Yes, but as the answer is simple I may not have understood your problem :)

    text = "values1" & "," & "value2" & "," & .....
    FileWrite(c1, text)
     
  14. Stellaferox

    Stellaferox Active Member Licensed User

    Agraham,

    That might work well, but I need roughly 133 million entries (with equivalent comma's) in a single textstring then. Will that work?
     
  15. agraham

    agraham Expert Licensed User

    Probably not! But it begs the question - Why do you need to save that much data to a csv file as a single line?
     
  16. Stellaferox

    Stellaferox Active Member Licensed User

    Well, I dont NEED to write it as a single line. I need to write 133784560 entries in a CSV, comma separated file. Then I want to read it in an array using the splStringfeature using the commaseparator.
    Later on when having checked the data I will use a binary file (I need to sort the data first).
    Marc
     
  17. agraham

    agraham Expert Licensed User

    Why do you need to write it to a CSV file and then reread it all back in again? I assume you are generating the data within the program. Unless it takes a lot of processing to generate each data point it might well be quicker to just generate the data straight into the array rather than doing a whole lot of string processing on it.

    Also that is a huge amout of data that will occupy a huge amount of memory. If it were Int32s it is nearly half a Gigabyte, as Strings it could be even more! Unless your PC has a lot of memory your app will cause Windows to page stuff to and from disk and slow down processing to a crawl. Are you sure that you have structured your app and its' data in the optimum way.
     
  18. Stellaferox

    Stellaferox Active Member Licensed User

    Agraham,

    You are right. I am generating the code from inside and then building a "mothertable". I have to eyeball the data, hence the CSV format.
    Later I want to read in the data in an array and after some work, split up the mothertable in a few smaller ones with indexes. Then I can use BinaryFile options. That is the main idea.
    I need to optimize my code (lot easier in the earlier days with assembly languages, but Basic4PPC doesn support this (yet)).
    Because computing from code takes a lot longer then reading a table (using binary search) I am able to have any entry I want in a 133 million base within 27 steps maximum.
    Marc
     
  19. agraham

    agraham Expert Licensed User

    You can't possibly eyeball all 133Mitems. Why not just generate the data into an array then put some testing code in to write out selected chunks of it to MsgBoxes or to small text files, they don't have to be CSV files to be readable.
    Sorry - you missed my point. I meant generate all the items into the array rather than read all those items from a file and then do your lookups and build your smaller tables. Although for 27 steps involving 27 equality checks to save any time it must be a relatively long algorithm to generate the data? Also that implies sorting the table. Unless you can generate the data in sorted form that could take a long time as well. Have you done any timings to estimate how long this mammoth thing will take to run?
    It probably never will as B4PPC outputs C# code which is compiled to IL (intermediate language) and written to the exe which the .NET Framework CLR (Common Language Runtime) JITs (Just In Time compiles) it at runtime to "safe" machine code. All this is done to stop humans fiddling with Assembly code and making mistakes. There are ways to cross over into "unsafe" code in DLLs but for B4PPC that would need a library and I'll go out on a limb and bet that Erel will never support this natively in B4PPC. If you are enough of an anorak (like me) you can buy books to find out how IL and the CLR works.
     
  20. Erel

    Erel Administrator Staff Member Licensed User

Loading...