Android Tutorial SQL tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 2, 2010.

Thread Status:
Not open for further replies.
  1. ChrShe

    ChrShe Member Licensed User

    I'm sorely unfamiliar with SQL Lite. Can we use an .mdf database file created in SQLExpress?

    Thanks much!
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Not directly. You will need to first convert it to SQLite. SQLite uses a different file structure.
     
  3. sioconcept

    sioconcept Active Member Licensed User

    Hi,
    I have a sqlite database encrypted, I would leave it as is (without decrypting them into a new file). The goal would be to decrypt the database and use it directly from memory for SQL queries. This may be read-only.
    Is it possible to use the library through an object or variable without a file? My problem is that the database must not be copied if it is clear and I supose that hacking the tablet / phone that database can be copied ...
    An idea?
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    You can use SQLCipher to work with an encrypted database.
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    The tutorial was updated with information about asynchronous queries and asynchronous batch inserts.
     
  6. Phil

    Phil Member Licensed User

    SQLite and MS Access Date conversion

    I had a hard time finding relevant information about converting date fields to and from Access and SQLite format. I have finally come up with a couple of solutions. Not much but I thought some of you might find them useful.

    ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
    This sqlite query will convert all "YourDate" field data in an SQLite database from Access short date format (MM/DD/YYYY) to
    SQLite format (YYYY-MM-DD) Make sure you backup the database first, just in case the results are not what you intended.

    Query:
    "UPDATE Table SET YourDate = substr(YourDate, 7) || "-" || substr(YourDate,1,2) || "-" || substr(YourDate, 4,2);"

    ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’


    This B4A subroutine will Convert a date string from Access short date format to SQLite date format and vice-versa

    Sub ConvertDateStr (DatetoConvert As String) As String

    Dim DateParsed(), OutputStr As String
    If DatetoConvert.CharAt(2) = "/" Then 'it's in Access short date format
    'convert date to SQLite format (YYYY-MM-DD) from Access short date format (MM/DD/YYYY)
    DateParsed = Regex.Split("/", DatetoConvert)
    OutputStr=DateParsed(2) & "-" & DateParsed(0) & "-" & DateParsed(1)
    'date is now in YYYY-MM-DD format
    Else If DatetoConvert.CharAt(4) = "-" Then 'it's in SQLite date format
    'convert date from SQLite format (YYYY-MM-DD) to Access short date (MM/DD/YYYY)
    DateParsed = Regex.Split("-", DatetoConvert)
    OutputStr=DateParsed(1) & "/" & DateParsed(2) & "/" & DateParsed(0)
    'date is now in MM/DD/YYYY format
    Else
    Msgbox("Cannot convert date.", "Format not Recognized")
    Return
    End If

    Return OutputStr

    End Sub

    ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
    Usage:
    'to convert an Access short date to SQLite format
    Dim SqlDate As String
    SqlDate = ConvertDateStr(AccessDate)
    ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
     
  7. Phil

    Phil Member Licensed User

    Formatting Datedialog output

    :signOops::sign0104:
    Note:

    If you use the DateDialog to set a date then try to use this sub to convert it you will need to format the Datedialog output first.

    sDate= NumberFormat(Dd.Month,2,0) & "/" & NumberFormat(Dd.DayOfMonth,2,0) & "/" & Dd.Year

    That reformats the string so that month and day are 2 digits. ie 01,02, etc

    Example:

    Dim sDate As String
    Dim Dd As DateDialog
    Dd.Year = DateTime.GetYear(DateTime.Now)
    Dd.Month = DateTime.GetMonth(DateTime.Now)
    Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
    ret = Dd.Show("Set date", "Choose Date", "OK", "Cancel", "", Null )

    If ret = DialogResponse.CANCEL Then
    Return
    Else
    sDate= NumberFormat(Dd.Month,2,0) & "/" & NumberFormat(Dd.DayOfMonth,2,0) & "/" & Dd.Year
    end if
     
  8. darso

    darso New Member Licensed User

    Does Anybody know where to get SQL - v1.20 library (jar file)

    thanks
     
  9. klaus

    klaus Expert Licensed User

    The SQL library is shiped with Basic4Android it is in the Library folder of Basic4Android.

    Best regards.
     
  10. darso

    darso New Member Licensed User

    My shiped SQL Library is version 1.02 (and B4A ver 2.25 ). ExecQueryAsync or ExecNonQueryBatch cannot be used on that version.
     
  11. klaus

    klaus Expert Licensed User

    You should either upgrade to B4A version 2.52 or ask Erel.

    Best regards.
     
  12. Carcas

    Carcas Member Licensed User

    Hi :)

    I have this problem:

    example SQL.zip runs properly but my app dont runs ......


    Sub Process_Globals
    Dim SQL1 As SQL
    End Sub
    Sub Globals
    End Sub

    Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
    SQL1.Initialize(File.DirInternal, "Prova1.db", True)
    End If
    CreateTables
    End Sub
    Sub CreateTables
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS Posizione")

    SQL1.ExecNonQuery("CREATE TABLE table1 (Pos INTEGER , Orient INTEGER, col3 INTEGER)")

    End Sub

    i have this error......

    Parsing code. 0.00
    Compiling code. 0.05
    Compiling layouts code. 0.00
    Generating R file. 0.13
    Compiling generated Java code. 1.01
    Convert byte code - optimized dex. 0.69
    Packaging files. 0.30
    Copying libraries resources 0.00
    Signing package file (debug key). 1.15
    ZipAlign file. 0.12
    Installing file to device. Error
    pkg: /data/local/tmp/result_DEBUG.apk
    Failure [INSTALL_FAILED_VERSION_DOWNGRADE]

    Restarting ADB Server may solve this problem. 'I done

    Tools - Restart ADB Server. Device serial: emulator-5554
     
  13. Erel

    Erel Administrator Staff Member Licensed User

    Seems like the installed version of your app (or the app with the same package name) has a higher version than your current app. You can change the VersionCode attribute or you can manually uninstall the app from the emulator.
     
  14. aklisiewicz

    aklisiewicz Active Member Licensed User

    Ok, so how would I read several columns?
    I tried


    Cursor1.Position = i
    Cursor1.GetString("col1","Col2","Col3")

    but it doesn't seem to work. I simply want to read one record with a given ID (or CursorPosition) and then display theta on the screen.
    For some reason I'm finding such simple task very tedious and complicated to implement using B4A. While working with desktop Apps
    the steps involved were: 1)reading the record at a given ID (usually Pk) 2)assigning value from the record Column to the screen variable 3)displaying screen variable in (either editable or read only screen field). I wonder how this co-relates in B4A. One thing I noticed is that all examples are (a)-very fragmented - that means it is hard to find (b)-have added unnecessary complexity. I understand that keeping a FileName, Path or other things as variables,arrays, maps etc. brings lots of flexibility, but at the same time it makes en example App more complex to study.
    Here is an example. As a newbie I would like to see simple App which allows to Add/Change/Delete record, but I would like to see each activity done on a separate screen. For example I havea list of records, I select a record -> it opens a Form with record details. On that form I have a 2 buttons UPDATE and DELETE buttons which then either allow to update/delete record.
    This is typical scenario for most of the data grid/forms. Then as more advanced App I would like to see this same App with the form which allows: store/update an image (as path), store/update an image as BLOB, store/update Map Location, store update MEMO() (TEXT) type fields.



    . Then this same App can be brought to the next level to display record details (that would be Level2). Next this same App could have added Search capability (Level3), then perhaps Sorting capability (Level 4), then FormType activity which displays Parent/Child data (like DBUtils example does) (and that would be Level5).....etc.

    This way user can study from the simplest scenario and have more and more features added. Right now many examples (although workable) fall into different context and different development styles, which makes all of us very confused. Those who came from VB might have a little easier, but there are people who are coming from different environments and languages with different concepts and work styles.
    Despite I'm really grateful for all the documentation and example Apps (especially the Docs and tutorials from Klaus) I think it would be very beneficial to create on larger App and show hoe to implement from simple to very advanced features step by step.

    Arthur
     
    Last edited: Jun 12, 2013
  15. Geezer

    Geezer Active Member Licensed User

    Code:
    cursor1.position = I
    val1 = 
    cursor.getstring("col1")
    val2 = 
    cursor.getstring("col2")
    val3 = 
    cursor.getstring("col3")
    or direct to the views

    Code:
    label1.text = cursor.getstring("col1")
    edittext1.text = 
    cursor.getstring("col2")
    and so on
     
  16. aklisiewicz

    aklisiewicz Active Member Licensed User

    Excellent, that's what I was looking for.
    Thanks a lot.

    Now, how would I handle a this ?
    database = SQlite
    I need to display text field with multiple lines (I think it is a counterpart of MEMO() from MySQL) ?

    My 2nd column is TEXT() type so when I try to assign values from database

    Code:
    strDesc = Cur.GetString("col2")


    I get an error:
    src\B4A\UserInterfaceTabHost\issuedetails.java:475: inconvertible types
    found : java.lang.String
    required: android.widget.TextView
    mostCurrent._strdesc.setObject((android.widget.TextView)(mostCurrent._cur.GetString("col2")));


    Art
     
    Last edited: Jun 12, 2013
  17. Erel

    Erel Administrator Staff Member Licensed User

    I guess that strDesc is a Label or EditText.

    You should write:
    Code:
    strDesc.Text = Cur.GetString(..)
     
  18. aklisiewicz

    aklisiewicz Active Member Licensed User

    yes, but when I do this I can compile with no errors, then when I run it get type mismatch Error

    the column in SQLite is TEXT() type, not the CHAR() or VARCHAR()

    Art
     
  19. Erel

    Erel Administrator Staff Member Licensed User

    Can you post the code that raises the error and the full error message from the logs?
     
  20. johnaaronrose

    johnaaronrose Active Member Licensed User

    SQL Query Prepared Statements

    I seem to remember from my Oracle days that it had the facility for SQL Queries to use Prepared Statements (I think that they were called 'using parameterized values'). They had the advantage of preventing SQL Injection. Are they still available with B4A on SQLite databases?
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice