B4J Tutorial SQL Tutorial

Discussion in 'B4J Tutorials' started by Erel, Dec 1, 2013.

  1. LucaMs

    LucaMs Expert Licensed User

    Ah, ok, thank you.
     
  2. Softflow Systems

    Softflow Systems Member Licensed User

    I have started using Basic4Android just a couple of days back and want to use Microsoft SQl database without Web Service but I am having lots of trouble using all the solutions provided including MSSQL library.


    While compiling the following error comes.
    I am using Ver 3.0

    Parsing code. Error
    Error parsing program.
    Error description: Attribute not supported: additionaljar
    Occurred on line: 8
    #AdditionalJar: sqljdbc.jar

    1. AddditionalJar attribute is not supported and also can you give some example for the use with Microsoft Sql Server.
    2. Where is JSQL Library as mentioned in the post.

    I have also posted my problem in MSSQL forum but there is no reply as yet.

    Your early response will be highly appreciated.
     
  3. derez

    derez Expert Licensed User

    You are mixing B4A and B4J. Libraries that start with J are B4J libraries. #AdditionalJar also belongs to B4J.
     
  4. Gabino A. de la Gala

    Gabino A. de la Gala Active Member Licensed User

    Finally I've got it!!!.

    I can connect with Firebird like this:

    Code:
    #Region  Project Attributes

        
    #MainFormWidth: 600
        
    #MainFormHeight: 400
        
    #AdditionalJar: jaybird-full-2.2.4.jar
    #End Region

    Sub Process_Globals
        
    Private fx As JFX
        
    Private MainForm As Form
        
    Private sql1 As SQL
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        MainForm = Form1
        
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
        MainForm.Show
        sql1.Initialize(
    "org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:localhost/3050:C:\Datos\BaseDatos.Fdb?user=USER&password=xyz")
    End Sub
     
    Mashiane, JakeBullet70 and Erel like this.
  5. Gabino A. de la Gala

    Gabino A. de la Gala Active Member Licensed User

    SQL Server connection:
    Code:
    #Region  Project Attributes
        
    #MainFormWidth: 600
        
    #MainFormHeight: 400
        
    #AdditionalJar: jtds-1.3.1.jar
    #End Region

    Sub Process_Globals
        
    Private sql1 As SQL
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        
    Try
        sql1.Initialize(
    "net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://localhost:1433/NombreBD;user=xxx;password=yyy")
        
    Catch
            
    Log("Error de conexión con la BD "
        
    End Try

    End Sub
     
    hibrid0, Mashiane, XbNnX_507 and 2 others like this.
  6. Rafal Ciesielski

    Rafal Ciesielski New Member Licensed User

    Connection to Oracle SQL :

    Code:
    #Region  Project Attributes
        
    #MainFormWidth: 600
        
    #MainFormHeight: 400
        
    #AdditionalJar: ojdbc6.jar
    #End Region

    Sub Process_Globals
        
    Private fx As JFX
        
    Private MainForm As Form
        
    Private sql1 As SQL
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        
    Dim rs As ResultSet
        
    Try
            sql1.Initialize(
    "oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:user/pass@localhost:1521:ERP")
            rs = sql1.ExecQuery(
    "select * from dect")
            
    Do While rs.NextRow
                
    Log(rs.GetString("id"))
            
    Loop
            rs.Close
        
    Catch
            
    Log("Error")
        
    End Try       
    End Sub
     
    Ejbh and Erel like this.
  7. viktor wahyu

    viktor wahyu New Member

    can i connect to ms.SQL 2000/2005/2008 server in my local network with this lib?
    many many thanks
     
    Ejbh likes this.
  8. Erel

    Erel Administrator Staff Member Licensed User

    Yes. You can connect to any database that has a JDBC driver.
     
  9. Sytek

    Sytek Active Member Licensed User

    Hello
    I prefer RDC 'cause what if "someone" decompiles the jar file, all the info is there (User, Password) or I'm Wrong?
    Best Regards!
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    This tutorial is about SQL library which accesses local SQLite databases, not remote databases.
     
  11. AscySoft

    AscySoft Active Member Licensed User

    How about connecting to Access mdb (jet) files? I see there is a UCanAccess http://ucanaccess.sourceforge.net/site.html which is based on Jackcess(which is included as a library in B4J). Does anyone know the proper implementation/connection? Is there possible only to use Jackcess.
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    UCanAccess should work. Make sure to add all the required jars with multiple #AdditionalJar declarations.

    The two values required are explained here: http://ucanaccess.sourceforge.net/site.html

    You can use Jackcess but not with jSQL library as Jackcess by itself doesn't implement a Jdbc driver.
     
    AscySoft likes this.
  13. AscySoft

    AscySoft Active Member Licensed User

    Microsoft Access local connection like this
    Download Ucanaccess zip, put only ucanaccess.jar in b4j additional libs folder, and create (for simplicity) another folder in there, in my case 'lib'). There put all the 4 additional jar files (there are in ucanaccess.zip) and code like this:
    Code:
    #Region  Project Attributes
        
    #MainFormWidth: 600
        
    #MainFormHeight: 400
        
    #AdditionalJar: ucanaccess-2.0.5.jar
        
    #AdditionalJar: lib\jackcess-2.0.4.jar
        
    #AdditionalJar: lib\hsqldb.jar
        
    #AdditionalJar: lib\commons-logging-1.1.1.jar
        
    #AdditionalJar: lib\commons-lang-2.6.jar
    #End Region

    Sub Process_Globals
        
    Private fx As JFX
        
    Private MainForm As Form
        
    Private SQL1 As SQL
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        MainForm = Form1
        
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
        MainForm.Show
        
    Dim rs As ResultSet
        
    Try
          SQL1.Initialize (
    "net.ucanaccess.jdbc.UcanaccessDriver","jdbc:ucanaccess://E:/DATABASES/.....path to your file/Note.mdb")
            rs = SQL1.ExecQuery(
    "SELECT * FROM XUSERS;")
            
    Do While rs.NextRow
                
    Log(rs.GetString("NOMUTIL"))
            
    Loop
            rs.Close
        
    Catch
            
    Log(LastException)
        
    End Try     
        
    ExitApplication
    End Sub
    Erel, do you think I could use this in a b4J server application (I need to query a mdb file over the internet from a web browser)?
     
    magoandroid, Erel and rboeck like this.
  14. Erel

    Erel Administrator Staff Member Licensed User

    Thank you for sharing this. Note that #AdditionalJar attribute was not tested with sub-folders.

    There is nothing special in a server app. It will work. You should use a single threaded handler as it is not clear whether this driver supports multithreaded solutions.
     
    AscySoft likes this.
  15. CanelaKid

    CanelaKid Member Licensed User

    In B4J how do we know the size of the resultset (short of iterating through) e.g. equivalent of cursor.rowcount
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    The information is not available (it is not available to the JDBC driver). If it is important for you then you can first make a count query and then make the query itself.
     
  17. alan1968

    alan1968 Active Member Licensed User

    Hi all !

    Why not works ms sql 2005 ? log: Error de conexión à la BD (Exception) Not initialized

    Code:
    #Region  Project Attributes
        
    #MainFormWidth: 600
        
    #MainFormHeight: 400
        
    #AdditionalJar: jtds-1.3.1.jar


    #End Region

    Sub Process_Globals
        
    Private Fx As JFX
        
    Private MainForm As Form
        
    Private Sql1 As SQL
        
    Private Url As String
        
    Private Driver As String
        
    Private UserName As String
        
    Private PassWord As String
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
        MainForm = Form1
        MainForm.SetFormStyle(
    "UNIFIED")
        MainForm.RootPane.LoadLayout(
    "test"'Load the layout file.
        MainForm.Show
        
    Try
        Driver = 
    "net.sourceforge.jtds.jdbc.Driver"
        Url = 
    "jdbc:jtds:sqlserver://serv01:1433/sqlexpress"
        UserName = 
    "FOR_USER"
        PassWord= 
    "FOR_PSW"
        Sql1.Initialize(Driver,Url & 
    ";user=" & UserName & ";password=" & PassWord)
        
    Catch
        
    Dim e As Exception
            
    Log("Error de conexión à la BD " & e)
        
    End Try
    End Sub
    Sub Button1_MouseClicked (EventData As MouseEvent)
    Dim rs As ResultSet
    Try
    rs = Sql1.ExecQuery(
    "select * from person")
            
    Do While rs.NextRow
                
    Log(rs.GetString("id"))
            
    Loop
            rs.Close
        
    Catch
            
    Log("Error")
        
    End Try      

       
    End Sub
     
    Ejbh likes this.
  18. Erel

    Erel Administrator Staff Member Licensed User

    Change e to LastException:
    Log("Error: " & LastException)
     
  19. alan1968

    alan1968 Active Member Licensed User

    works !

    remplace: Url = "jdbc:jtds:sqlserver://serv01:1433/sqlexpress"
    by : Url = "jdbc:jtds:sqlserver://serv01:1433;instance=sqlexpress"'

    ;-)
     
    Erel likes this.
  20. rudolf

    rudolf Member

    need help,
    i need to build simple application insert, update and delete connected with mysql database,
    the data showed in tableview,
    the problem is :
    1. i dont know how to build mysql database connected in b4j, What I need to install xampp or uniserver?
    2. Can someone give me the source code to my learned
    sorry i am beginner,
     
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