Android Question jRDC2 with MSACCESS FILE: CONFIG FILE TO TEST THAT I HAVE A CONNECTION

Ryan Pigeon

Ryan Pigeon

Member
HI all you busy bees.

Can someone help? I am trying the jRDC2 example using a msaccess file on my PC. How do I configure this file:

#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
DriverClass=net.ucanaccess.jdbc.UcanaccessDriver

JdbcUrl=jdbc:ucanaccess://c:/users/bruno/downloads/test.mdb?characterEncoding=utf8

User=
Password=
#Java server port
ServerPort=17178

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#example of postegres configuration:
#JdbcUrl=jdbc:postgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
id INTEGER PRIMARY KEY AUTO_INCREMENT,\
name CHAR(30) NOT NULL,\
image BLOB)
sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
sql.select_animal=SELECT name, image, id FROM animals WHERE id = ?;
sql.create_table=CREATE TABLE article (col1 numeric(10,4) ,col2 text);
sql.select=select * from article
sql.insert=INSERT INTO article VALUES(?, ?)
 
Sort by date Sort by votes
B

BigBoss123

Member
Licensed User
Longtime User
Here is my Config file for using MS Access using a ACCDB database but will work on a MDB files as well.

Config file: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION


#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true
User=
Password=
ServerPort=17178


#example for MSAccess configuration (With password protection )
#DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
#JdbcUrl=jdbc:net.ucanaccess://C:/waistorage/storage1.accdb;memory=true
#JdbcUrl= "jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true;showSchema=true;openExclusive=true;immediatelyReleaseResources=true) 'change path, user as password as needed
#User= admin
#Password= waistorage123
#ServerPort=17178


#LOAD DATABASES
#Load Customers
sql.startcustomers = SELECT Index,  Name FROM customer WHERE active= 1  ORDER by  Name

#Load Categories
sql.startcategories_select = SELECT category, description FROM category WHERE category < 50 ORDER  BY category ASC
sql.category_select = SELECT category, bin FROM category WHERE description = ?
sql.categories_select = SELECT category, case, description, value, Bin FROM category WHERE category < 50 ORDER BY category ASC
sql.selectbincode = SELECT * FROM LastRow

#Load Destinations
sql.startdestination_select = SELECT loc_code, description FROM destination ORDER BY description ASC
sql.destination_select = SELECT * FROM destination ORDER BY description ASC

#Load vintage
sql.startvintage_select = SELECT year, description FROM vintage ORDER BY year DESC
sql.vintage_select = SELECT * FROM vintage ORDER BY year DESC

#Load winetype
sql.startwinetype_select = SELECT index, description FROM winetype ORDER BY description ASC
sql.winetype_select = SELECT * FROM winetype ORDER BY description ASC

# PRODUCTS
sql.productsselect = SELECT * FROM products WHERE barcode= ?
sql.productsupdate = UPDATE products  SET wareNo=? , wareRow = ?, wareLoc = ? WHERE  barcode=?
sql.outgoingproduct = UPDATE products SET currentbal = ? WHERE barcode = ?
sql.getproductscust = SELECT * FROM products WHERE custname =? and currentbal > 0 ORDER BY barcode
sql.zeroproduct = UPDATE products SET  ZeroDate=?, active=? WHERE barcode=?

#CUSTOMERS
sql.insert_customers = INSERT INTO customer VALUES (null, ?,?)
sql.select_customers = SELECT  * FROM customer WHERE active= 1  ORDER by Name
sql.get_custdetails = SELECT Index from customer WHERE Name= ?

#TRANSACTIONS
sql.create_product1 = UPDATE products SET barcode =?, wareNo =?, wareRow =?, wareLoc =?, custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,   bincode=?, Mixed=?, ExcisePaid=? WHERE barcode =?
sql.create_product11 = UPDATE products SET   barcode =?,  wareNo =?, wareRow =?, wareLoc =?,  custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,  Mixed=?, ExcisePaid=?  WHERE barcode =?
sql.get_transact = SELECT dbdate, document1, dbquantity, dbvalue  FROM transact WHERE barcode= ? ORDER by dbdate
sql.newTrans =INSERT INTO  transact (barcode, dbdate, custindex, custname, category, dbquantity, dbvalue)  VALUES( ?,?,?,?,?,?,?)
sql.outTrans =INSERT INTO  transact (barcode, dbdate, document1, custindex, custname, category, dbquantity, dbvalue, label, excise, adjustment)  VALUES( ?,?,?,?,?,?,?,?,?,?,?)
sql.extbins = UPDATE ExternalBincontrol SET currentbal=? AND active=? WHERE barcode=?
sql.othertrans = INSERT INTO othertransact (barcode, dbdate, CategoryDesc, custindex, custname, category, dbquantity) VALUES(?,?,?,?,?,?,?)

#BARCODETRANSACTION
sql.create_barcodetrans = INSERT INTO BarcodeTrans (barcode, custindex, custname, date_start, date_end,  category) VALUES(?,?,?,?,?,?)
sql.getbarcodetrans = SELECT * FROM BarcodeTrans WHERE barcode = ?
sql.update_barcodetrans = UPDATE BarcodeTrans SET date_end =? WHERE barcode = ?

#BINCONTROL
sql.bincontrol_select = SELECT * FROM Bincontrol WHERE barcode = ?
sql.update_bincontrol = UPDATE Bincontrol set currentbal = ?, active = ?, Rental = ? WHERE barcode = ?

I hope this helps

David
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
BigBoss123 said:
Here is my Config file for using MS Access using a ACCDB database but will work on a MDB files as well.

Config file: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION


#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true
User=
Password=
ServerPort=17178


#example for MSAccess configuration (With password protection )
#DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
#JdbcUrl=jdbc:net.ucanaccess://C:/waistorage/storage1.accdb;memory=true
#JdbcUrl= "jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true;showSchema=true;openExclusive=true;immediatelyReleaseResources=true) 'change path, user as password as needed
#User= admin
#Password= waistorage123
#ServerPort=17178


#LOAD DATABASES
#Load Customers
sql.startcustomers = SELECT Index,  Name FROM customer WHERE active= 1  ORDER by  Name

#Load Categories
sql.startcategories_select = SELECT category, description FROM category WHERE category < 50 ORDER  BY category ASC
sql.category_select = SELECT category, bin FROM category WHERE description = ?
sql.categories_select = SELECT category, case, description, value, Bin FROM category WHERE category < 50 ORDER BY category ASC
sql.selectbincode = SELECT * FROM LastRow

#Load Destinations
sql.startdestination_select = SELECT loc_code, description FROM destination ORDER BY description ASC
sql.destination_select = SELECT * FROM destination ORDER BY description ASC

#Load vintage
sql.startvintage_select = SELECT year, description FROM vintage ORDER BY year DESC
sql.vintage_select = SELECT * FROM vintage ORDER BY year DESC

#Load winetype
sql.startwinetype_select = SELECT index, description FROM winetype ORDER BY description ASC
sql.winetype_select = SELECT * FROM winetype ORDER BY description ASC

# PRODUCTS
sql.productsselect = SELECT * FROM products WHERE barcode= ?
sql.productsupdate = UPDATE products  SET wareNo=? , wareRow = ?, wareLoc = ? WHERE  barcode=?
sql.outgoingproduct = UPDATE products SET currentbal = ? WHERE barcode = ?
sql.getproductscust = SELECT * FROM products WHERE custname =? and currentbal > 0 ORDER BY barcode
sql.zeroproduct = UPDATE products SET  ZeroDate=?, active=? WHERE barcode=?

#CUSTOMERS
sql.insert_customers = INSERT INTO customer VALUES (null, ?,?)
sql.select_customers = SELECT  * FROM customer WHERE active= 1  ORDER by Name
sql.get_custdetails = SELECT Index from customer WHERE Name= ?

#TRANSACTIONS
sql.create_product1 = UPDATE products SET barcode =?, wareNo =?, wareRow =?, wareLoc =?, custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,   bincode=?, Mixed=?, ExcisePaid=? WHERE barcode =?
sql.create_product11 = UPDATE products SET   barcode =?,  wareNo =?, wareRow =?, wareLoc =?,  custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,  Mixed=?, ExcisePaid=?  WHERE barcode =?
sql.get_transact = SELECT dbdate, document1, dbquantity, dbvalue  FROM transact WHERE barcode= ? ORDER by dbdate
sql.newTrans =INSERT INTO  transact (barcode, dbdate, custindex, custname, category, dbquantity, dbvalue)  VALUES( ?,?,?,?,?,?,?)
sql.outTrans =INSERT INTO  transact (barcode, dbdate, document1, custindex, custname, category, dbquantity, dbvalue, label, excise, adjustment)  VALUES( ?,?,?,?,?,?,?,?,?,?,?)
sql.extbins = UPDATE ExternalBincontrol SET currentbal=? AND active=? WHERE barcode=?
sql.othertrans = INSERT INTO othertransact (barcode, dbdate, CategoryDesc, custindex, custname, category, dbquantity) VALUES(?,?,?,?,?,?,?)

#BARCODETRANSACTION
sql.create_barcodetrans = INSERT INTO BarcodeTrans (barcode, custindex, custname, date_start, date_end,  category) VALUES(?,?,?,?,?,?)
sql.getbarcodetrans = SELECT * FROM BarcodeTrans WHERE barcode = ?
sql.update_barcodetrans = UPDATE BarcodeTrans SET date_end =? WHERE barcode = ?

#BINCONTROL
sql.bincontrol_select = SELECT * FROM Bincontrol WHERE barcode = ?
sql.update_bincontrol = UPDATE Bincontrol set currentbal = ?, active = ?, Rental = ? WHERE barcode = ?

I hope this helps

David
Click to expand...
Thanks a stack, David. I will test when I get home in an hour. With MySQL you can test the connection. How do you do it with the access dB? To make sure rdc is running correctly.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Ryan Pigeon said:
I will test when I get home in an hour. With MySQL you can test the connection. How do you do it with the access dB? To make sure rdc is running correctly.
Click to expand...
For all DBs that work with jRDC2, it’s
http://yourserversiporname:yourportconfiguration/test
Click to expand...
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
BigBoss123 said:
Here is my Config file for using MS Access using a ACCDB database but will work on a MDB files as well.

Config file: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION


#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true
User=
Password=
ServerPort=17178


#example for MSAccess configuration (With password protection )
#DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
#JdbcUrl=jdbc:net.ucanaccess://C:/waistorage/storage1.accdb;memory=true
#JdbcUrl= "jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true;showSchema=true;openExclusive=true;immediatelyReleaseResources=true) 'change path, user as password as needed
#User= admin
#Password= waistorage123
#ServerPort=17178


#LOAD DATABASES
#Load Customers
sql.startcustomers = SELECT Index,  Name FROM customer WHERE active= 1  ORDER by  Name

#Load Categories
sql.startcategories_select = SELECT category, description FROM category WHERE category < 50 ORDER  BY category ASC
sql.category_select = SELECT category, bin FROM category WHERE description = ?
sql.categories_select = SELECT category, case, description, value, Bin FROM category WHERE category < 50 ORDER BY category ASC
sql.selectbincode = SELECT * FROM LastRow

#Load Destinations
sql.startdestination_select = SELECT loc_code, description FROM destination ORDER BY description ASC
sql.destination_select = SELECT * FROM destination ORDER BY description ASC

#Load vintage
sql.startvintage_select = SELECT year, description FROM vintage ORDER BY year DESC
sql.vintage_select = SELECT * FROM vintage ORDER BY year DESC

#Load winetype
sql.startwinetype_select = SELECT index, description FROM winetype ORDER BY description ASC
sql.winetype_select = SELECT * FROM winetype ORDER BY description ASC

# PRODUCTS
sql.productsselect = SELECT * FROM products WHERE barcode= ?
sql.productsupdate = UPDATE products  SET wareNo=? , wareRow = ?, wareLoc = ? WHERE  barcode=?
sql.outgoingproduct = UPDATE products SET currentbal = ? WHERE barcode = ?
sql.getproductscust = SELECT * FROM products WHERE custname =? and currentbal > 0 ORDER BY barcode
sql.zeroproduct = UPDATE products SET  ZeroDate=?, active=? WHERE barcode=?

#CUSTOMERS
sql.insert_customers = INSERT INTO customer VALUES (null, ?,?)
sql.select_customers = SELECT  * FROM customer WHERE active= 1  ORDER by Name
sql.get_custdetails = SELECT Index from customer WHERE Name= ?

#TRANSACTIONS
sql.create_product1 = UPDATE products SET barcode =?, wareNo =?, wareRow =?, wareLoc =?, custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,   bincode=?, Mixed=?, ExcisePaid=? WHERE barcode =?
sql.create_product11 = UPDATE products SET   barcode =?,  wareNo =?, wareRow =?, wareLoc =?,  custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,  Mixed=?, ExcisePaid=?  WHERE barcode =?
sql.get_transact = SELECT dbdate, document1, dbquantity, dbvalue  FROM transact WHERE barcode= ? ORDER by dbdate
sql.newTrans =INSERT INTO  transact (barcode, dbdate, custindex, custname, category, dbquantity, dbvalue)  VALUES( ?,?,?,?,?,?,?)
sql.outTrans =INSERT INTO  transact (barcode, dbdate, document1, custindex, custname, category, dbquantity, dbvalue, label, excise, adjustment)  VALUES( ?,?,?,?,?,?,?,?,?,?,?)
sql.extbins = UPDATE ExternalBincontrol SET currentbal=? AND active=? WHERE barcode=?
sql.othertrans = INSERT INTO othertransact (barcode, dbdate, CategoryDesc, custindex, custname, category, dbquantity) VALUES(?,?,?,?,?,?,?)

#BARCODETRANSACTION
sql.create_barcodetrans = INSERT INTO BarcodeTrans (barcode, custindex, custname, date_start, date_end,  category) VALUES(?,?,?,?,?,?)
sql.getbarcodetrans = SELECT * FROM BarcodeTrans WHERE barcode = ?
sql.update_barcodetrans = UPDATE BarcodeTrans SET date_end =? WHERE barcode = ?

#BINCONTROL
sql.bincontrol_select = SELECT * FROM Bincontrol WHERE barcode = ?
sql.update_bincontrol = UPDATE Bincontrol set currentbal = ?, active = ?, Rental = ? WHERE barcode = ?

I hope this helps

David
Click to expand...
Hi there, David. I hope you are well. I used your config example and when I test (http://localhost:17178/test), it says error fetching connection. Here is the code:

#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/users/public/test.mdb;memory=true
User=
Password=
ServerPort=17178
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
BigBoss123 said:
Here is my Config file for using MS Access using a ACCDB database but will work on a MDB files as well.

Config file: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION


#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true
User=
Password=
ServerPort=17178


#example for MSAccess configuration (With password protection )
#DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
#JdbcUrl=jdbc:net.ucanaccess://C:/waistorage/storage1.accdb;memory=true
#JdbcUrl= "jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true;showSchema=true;openExclusive=true;immediatelyReleaseResources=true) 'change path, user as password as needed
#User= admin
#Password= waistorage123
#ServerPort=17178


#LOAD DATABASES
#Load Customers
sql.startcustomers = SELECT Index,  Name FROM customer WHERE active= 1  ORDER by  Name

#Load Categories
sql.startcategories_select = SELECT category, description FROM category WHERE category < 50 ORDER  BY category ASC
sql.category_select = SELECT category, bin FROM category WHERE description = ?
sql.categories_select = SELECT category, case, description, value, Bin FROM category WHERE category < 50 ORDER BY category ASC
sql.selectbincode = SELECT * FROM LastRow

#Load Destinations
sql.startdestination_select = SELECT loc_code, description FROM destination ORDER BY description ASC
sql.destination_select = SELECT * FROM destination ORDER BY description ASC

#Load vintage
sql.startvintage_select = SELECT year, description FROM vintage ORDER BY year DESC
sql.vintage_select = SELECT * FROM vintage ORDER BY year DESC

#Load winetype
sql.startwinetype_select = SELECT index, description FROM winetype ORDER BY description ASC
sql.winetype_select = SELECT * FROM winetype ORDER BY description ASC

# PRODUCTS
sql.productsselect = SELECT * FROM products WHERE barcode= ?
sql.productsupdate = UPDATE products  SET wareNo=? , wareRow = ?, wareLoc = ? WHERE  barcode=?
sql.outgoingproduct = UPDATE products SET currentbal = ? WHERE barcode = ?
sql.getproductscust = SELECT * FROM products WHERE custname =? and currentbal > 0 ORDER BY barcode
sql.zeroproduct = UPDATE products SET  ZeroDate=?, active=? WHERE barcode=?

#CUSTOMERS
sql.insert_customers = INSERT INTO customer VALUES (null, ?,?)
sql.select_customers = SELECT  * FROM customer WHERE active= 1  ORDER by Name
sql.get_custdetails = SELECT Index from customer WHERE Name= ?

#TRANSACTIONS
sql.create_product1 = UPDATE products SET barcode =?, wareNo =?, wareRow =?, wareLoc =?, custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,   bincode=?, Mixed=?, ExcisePaid=? WHERE barcode =?
sql.create_product11 = UPDATE products SET   barcode =?,  wareNo =?, wareRow =?, wareLoc =?,  custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,  Mixed=?, ExcisePaid=?  WHERE barcode =?
sql.get_transact = SELECT dbdate, document1, dbquantity, dbvalue  FROM transact WHERE barcode= ? ORDER by dbdate
sql.newTrans =INSERT INTO  transact (barcode, dbdate, custindex, custname, category, dbquantity, dbvalue)  VALUES( ?,?,?,?,?,?,?)
sql.outTrans =INSERT INTO  transact (barcode, dbdate, document1, custindex, custname, category, dbquantity, dbvalue, label, excise, adjustment)  VALUES( ?,?,?,?,?,?,?,?,?,?,?)
sql.extbins = UPDATE ExternalBincontrol SET currentbal=? AND active=? WHERE barcode=?
sql.othertrans = INSERT INTO othertransact (barcode, dbdate, CategoryDesc, custindex, custname, category, dbquantity) VALUES(?,?,?,?,?,?,?)

#BARCODETRANSACTION
sql.create_barcodetrans = INSERT INTO BarcodeTrans (barcode, custindex, custname, date_start, date_end,  category) VALUES(?,?,?,?,?,?)
sql.getbarcodetrans = SELECT * FROM BarcodeTrans WHERE barcode = ?
sql.update_barcodetrans = UPDATE BarcodeTrans SET date_end =? WHERE barcode = ?

#BINCONTROL
sql.bincontrol_select = SELECT * FROM Bincontrol WHERE barcode = ?
sql.update_bincontrol = UPDATE Bincontrol set currentbal = ?, active = ?, Rental = ? WHERE barcode = ?

I hope this helps

David
Click to expand...

Hi David. Would you mind having a look at my code? Thank you!
BigBoss123 said:
Here is my Config file for using MS Access using a ACCDB database but will work on a MDB files as well.

Config file: 
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION


#example for MSAccess configuration (No password )
DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true
User=
Password=
ServerPort=17178


#example for MSAccess configuration (With password protection )
#DriveClass=net.ucanaccess.jdbc.UcanaccessDriver
#JdbcUrl=jdbc:net.ucanaccess://C:/waistorage/storage1.accdb;memory=true
#JdbcUrl= "jdbc:ucanaccess://C:/waistorage/storage.accdb;memory=true;showSchema=true;openExclusive=true;immediatelyReleaseResources=true) 'change path, user as password as needed
#User= admin
#Password= waistorage123
#ServerPort=17178


#LOAD DATABASES
#Load Customers
sql.startcustomers = SELECT Index,  Name FROM customer WHERE active= 1  ORDER by  Name

#Load Categories
sql.startcategories_select = SELECT category, description FROM category WHERE category < 50 ORDER  BY category ASC
sql.category_select = SELECT category, bin FROM category WHERE description = ?
sql.categories_select = SELECT category, case, description, value, Bin FROM category WHERE category < 50 ORDER BY category ASC
sql.selectbincode = SELECT * FROM LastRow

#Load Destinations
sql.startdestination_select = SELECT loc_code, description FROM destination ORDER BY description ASC
sql.destination_select = SELECT * FROM destination ORDER BY description ASC

#Load vintage
sql.startvintage_select = SELECT year, description FROM vintage ORDER BY year DESC
sql.vintage_select = SELECT * FROM vintage ORDER BY year DESC

#Load winetype
sql.startwinetype_select = SELECT index, description FROM winetype ORDER BY description ASC
sql.winetype_select = SELECT * FROM winetype ORDER BY description ASC

# PRODUCTS
sql.productsselect = SELECT * FROM products WHERE barcode= ?
sql.productsupdate = UPDATE products  SET wareNo=? , wareRow = ?, wareLoc = ? WHERE  barcode=?
sql.outgoingproduct = UPDATE products SET currentbal = ? WHERE barcode = ?
sql.getproductscust = SELECT * FROM products WHERE custname =? and currentbal > 0 ORDER BY barcode
sql.zeroproduct = UPDATE products SET  ZeroDate=?, active=? WHERE barcode=?

#CUSTOMERS
sql.insert_customers = INSERT INTO customer VALUES (null, ?,?)
sql.select_customers = SELECT  * FROM customer WHERE active= 1  ORDER by Name
sql.get_custdetails = SELECT Index from customer WHERE Name= ?

#TRANSACTIONS
sql.create_product1 = UPDATE products SET barcode =?, wareNo =?, wareRow =?, wareLoc =?, custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,   bincode=?, Mixed=?, ExcisePaid=? WHERE barcode =?
sql.create_product11 = UPDATE products SET   barcode =?,  wareNo =?, wareRow =?, wareLoc =?,  custindex=?, custname=?, winetype=?, destination=?, category=?, year=?, date=?, beginning=?, currentbal=?, lotcode=?, active=?,  Mixed=?, ExcisePaid=?  WHERE barcode =?
sql.get_transact = SELECT dbdate, document1, dbquantity, dbvalue  FROM transact WHERE barcode= ? ORDER by dbdate
sql.newTrans =INSERT INTO  transact (barcode, dbdate, custindex, custname, category, dbquantity, dbvalue)  VALUES( ?,?,?,?,?,?,?)
sql.outTrans =INSERT INTO  transact (barcode, dbdate, document1, custindex, custname, category, dbquantity, dbvalue, label, excise, adjustment)  VALUES( ?,?,?,?,?,?,?,?,?,?,?)
sql.extbins = UPDATE ExternalBincontrol SET currentbal=? AND active=? WHERE barcode=?
sql.othertrans = INSERT INTO othertransact (barcode, dbdate, CategoryDesc, custindex, custname, category, dbquantity) VALUES(?,?,?,?,?,?,?)

#BARCODETRANSACTION
sql.create_barcodetrans = INSERT INTO BarcodeTrans (barcode, custindex, custname, date_start, date_end,  category) VALUES(?,?,?,?,?,?)
sql.getbarcodetrans = SELECT * FROM BarcodeTrans WHERE barcode = ?
sql.update_barcodetrans = UPDATE BarcodeTrans SET date_end =? WHERE barcode = ?

#BINCONTROL
sql.bincontrol_select = SELECT * FROM Bincontrol WHERE barcode = ?
sql.update_bincontrol = UPDATE Bincontrol set currentbal = ?, active = ?, Rental = ? WHERE barcode = ?

I hope this helps

David
Click to expand...
Hi David. Would you mind having a look at my code? If so, how can I get the zip file to you? Thank you.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
You need to post the log output of the jRDC2 server. Either run it via B4J or run it via command line. Either way, copy and paste the output here. Also, it is always advisable in situations like this to write a small B4J app that tries to open/close the DB, just to make sure that the DB can be accessed properly. It's usually simpler to troubleshoot any access issues with the underlying DB.
 
Upvote 0
Mashiane

Mashiane

Expert
Licensed User
Longtime User
Now I notice this is a 3rd question with this,

Found this, could be worth checking out:

Jrdc with ucanaccess not committing inserts (Solved)

I have Jrdc connecting with ms access ( would prefer not to use access but stuck with it for now). Its working fine for select queries but having trouble with inserts. I am testing on a simple table with an auto 'id' column and 'UserName' text column with unique constraint. I have the config...
www.b4x.com www.b4x.com
 
Upvote 0
B

BigBoss123

Member
Licensed User
Longtime User
Ryan Pigeon said:
IS THERE ANYONE THAT CAN HAVE A LOOK AT MY CODE? I AM AT THE VERY FINAL STRETCH ON THE WAY TO WIN THIS! JUST NEED A LITTLE HELP!
Click to expand...
Have you opened the port 17178? You need this both outwards and inwards.

You can check this in 'Settings' and then the Network Connections under 'advanced Settings'

Cheers
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
Guys...I REALLY appreciate all the pointers and am happy to report that I have conquered this quest. To BigBoss and Erel's posts, I thank thee... you were invaluable. And to all the guys that want the solution.... I'll be happy to share my code with you just email me... ryan@easyas.co.za. Love to you all.
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
Mashiane said:
Now I notice this is a 3rd question with this,

Found this, could be worth checking out:

Jrdc with ucanaccess not committing inserts (Solved)

I have Jrdc connecting with ms access ( would prefer not to use access but stuck with it for now). Its working fine for select queries but having trouble with inserts. I am testing on a simple table with an auto 'id' column and 'UserName' text column with unique constraint. I have the config...
www.b4x.com www.b4x.com
Click to expand...
Mashy, you are a true gentleman. Thank you too for all your guidance. If you need my YELP with anything, I'll be here!
 
Upvote 0
Mashiane

Mashiane

Expert
Licensed User
Longtime User
If your database will be hosted locally, you can just use jSQL direct connection, this has been my implementation. I am of the opinion that anyway, the same configuation and setup would work on your side.

[BANanoVuetifyAD3] BANanoServer - Direct MS-ACCESS CRUD

Hi there A rather incredible opportunity!!! I mean accessing MS-ACCESS databases directly from the jetty server with jSQL. Download This has been rather a tricky exercise. 1st, thanks to @Erel post here. BTW, you need to follow the steps on that post first to make this work...
www.b4x.com www.b4x.com
 
Upvote 0
Ryan Pigeon

Ryan Pigeon

Member
Mashiane said:
If your database will be hosted locally, you can just use jSQL direct connection, this has been my implementation. I am of the opinion that anyway, the same configuation and setup would work on your side.

[BANanoVuetifyAD3] BANanoServer - Direct MS-ACCESS CRUD

Hi there A rather incredible opportunity!!! I mean accessing MS-ACCESS databases directly from the jetty server with jSQL. Download This has been rather a tricky exercise. 1st, thanks to @Erel post here. BTW, you need to follow the steps on that post first to make this work...
www.b4x.com www.b4x.com
Click to expand...
HAVE YOU GOT AN EXAMPLE FOR THIS?
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Ryan Pigeon
  • Question
Android Question MSACCESS & jRDC2: SOLVED!!
Replies
6
Views
2K
MarrowZero
MarrowZero
Sergio Castellari
  • Question
Android Question How do I indicate a date = "0000-00-00" in jRDC2, in the Config.Properties file?
Replies
2
Views
1K
Sergio Castellari
Sergio Castellari
M
  • Question
Other SQL dynamic queries with jRDC2
Replies
9
Views
2K
MrKim
MrKim
josejad
  • Article
Android Example [B4XPages] jRDC2 + MySql CRUD + Login
2 3
Replies
43
Views
12K
aeric
aeric
Erel
  • Locked
  • Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
138
Views
143K
Erel
Erel
Top