B4J Question B4A code convercion to B4J

Anastasios Michaelides · Jun 25, 2018

Hello

I have an android app in order to calculate the price of a window by inserting
width and height dimensions.
The prices are stored in a SQL database.
Is there a possibility to run this code on a server, and if yes what i have to change?

Thank you in advance.

B4X:

#Region  Project Attributes
    #FullScreen: True
    #IncludeTitle: FALSE
    #ApplicationLabel: Energon me app
    #VersionCode: 1
    #VersionName:
    #SupportedOrientations: PORTRAIT
    #CanInstallToExternalStorage: True
#End Region

#Region  Activity Attributes
    #FullScreen: TRUE
    #IncludeTitle: FALSE
#End Region

Sub Process_Globals
    Dim SQL1 As SQL
End Sub

Sub Globals
    Dim MyFolder As String    = "4000"
    Dim DBFileName As String = "4000.sqlite"
    Dim DBFilePath As String
    Dim Cursor1 As Cursor
    Dim line, txt As String
    Dim FileName As String = "dimensions.txt"  'height is first col, width is first row
    Dim reader As TextReader
    Dim DBTableName As String = "tblspecs"
    Dim i As Int
    Dim edtheight, edtWidth As EditText
    Dim btnGo, btnClear As Button
    Dim lblPrice As Label
 
    Private Aluplast As Button
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("companyselect")

    If File.ExternalWritable Then
        DBFilePath = File.DirRootExternal & "/" & MyFolder
    Else
        DBFilePath = File.DirInternal & "/" & MyFolder
    End If
    File.MakeDir(DBFilePath,"")
 
    If SQL1.IsInitialized =False Then
        SQL1.Initialize(DBFilePath,DBFileName,True)
    End If
 
    CreateTable  'create table if not exist
 
    If Not (File.Exists(DBFilePath,FileName)) Then
        File.Copy(File.DirAssets,FileName,DBFilePath,FileName)     
    End If
 
    ImportCSV   'import data from csv file into table
End Sub

Sub Activity_Resume
End Sub

Sub Activity_Pause (UserClosed As Boolean)
    If UserClosed Then SQL1.Close
End Sub

Sub CreateTable
'    txt="DROP TABLE IF EXISTS " & DBTableName
'    SQL1.ExecNonQuery(txt)
    txt="CREATE TABLE IF NOT EXISTS " & DBTableName & "(HEIGHT INTEGER, WIDTH INTEGER, PRICE REAL , PRIMARY KEY(HEIGHT,WIDTH) )"
    SQL1.ExecNonQuery(txt)  'Create tabl
End Sub

Sub ImportCSV
    Dim j As Int
    Dim MyReRowContentount As Int
    MyReRowContentount=SQL1.ExecQuerySingleResult("SELECT count(*) FROM " & DBTableName )
    If MyReRowContentount=0 Then
        SQL1.BeginTransaction
        reader.Initialize(File.OpenInput(DBFilePath,FileName))     
        line = reader.ReadLine     'first line of text file contains the wdth
        Dim FirstLine() As String                 
        FirstLine = Regex.Split(",", line)
        line = reader.ReadLine
        For i= 1 To FirstLine.Length - 1   'skip first item in first line since blank
            Do While line <> Null
                  Dim RowContent() As String  'RowContent is row content
                  RowContent = Regex.Split(",", line)
                  SQL1.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?)", Array As String(RowContent(0) , FirstLine(i) ,RowContent(i) ))
                  j = j + 1
                  line = reader.ReadLine
            Loop
            reader.Initialize(File.OpenInput(DBFilePath,FileName))
            reader.ReadLine 'skip first line         
            line = reader.ReadLine
        Next
        SQL1.TransactionSuccessful
        SQL1.EndTransaction
        reader.Close
        Msgbox(j & " Records in text file were imported to " & DBTableName, "T E X T  F I L E")
    Else
        ToastMessageShow("Your table already has records.",False)
    End If     

End Sub

Sub btnGo_Click
    'txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT =? AND WIDTH=?"
    'txt="SELECT * FROM " & DBTableName & "WHERE _HEIGHT BETWEEN" & 500 & "AND" & 2400
    txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT= (SELECT HEIGHT FROM " & DBTableName _
    & " WHERE HEIGHT >=? LIMIT 1) AND WIDTH = (SELECT WIDTH FROM " & DBTableName & " WHERE WIDTH >=? LIMIT 1)"
    Cursor1=SQL1.ExecQuery2(txt,Array As String(edtheight.Text,edtWidth.Text))
    If Cursor1.RowCount = 0 Then
        'lblPrice.Text =  Cursor1.GetDouble("PRICE")
        ToastMessageShow("No records found for your crireria. Please check your height and width",False)
        LogColor("No records found for your crireria",Colors.red)
    Else
     
        For i=0 To Cursor1.RowCount-1
            Cursor1.Position=0
'            Log("My result: H W P " & Cursor1.GetInt("HEIGHT") & "   " &  Cursor1.GetInt("WIDTH") &  "   " &  Cursor1.GetDouble("PRICE") )  'displays
           lblPrice.Text =  Cursor1.GetDouble("PRICE")
        Next
    End If
End Sub

Sub btnClear_Click
    edtheight.Text="" : edtWidth.text="" : lblPrice.text=""
End Sub
Sub Aluplast_Click
    Activity.LoadLayout("main")

End Sub

[code/]

Erel · Jun 26, 2018

Tip: never use TextReader / TextWriter. Use File.ReadList instead. If you are loading a CSV file then use StringUtils.LoadCSV.

Anastasios Michaelides said:
Is there a possibility to run this code on a server, and if yes what i have to change?

This is an Android code. It will not run as-is on the server. If you are asking whether you can access the database from a server app then the answer is yes.

If you have any specific question then please post it.

Anastasios Michaelides · Jun 26, 2018

Hello again

I am trying to covert the code to a b4j app and i am getting an error.

This is the code:

B4X:

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim SQL2 As SQL
    Dim MyFolder As String    = "4000"
    Dim DBFileName As String = "4000.sqlite"
    Dim DBFilePath As String
    Dim Cursor1 As Cursor
    Dim line, txt As String
    Dim FileName As String = "dimensions.txt"  'height is first col, width is first row
    Dim reader As TextReader
    Dim DBTableName As String = "tblspecs"
    Dim i As Int
   
    Dim edtheight, edtWidth As TextField
    Dim btnGo, btnClear As Button
    Dim lblPrice As Label
   
End Sub

Sub AppStart (Form1 As Form, Args() As String)
   
   
'   
'    If File.DirApp Then
'        DBFilePath = File.DirTemp & "/" & MyFolder
'    Else
'        DBFilePath = File.DirTemp & "/" & MyFolder
'    End If
    File.MakeDir(DBFilePath,"")
   
    If SQL2.IsInitialized =False Then
        SQL2.InitializeSQLite(DBFilePath,DBFileName,True)
    End If
   
    CreateTable  'create table if not exist
   
    If Not (File.Exists(DBFilePath,FileName)) Then
        File.Copy(File.DirApp,FileName,DBFilePath,FileName)
    End If
    ImportCSV   'import data from csv file into table
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.RootPane.LoadLayout("main")
    MainForm.Show
   
   
End Sub

Sub CreateTable
    txt="DROP TABLE IF EXISTS " & DBTableName
    SQL2.ExecNonQuery(txt)
    txt="CREATE TABLE IF NOT EXISTS " & DBTableName & "(HEIGHT TEXT, WIDTH TEXT, PRICE REAL , PRIMARY KEY(HEIGHT,WIDTH) )"
    'SQL2.ExecNonQuery(txt)  'Create tabl
End Sub

Sub ImportCSV
    Dim j As Int
    Dim MyReRowContentount As Int
    MyReRowContentount=SQL2.ExecQuerySingleResult("SELECT count(*) FROM " & DBTableName )
    If MyReRowContentount=0 Then
        SQL2.BeginTransaction
        reader.Initialize(File.OpenInput(DBFilePath,FileName))
        line = reader.ReadLine     'first line of text file contains the wdth
        Dim FirstLine() As String
        FirstLine = Regex.Split(",", line)
        line = reader.ReadLine
        For i= 1 To FirstLine.Length - 1   'skip first item in first line since blank
            Do While line <> Null
                Dim RowContent() As String  'RowContent is row content
                RowContent = Regex.Split(",", line)
                SQL2.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?)", Array As String(RowContent(0) , FirstLine(i) ,RowContent(i) ))
                j = j + 1
                line = reader.ReadLine
            Loop
            reader.Initialize(File.OpenInput(DBFilePath,FileName))
            reader.ReadLine 'skip first line
            line = reader.ReadLine
        Next
        SQL2.TransactionSuccessful
        SQL2.Close
        reader.Close
        'Msgbox(j & " Records in text file were imported to " & DBTableName, "T E X T  F I L E")
    Else
        'ToastMessageShow("Your table already has records.",False)
    End If

End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub btnGo_Click
    'Dim query As String = "CALL psm.GetAppData(?,?)"
    'Dim obj As Object = SQL2.CreateCallStatement(query, Array As String(edtheight.Text,edtWidth.Text))
    'Cursor1 = SQL2.ExecCall(obj)
   
   
    'txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT =? AND WIDTH=?"
    'txt="SELECT * FROM " & DBTableName & "WHERE _HEIGHT BETWEEN" & 500 & "AND" & 2400
    txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT= (SELECT HEIGHT FROM " & DBTableName _
    & " WHERE HEIGHT >=? LIMIT 1) AND WIDTH = (SELECT WIDTH FROM " & DBTableName & " WHERE WIDTH >=? LIMIT 1)"
    Cursor1=SQL2.ExecQuery2(txt,Array As String(edtheight.Text, edtWidth.Text))
    If Cursor1.RowCount = 0 Then
       
       
        'lblPrice.Text =  Cursor1.GetDouble("PRICE")
        'ToastMessageShow("No records found for your crireria. Please check your height and width",False)
        'LogColor("No records found for your crireria",Colors.red)
    Else
       
        For i=0 To Cursor1.RowCount-1
            Cursor1.Position=0
'            Log("My result: H W P " & Cursor1.GetInt("HEIGHT") & "   " &  Cursor1.GetInt("WIDTH") &  "   " &  Cursor1.GetDouble("PRICE") )  'displays
            lblPrice.Text =  Cursor1.GetDouble("PRICE")
        Next
    End If
End Sub

This is the error:

Compiling generated Java code. Error
B4J line: 111
Cursor1=SQL2.ExecQuery2(txt,Array As String(edthe
javac 1.8.0_65
src\b4j\example\main.java:256: error: package android.database does not exist
_cursor1.setObject((android.database.Cursor)(_sql2.ExecQuery2(_txt,anywheresoftware.b4a.keywords.Common.ArrayToList(new String[]{_edtheight.getText(),_edtwidth.getText()})).getObject()));
^

Anastasios Michaelides · Jun 26, 2018

I made progress but i have an issue.
I am getting prices only from the first column and not from the others

B4X:

#Region Project Attributes 
    #MainFormWidth: 600
    #MainFormHeight: 600 
    #AdditionalJar: mysql-connector-java-5.1.27-bin.jar
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim SQL2 As SQL
    'Dim MyFolder As String    = "4000"
    Dim DBFileName As String = "4000.sqlite"
    Dim DBFilePath As String
    Dim Cursor As ResultSet
    Dim line, txt As String
    Dim FileName As String = "dimensions.txt"  'height is first col, width is first row
    Dim reader As TextReader
    Dim DBTableName As String = "tblspecs"
    Dim i As Int
   
    Dim edtheight, edtWidth As TextField
    Dim btnGO As Button
    Dim lblPrice As Label
   
End Sub

Sub AppStart (Form1 As Form, Args() As String)
   
   
'   
'    If File.DirApp Then
'        DBFilePath = File.DirTemp & "/" & MyFolder
'    Else
'        DBFilePath = File.DirTemp & "/" & MyFolder
'    End If
    'File.MakeDir(DBFilePath,"")
   
    'If SQL2.IsInitialized =False Then
        'SQL2.InitializeSQLite(DBFilePath,DBFileName,True)
   
    'End If
   
    'CreateTable  'create table if not exist
   
   
    MainForm = Form1
    MainForm.RootPane.LoadLayout("main")
    MainForm.Show
   
    SQL2.InitializeSQLite(File.DirAssets, DBFileName, True)
    'SQL2.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test?characterEncoding=utf8")
    If Not (File.Exists(DBFilePath,FileName)) Then
        File.Copy(File.DirAssets,FileName,DBFilePath,FileName)
    End If
    ImportCSV   'import data from csv file into table
   
'    btnGO.Initialize(True)
'    lblPrice.Initialize(True)
'    edtheight.Initialize(True)
'    edtWidth.Initialize(True)
End Sub

Sub CreateTable
    SQL2.InitializeSQLite(File.DirAssets, DBFileName, True)
    txt="DROP TABLE IF EXISTS " & DBTableName
    SQL2.ExecNonQuery(txt)
    txt="CREATE TABLE IF NOT EXISTS " & DBTableName & "(HEIGHT TEXT, WIDTH TEXT, PRICE REAL , PRIMARY KEY(HEIGHT,WIDTH) )"
    'SQL2.ExecNonQuery(txt)  'Create tabl
End Sub

Sub ImportCSV
    Dim j As Int
    Dim MyReRowContentount As Int
    MyReRowContentount=SQL2.ExecQuerySingleResult("SELECT count(*) FROM " & DBTableName )
    If MyReRowContentount=0 Then
        SQL2.BeginTransaction
        reader.Initialize(File.OpenInput(DBFilePath,FileName))
        line = reader.ReadLine     'first line of text file contains the wdth
        Dim FirstLine() As String
        FirstLine = Regex.Split(",", line)
        line = reader.ReadLine
        For i= 1 To FirstLine.Length - 1   'skip first item in first line since blank
            Do While line <> Null
                Dim RowContent() As String  'RowContent is row content
                RowContent = Regex.Split(",", line)
                SQL2.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?)", Array As String(RowContent(0) , FirstLine(i) ,RowContent(i) ))
                j = j + 1
                line = reader.ReadLine
            Loop
            reader.Initialize(File.OpenInput(DBFilePath,FileName))
            reader.ReadLine 'skip first line
            line = reader.ReadLine
        Next
        SQL2.TransactionSuccessful
        SQL2.Close
        reader.Close
        'Msgbox(j & " Records in text file were imported to " & DBTableName, "T E X T  F I L E")
    Else
        'ToastMessageShow("Your table already has records.",False)
    End If

End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub btnGo_Click
    'Dim query As String = "CALL psm.GetAppData(?,?)"
    'Dim obj As Object = SQL2.CreateCallStatement(query, Array As String(edtheight.Text,edtWidth.Text))
    'Cursor = SQL2.ExecCall(obj)
   
   
    'txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT =? AND WIDTH=?"
    'txt="SELECT * FROM " & DBTableName & "WHERE _HEIGHT BETWEEN" & 500 & "AND" & 2400
'    txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT= (SELECT HEIGHT FROM " & DBTableName _
'    & " WHERE HEIGHT >=? LIMIT 1) AND WIDTH = (SELECT WIDTH FROM " & DBTableName & " WHERE WIDTH >=? LIMIT 1)"
'    Cursor=SQL2.ExecQuery2(txt,Array As String(edtheight.Text, edtWidth.Text))
'    If Cursor.NextRow  Then
''       
'       
'        lblPrice.Text =  Cursor.GetDouble("PRICE")
'        'ToastMessageShow("No records found for your crireria. Please check your height and width",False)
'        'LogColor("No records found for your crireria",Colors.red)
'    Else
''       
'        For i=0 To Cursor.ColumnCount
'            Cursor.NextRow
'            Log("My result: H W P " & Cursor.GetInt("HEIGHT") & "   " &  Cursor.GetInt("WIDTH") &  "   " &  Cursor.GetDouble("PRICE") )  'displays
'            lblPrice.Text =  Cursor.GetDouble("PRICE")
'        Next
'    End If



    txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT= (SELECT HEIGHT FROM " & DBTableName _
    & " WHERE HEIGHT >=? LIMIT 1) AND WIDTH = (SELECT WIDTH FROM " & DBTableName & " WHERE WIDTH >=? LIMIT 1)"
    Cursor=SQL2.ExecQuery2(txt,Array As String(edtheight.Text,edtWidth.Text))
    'If Cursor.RowCount = 0 Then
        lblPrice.Text =  Cursor.GetDouble("PRICE")
        'ToastMessageShow("No records found for your crireria. Please check your height and width",False)
        'LogColor("No records found for your crireria",Colors.red)
    'Else
       
        'For i=0 To Cursor.RowCount-1
            'Cursor.Position=0
'            Log("My result: H W P " & Cursor1.GetInt("HEIGHT") & "   " &  Cursor1.GetInt("WIDTH") &  "   " &  Cursor1.GetDouble("PRICE") )  'displays
            lblPrice.Text =  Cursor.GetDouble("PRICE")
        'Next
    'End If
End Sub

Erel · Jun 27, 2018

Why is Cursor a global variable? It is a mistake.

Example of using SQL.ExecQuery: https://www.b4x.com/b4j/help/jsql.html#sql_execquery

B4J Question B4A code convercion to B4J

Anastasios Michaelides

Member

Attachments

Erel

B4X founder

Anastasios Michaelides

Member

Anastasios Michaelides

Member

Erel

B4X founder

Similar Threads

B4J Question B4A code convercion to B4J

Anastasios Michaelides

Member

Attachments

Erel

B4X founder

Anastasios Michaelides

Member

Anastasios Michaelides

Member

Erel

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency