#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
