ODBC

[Dave Zowasky]

Hello to all,

I am trying to create and populate a DBF or MDB via ODBC and have been running into primarykey problems.

Is there any way around this problem with these types of odbc drivers?

Do I need to move to an SQL driver?

here is some of my code:

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()
local adsn
local xdsn
local oodbc
local oodbca

xdsn:="TESTDBF"

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( "Test", { { "Name",  "C", 50, 0,"NOT NULL PRIMARY KEY" },;
                         { "phone", "C", 15, 0,"" },;
                         { "age",   "C",  3, 0,"" } } )

 oodbca:=TdbOdbcDirect():new("SELECT * FROM TEST",oodbc)

           oodbca:AddNew()
           oodbca:Update()

oodbc:end()

return nil

Thanks

[Antonio Linares]

Dave,

ADO is the way to go.

Please make a search for ADO in these forums. There are many examples about it

[Dave Zowasky]

Antonio,

I have downloaded and installed MS SQL Server 2008

http://www.microsoft.com/express/sql/download/


I created my odbc dsn .

I am able to build my table via odbc very well, however I am having trouble figuring out what is the
best way to populate the table with data.


Any advice would be most appreciated.

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()
local adsn
local xdsn
local oodbc
local oodbca

xdsn:="testsql"

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( "Test", { { "Name",  "C", 50, 0,"NOT NULL PRIMARY KEY" },;
                         { "phone", "C", 15, 0,"" },;
                         { "age",   "C",  3, 0,"" } } )

 oodbca:=TdbOdbcDirect():new("SELECT * FROM TEST",oodbc)

           oodbca:AddNew()
           oodbca:fieldput(1,"tester 123")
           oodbca:Update()

oodbc:end()

return nil

Thanks

[Dave Zowasky]

Got this to work

Looks good now!

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()
local adsn
local xdsn
local oodbc
local oodbca

xdsn:="testsql"

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( "Test", { { "Name",  "C", 50, 0,"NOT NULL PRIMARY KEY" },;
                         { "phone", "C", 15, 0,"" },;
                         { "age",   "C",  3, 0,"" } } )

 oodbca:=TdbOdbcDirect():new("SELECT * FROM TEST",oodbc)

oodbca:open()

           oodbca:AddNew()
           oodbca:fieldput(1,"tester 123")
           oodbca:Update()
 

Thanks

[Dave Zowasky]

Hello,

I have a new problem.

When I run this code I get

FiveODBC Error TDbOdbcDirect:Update()
[#0] Class:S1000
[Microsoft][SQL Srever Native Client 10.0] Connection is Busy with the Results for another command

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()
local adsn
local xdsn:="testsql"
local oodbc
local odbf
local xt:="TEST"
local x:=0

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( xt, { { "Name",  "C", 50, 0,"NOT NULL PRIMARY KEY" },;
                    { "phone", "C", 15, 0,"" },;
                    { "age",   "C",  3, 0,"" } } )
oodbc:end()
do while x<3
   x=x+1
   oodbc:=TOdbc():new(xdsn)

   odbf:=TdbOdbcDirect():new("SELECT * FROM "+xt ,oodbc)
   odbf:open()
   odbf:AddNew()
   odbf:fieldput(1,"tester"+ltrim(str(x)))
   odbf:Update()
   odbf:complete()

   oodbc:commit()
   oodbc:end()

 enddo
return nil

Any ideas on how to fix this problem?

Thanks

[Dave Zowasky]

Hello to all:

I got this to work:

using a complete() after each odbf: operation.

Code: Select all  Expand view

#include "FiveWin.ch"

function Main()
local adsn
local xdsn:="testsql"
local oodbc
local odbf
local xt:="TEST"
local x:=0

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( xt, { { "Name",  "C", 50, 0,"NOT NULL PRIMARY KEY" },;
                    { "phone", "C", 15, 0,"" },;
                    { "age",   "C",  3, 0,"" } } )
oodbc:end()


do while x<10
   x=x+1
   oodbc:=TOdbc():new(xdsn)
   odbf:=TdbOdbcDirect():new("SELECT * FROM "+xt ,oodbc)

   odbf:open()
   odbf:complete()
   odbf:AddNew()
   odbf:complete()
   odbf:fieldput(1,"tester"+ltrim(str(x)))
   odbf:complete()
   odbf:update()
   odbf:complete()
   odbf:end()

   oodbc:commit()
   oodbc:end()
 enddo
return nil

[kajot]

when I run the program I will get the error

SQLDriverConnect error from TODbc:New()

Program:
#include "FiveWin.ch"
#include "XBrowse.Ch"
#include "sql.ch"

function Main()
local adsn
local xdsn
local oodbc
local oodbca

xdsn:="inkom"

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( "Test", { { "Name", "C", 50, 0,"NOT NULL PRIMARY KEY" },;
{ "phone", "C", 15, 0,"" },;
{ "age", "C", 3, 0,"" } } )

oodbca:=TdbOdbcDirect():new("SELECT * FROM TEST",oodbc)
oodbca:AddNew()
oodbca:fieldput(1,"tester 123")
oodbca:Update()

oodbc:end()

return nil


best regards
kajot

[Dave Zowasky]

Kajot,


It looks like you are missing oodbca:open() and oodbca:complete() statements in your code.

I added them in and it works.

Code: Select all  Expand view

#include "FiveWin.ch"
#include "XBrowse.Ch"
#include "sql.ch"

function Main()
local adsn
local xdsn
local oodbc
local oodbca

xdsn:="testsql"

oodbc:=TOdbc():new(xdsn)
oOdbc:Create( "TEST3", { { "Name", "C", 50, 0,"NOT NULL PRIMARY KEY" },;
{ "phone", "C", 15, 0,"" },;
{ "age", "C", 3, 0,"" } } )

oodbca:=TdbOdbcDirect():new("SELECT * FROM Test3",oodbc)
oodbca:open()
oodbca:complete()
oodbca:AddNew()
oodbca:fieldput(1,"tester 123")
oodbca:complete()
oodbca:Update()
oodbca:complete()
oodbca:end()
oodbc:end()

return nil


[kajot]

I got the same error

SQLDriverConnect error from TODbc:New()


I want to conect to MSSQL 2005 express, ODBC TEST works OK.

bestregards
kajot

[Dave Zowasky]

Antonio,

We have applied an ODBC export that we use within some of our products to create
an sql database table and it works great. As we were testing it the other day I noticed that
I was having an issue that appears to be tied to exceeding 255 fields. We were seeing an index crash
on the temporary dbf file. I put in a field limit of 255 in the ODBC parsing within our product and it
has been working perfectly.

I was wondering if the 255 issue I am is seeing correct.

The product I was working on is using my older version of FWH so I was not sure if that is an issue.

Also I was wondering if there was any way for me to code the temporary files another way to get around the problem.

Last question is if there is a 255 field limit does 64 bits help?

As always for all your help.