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