Database With ODBC + FNXbasic : It works

[Bruno FR28]

Bonjour/ hello

I propose my first try for using ODBC with FNXbasic. the file ZIP contains :
- a file .inc to declare const� and function ODBC
- a file .bas which connect and select a Access Database
- a file .doc to create the DNS for this example (DNS "test")
- a file .mdb (sample.mdb)
- a file .exe

Warning : the all ODBC functions are not declare in the .inc, Only the main for usual query.

test on Win XP SP2 French

Cordialement/Regards
Bruno.

[cvirus]

Nice work i have tested and it works like a charm, the next thing is to implement the update the insert delect and all that stuff to make a complete working database system.

I have notice that the .inc file contains the constants and also the API calls.

Thanks

[Bruno FR28]

Hello Cvirus

easy for Insert, Update.. put this code before the comment "ODBC - tape 6", in the ODBCAPI.bas

` end of previous select , remark SQL CLOSE, and not SQLDROP
SQLFreeStmt.SQLStmt=glSQLstmt
SQLFreeStmt.EndOption =SQL_CLOSE
SQLFreeStmt.execute

` an update for example, but could be insert or delete.
SQL ="update table1 set col1=col1+1 where col1 = (select max(col1) from table1)"
SQLExecDirect.SQLStmt =glSQLStmt
SQLExecDirect.sqlString =SQL
SQLExecDirect.sqlstrlen =len(SQL)
SQLExecDirect.execute

` no data return, so no loop to get data. just test the SQL syntax
if SQLExecDirect.result <> SQL_SUCCESS then
  SQLError.env = glEnv
  SQLError.dbc = glDbc
  SQLError.SQLStmt = glSQLstmt 
  SQLError.Buflen = 255
  SQLError.Outlen = varptr(lgcolval)
  SQLerror.execute

  wstr= "Erreur SQL "+ CR
  wstr= wstr + "ODBC State : "+ SQLError.SQLstate + CR
     wstr= wstr + "ODBC Native Error : "+ str$(SQLError.NativeError) + CR
  Wstr= wstr + "ODBC Message : " + SQLError.buffer

  i=MessageDlg(wstr,"Erreur SQL", mbok+mbiconhand)
end if

`ODBC tape 6 : ....

after each execute, the max col1 increase.

Cordialement/Regards
Bruno

PS : I collect all the remarks, and i will write a new version.

[cvirus]

This is very usefull but a bit confused, maybe youi should turn that into ann lib and use functions to do the job, maybe more friendly that way, or as soon as you finish write a tuto to explain that stuff.

[Bruno FR28]

oh !� �I recognize that it is hard at the beginning but it`s the normal use of the ODBC Library....

But to work with DataBase, you need know 2 things :
1- SQL for working data
2 - the library to use SQL in your program here ODBC

it is not very easy, otherwise why professional developper? but it is possible for everyone with some time



Bruno

[cvirus]

Are a prof. Developer? I know that with some time everybody can make an app with odbc, but like that is a bit hard because the sql commands are not very clear.  I can make an app like that in no time with java, bwcause the sintax is clear.  ;D

[Bruno FR28]

I was... i stopped writting program 12 years ago.

the SQL and ODBC are 2 different things : ODBC make you program discuss with the database Engine (give orders and get the results), and the SQL manage the Data ( tell to the Data Engine what doing).
i suppose in Java , you have many classes to replace ODBC but the difficulty of SQL stay anyway.

Bruno.
is my english comprehensible ?

[cvirus]

In java there are several ways to connect to diferente databases, and its a lot easy,  a know that odbc and sql are two diferent things but thanks, and yes your english is nice.

[Bruno FR28]

Bonjour/hello

I read the FNXbasicHelp, and there is noway to declare user class. only record for data, but no method. and Type is just to expand existing type.

I must to write function "to the old way" (connect(db) instead db.connect for example).

Bye
Bruno

[martiekr]

Hi Bruno,

SQL is just like every other language: you have to learn it.
It is not difficult, just different :D .
I`m a programmer for almost 40 years now and i started using SQL 10 years ago (MS-Acces). Now i am working with Database-angines like MySQL, PostgreSQL, DB2 (and MS-Access) all using SQL.
I can create very complex SQL statements (multiple tabels + joins).
Btw. having a MS-Access around can be very handy as it has a (graphical) query builder. So you put the tables in the querybuilder, drag the JOIN connect between the tables(columns) and there you go, the (complex) SQL is ready to use (90% of the time werking correct).

Grtz.
Martie
[quote author=6B6F7F747F7928221A0 link=1344582769/6#6 date=1344632541]I was... i stopped writting program 12 years ago.

the SQL and ODBC are 2 different things : ODBC make you program discuss with the database Engine (give orders and get the results), and the SQL manage the Data ( tell to the Data Engine what doing).
i suppose in Java , you have many classes to replace ODBC but the difficulty of SQL stay anyway.

Bruno.
is my english comprehensible ?[/quote]