8
uses odbcsql,SysUtils,Classes;
11
TDSNTypes = (dtUser,dtSystem,dtBoth);
12
TODBCParamType = (ptUnknown,ptInput,ptInputOutput,ptResult,ptOutput,ptRetVal);
13
TODBCParamTypes = Set of TODBCParamType;
15
TODBCObject = Class(TComponent)
18
FHandleType : SQLSmallint;
19
Function GetHandle : SQLHandle;
20
function GetHandleAllocated: Boolean;
21
function GetExtendedErrorInfo: String;
23
Function CreateHandle : SQLHandle; Virtual;
24
Function ParentHandle : SQLHandle; Virtual;
26
Function CheckODBC(Res : Integer;Msg : String) : Integer;
28
Destructor Destroy; override;
29
Property Handle : SQLHandle Read GetHandle;
30
Property HandleAllocated : Boolean Read GetHandleAllocated;
33
TODBCEnvironment = Class(TODBCObject)
35
FODBCBehaviour : Integer;
36
procedure SetODBCbehaviour(const Value: Integer);
37
function GetNullTerminate: Boolean;
38
procedure SetNullTerminate(const Value: Boolean);
40
function CreateHandle: SQLHandle; override;
41
Procedure SetIntAttribute(Const Attr,Value : Integer);
42
Procedure SetStringAttribute(Const Attr: Integer; Value : String);
43
Function GetIntAttribute(Const Attr : Integer) : Integer;
44
Function GetStringAttribute(Const Attr : Integer) : String;
46
Constructor Create(Aowner : TComponent);override;
47
Function GetDriverNames(List : Tstrings) : Integer;
48
Function GetDataSourceNames(List : Tstrings; Types : TDSNTypes;Descriptions : Boolean) : Integer;
49
function GetDriverOptions(Driver: String; Options: TStrings): Integer;
50
Property ODBCBehaviour : Integer Read FODBCBehaviour Write SetODBCbehaviour;
51
Property NullTerminateStrings : Boolean Read GetNullTerminate Write SetNullTerminate;
54
TConnectionBrowseEvent = Procedure (Sender : TObject;InParams,OutParams : Tstrings) of Object;
56
TODBCConnection = Class(TODBCObject)
59
FDriverParams : TStrings;
64
FEnvironMent : TODBCEnvironment;
65
FOnBrowseConnection : TConnectionBrowseEvent;
66
FWindowHandle : integer;
67
FDriverCOmpletion: SQLUSmallInt;
68
function GetDriverName: String;
69
function GetDriverParams: TStrings;
70
procedure SetActive(const Value: Boolean);
71
procedure SetDriverName(const Value: String);
72
procedure SetDriverParams(const Value: TStrings);
73
procedure SetDSN(const Value: String);
74
function GetEnvironment: TODBCEnvironMent;
75
procedure SetEnvironment(const Value: TODBCEnvironMent);
77
procedure ConnectToDriver;
78
procedure ConnectToDSN;
79
Procedure ConnectBrowsing;
80
Function ParentHandle : SQLHandle; override;
81
Procedure CheckActive;
82
Procedure CheckInActive;
84
Constructor Create(Aowner : TComponent);override;
85
Destructor Destroy; override;
88
Procedure GetTableNames(S : TStrings; SystemTables : Boolean);
89
Procedure GetFieldNames(TableName : String; S : TStrings);
90
Procedure GetPrimaryKeyFields(TableName : String; S : TStrings);
91
procedure GetProcedureNames(S : TStrings);
92
procedure GetProcedureParams(ProcName : String;ParamTypes : TODBCParamTypes; S : TStrings);
93
Property DSN : String Read FDSN Write SetDSN;
94
Property DriverName : String Read GetDriverName Write SetDriverName;
95
Property DriverCompletion : SQLUSmallInt Read FDriverCOmpletion Write FDriverCompletion;
96
Property DriverParams : TStrings Read GetDriverParams Write SetDriverParams;
97
Property Active : Boolean Read FActive Write SetActive;
98
Property Environment : TODBCEnvironMent Read GetEnvironment Write SetEnvironment;
99
Property UserName : String Read FUserName Write FUserName;
100
Property Password : string Read FPassword Write FPassword;
101
Property OnBrowseConnection : TConnectionBrowseEvent Read FonBrowseConnection Write FOnBrowseConnection;
102
Property WindowHandle : integer Read FWindowHandle Write FWindowHandle;
105
TODBCStatement = Class;
107
TODBCFieldList = Class(TCollection)
109
FStatement : TODBCStatement;
111
Constructor Create(Statement : TODBCStatement);
115
TODBCStatement allocates 1 big data buffer. For each bound field
116
two things are allocated in the buffer:
117
- Size of fetched data as filled in by fetch.
118
- data. (may be zero for blobs etc)
119
The FBuffOffset contains the offset in the buffer of the size field.
120
Data immediatly follows the size.
123
TODBCField = Class(TCollectionItem)
126
FPosition : SQLSmallInt;
128
FSize : SQLUInteger; // Declared size, as returned by DescribeCol
130
FDataType : SQLSmallInt; // Declared type, as returned by DescribeCol
131
FBuffOffSet : SQLInteger; // Offset in data buffer.
132
FBuffer : Pointer; // Pointer to data.
133
FBufSize : SQLInteger; // Allocated buffer size.
134
FBufType : SQLSmallInt; // Allocated buffer type
135
function GetAsString: String;
136
function GetData : PChar;
137
Function GetIsNull : Boolean;
138
Function GetAsInteger : Integer;
139
Function GetAsBoolean : Boolean;
140
Function GetAsDouble : Double;
141
Function GetAsDateTime : TDateTime;
143
Property Position : SQLSmallint Read FPosition;
144
Property Name : String read FName;
145
Property DataType : SQLSmallInt read FDatatype;
146
Property Size : SQLUinteger read FSize;
147
property DecimalDigits : SQLSmallInt read FDecimalDigits;
148
Property Nullable : Boolean Read FNullable;
149
Property Data : Pchar Read GetData;
150
Property BufType : SQLSmallInt Read FBufType;
151
Property BufSize : SQLInteger Read FBufSize;
152
Property IsNull : Boolean Read GetIsNull;
153
Property AsString : String Read GetAsString;
154
Property AsInteger : Integer Read GetAsInteger;
155
Property AsBoolean : Boolean Read GetAsBoolean;
156
Property AsDouble : Double Read GetAsDouble;
157
Property AsDateTime : TDateTime Read GetAsDateTime;
160
TODBCStatement = Class(TODBCObject)
163
FConnection: TODBCConnection;
164
FFields : TODBCFieldList;
167
Function ParentHandle : SQLHandle; override;
168
procedure SetConnection(const Value: TODBCConnection);
169
procedure AllocBuffers;
171
Constructor Create(Aowner : TComponent);override;
172
Destructor Destroy; override;
173
Procedure BindFields(RestrictList : TStrings);virtual;
174
Procedure ClearFields;virtual;
175
Function Fetch : Boolean;
176
Property Connection : TODBCConnection Read FConnection Write SetConnection;
177
Property BOF : Boolean read FBOF;
178
Property EOF : Boolean read FEOF;
179
Property Fields : TODBCFieldList Read FFields;
182
TODBCTableList = Class(TODBCStatement)
184
Procedure GetTableNames(S : TStrings; SystemTables : Boolean);
187
TODBCFieldNamesList = Class(TODBCStatement)
189
Procedure GetFieldNames(TableName : String;S : TStrings);
192
TODBCPrimaryKeyFieldsList = Class(TODBCStatement)
194
Procedure GetPrimaryKeyFields(TableName : String;S : TStrings);
197
TODBCProcedureList = Class(TODBCStatement)
199
Procedure GetProcedureList(S : TStrings);
202
TODBCProcedureParams = Class(TODBCStatement)
203
Procedure GetProcedureParams(ProcName: String; ParamTypes: TODBCParamTypes; S: TStrings);
206
TStatementState = (ssInactive,ssPrepared,ssBound,ssOpen);
208
TODBCSQLStatement = Class(TODBCStatement)
211
FState : TStatementState;
212
function GetActive: Boolean;
213
procedure SetActive(const Value: Boolean);
215
procedure FreeStatement(Option: SQLUSMALLINT);
216
procedure ExecuteDirect;
217
procedure ExecutePrepared;
218
Procedure SetSQL(const Value: TStrings);
220
Constructor Create(Aowner : TComponent);override;
221
Destructor Destroy; override;
224
Procedure BindFields(RestrictList : TStrings);override;
228
procedure GetFieldList(List: TStrings);
229
Property Active : Boolean Read GetActive Write SetActive;
230
Property SQL : TStrings Read FSQL Write SetSQL;
233
EODBCError = Class(Exception);
236
ODBCParamTypeNames : Array [TODBCParamType] of string
237
= ('Unknown','Input','Input/Output','Result','Output','RetVal');
239
Function DefaultEnvironment : TODBCEnvironment;
246
SErrUnexpected = 'Unexpected ODBC error:';
247
SErrEnvironmentHandle = 'Cannot allocate environment handle:';
248
SErrInvalidBehaviour = 'Invalid value for ODBC behaviour: %d';
249
SErrNotConnected = 'Operation invalid when not connected.';
250
SErrConnected = 'Operation invalid when connected.';
251
SNeedDSNOrDriver = 'Cannot connect with empty DSN and driver names.';
252
SErrGettingDataSources = 'Error getting datasources:';
253
SErrGettingDriverNames = 'Error getting driver names:';
254
SErrGettingDriverOptions = 'Error getting driver options:';
255
SErrSettingEnvAttribute = 'Error setting environment attribute:';
256
SErrGettingEnvAttribute = 'Error Getting environment attribute:';
257
SErrBrowseConnecting = 'Error connecting to datasource via browse:';
258
SErrDSNConnect = 'Error connecting to DSN:';
259
SErrDriverConnect = 'Error connecting to driver:';
260
SErrDisconnecting = 'Error disconnecting:';
261
SErrNoConnectionForStatement = 'Missing connection for statement.';
262
SErrNoSQLStatement = 'Missing SQL statement.';
263
SErrPreparing = 'Error preparing statement:';
264
SErrGettingTableNames = 'Error getting table names:';
265
SErrFetchingData = 'Error fetching data:';
266
SErrFieldNames = 'Error getting field names:';
267
SErrPrimaryKeys = 'Error getting primary key names:';
268
SErrProcedureNames = 'Error getting procedure names:';
269
SErrExecuting = 'Error while executing statement:';
270
SErrExecutingPrepared = 'Error while executing prepared statement:';
271
SErrNotPrepared = 'Statement is not prepared';
272
SErrNotInactive = 'Statement is already prepared or executed.';
273
SErrStatementActive = 'A statement is still active';
274
SErrColumnCount = 'Error retrieving cilumn count:';
275
SErrColDescription = 'Error retrieving column description';
276
SErrInvalidConversion = 'invalid type conversion';
277
SErrBindCol = 'Error binding column';
279
ODBCSuccess = [SQL_SUCCESS,SQL_SUCCESS_WITH_INFO];
281
Procedure ODBCError (Msg : String);
284
Raise EODBCError.Create(Msg);
287
Procedure ODBCErrorFmt (Fmt : String;Args : Array of const);
290
Raise EODBCError.CreateFmt(Fmt,Args);
293
Function CheckODBC(Res : Integer;Msg : String) : Integer;
297
if not Res in [SQL_SUCCESS,SQL_SUCCESS_WITH_INFO] then
301
ODBCErrorFmt(msg,[res]);
305
function TODBCObject.CheckODBC(Res: Integer; Msg: String): Integer;
311
if not Res in [SQL_SUCCESS,SQL_SUCCESS_WITH_INFO] then
315
S:=GetExtendedErrorInfo;
317
Msg:=Msg+LineEnding+S;
322
function TODBCObject.GetExtendedErrorInfo : String;
326
I,MsgLen : SQLSmallInt;
327
SQLState : Array[0..6] of Char;
328
NativeError : SQLInteger;
329
MSg : Array[0..SQL_MAX_MESSAGE_LENGTH] of Char;
330
SState,SMsg : String;
337
Res:=SQLGetDiagRec(FhandleType, FHandle, i, SqlState, NativeError,
338
Msg, sizeof(Msg), MsgLen);
339
If Res<>SQL_NO_DATA then
343
If Length(Result)>0 then
344
Result:=Result+LineEnding;
345
Result:=Result+Format('[%s] : %s (%d)',[SState,SMsg,NativeError]);
347
Until (Res=SQL_NO_DATA);
353
function TODBCObject.CreateHandle: SQLHandle;
356
Writeln(Classname,': Creating handle of type ',FHAndleType,' and parent ',ParentHandle);
358
CheckODBC(SQLAllocHandle(FHandleType,ParentHandle,FHandle),SErrEnvironmentHandle);
363
destructor TODBCObject.Destroy;
370
procedure TODBCObject.FreeHandle;
374
SQLFreeHandle(FHandleType,FHandle);
379
function TODBCObject.GetHandle: SQLHandle;
386
function TODBCObject.GetHandleAllocated: Boolean;
391
function TODBCObject.ParentHandle: SQLHandle;
393
Result:=SQL_NULL_HANDLE;
398
constructor TODBCEnvironment.Create(Aowner: TComponent);
400
FHandleType:=SQL_HANDLE_ENV;
404
function TODBCEnvironment.CreateHandle: SQLHandle;
406
Result:=Inherited CreateHandle;
407
ODBCbehaviour:=SQL_OV_ODBC3;
410
function TODBCEnvironment.GetDataSourceNames(List: Tstrings;
411
Types: TDSNTypes;Descriptions : Boolean): Integer;
415
DSNDesc: array[0..SQL_MAX_OPTION_STRING_LENGTH] of Char;
416
lenn,lend : SQLSmallInt;
422
dtSystem : Dir:=SQL_FETCH_FIRST_SYSTEM;
423
dtUser : Dir:=SQL_FETCH_FIRST_USER;
424
dtBoth : Dir:=SQL_FETCH_FIRST;
427
CheckODBC(SQLDatasources(Handle, Dir,
428
DSNName,SQL_MAX_OPTION_STRING_LENGTH, @lenn,
429
DSNDesc,SQL_MAX_OPTION_STRING_LENGTH, @lend),SErrGettingDataSources);
431
If Not Descriptions then
439
Until Not (SQLDataSources(Handle, SQL_FETCH_NEXT,
440
DSNName, SQL_MAX_OPTION_STRING_LENGTH, @lenn,
441
DSNDesc,SQL_MAX_OPTION_STRING_LENGTH, @lend) in ODBCSuccess);
445
function TODBCEnvironment.GetDriverNames(List : Tstrings): Integer;
448
DriverName: array[0..SQL_MAX_OPTION_STRING_LENGTH] of Char;
453
CheckODBC(SQLDrivers(Handle, SQL_FETCH_FIRST, DriverName,
454
SQL_MAX_OPTION_STRING_LENGTH, @len, Nil,0,Nil),SErrGettingDriverNames);
456
List.Add(DriverName);
457
Until Not (SQLDrivers(Handle, SQL_FETCH_NEXT, DriverName,
458
SQL_MAX_OPTION_STRING_LENGTH, @len, Nil,0,Nil) in ODBCSuccess);
462
function TODBCEnvironment.GetDriverOptions(Driver : String;Options: Tstrings): Integer;
466
DriverOptions: array[0..SQL_MAX_OPTION_STRING_LENGTH] of Char;
467
lenn,leno : SQLSmallInt;
473
CheckODBC(SQLDrivers(Handle, SQL_FETCH_FIRST, DriverName,
474
SQL_MAX_OPTION_STRING_LENGTH, @lenn, DriverOptions,
475
SQL_MAX_OPTION_STRING_LENGTH,@Leno),SErrGettingDriverOptions);
479
Found:=CompareText(Driver,DriverName)=0;
482
P:=@DriverOptions[0];
490
Until Not (SQLDrivers(Handle, SQL_FETCH_NEXT, DriverName,
491
SQL_MAX_OPTION_STRING_LENGTH, @lenn, DriverOptions,
492
SQL_MAX_OPTION_STRING_LENGTH,@Leno) in ODBCSuccess) or Found;
493
Result:=Options.Count;
496
function TODBCEnvironment.GetIntAttribute(const Attr: Integer): Integer;
498
CheckODBC(SQLSetEnvAttr(Handle,Attr,SQLPointer(@result),0),SErrSettingEnvAttribute);
501
function TODBCEnvironment.GetNullTerminate: Boolean;
503
Result:=(GetIntAttribute(SQL_ATTR_OUTPUT_NTS)=SQL_TRUE);
506
function TODBCEnvironment.GetStringAttribute(const Attr: Integer): String;
515
SetLength(Result,OldLen);
516
CheckODBC(SQLGetEnvAttr(Handle,Attr,SQLPointer(@result),OldLen,@Len),SErrGettingEnvAttribute);
518
SetLength(Result,Len);
521
procedure TODBCEnvironment.SetIntAttribute(const Attr, Value: Integer);
523
CheckODBC(SQLSetEnvAttr(Handle,Attr,SQLPointer(Value),0),SErrSettingEnvAttribute);
526
procedure TODBCEnvironment.SetNullTerminate(const Value: Boolean);
529
SetIntAttribute(SQL_ATTR_OUTPUT_NTS,SQL_TRUE)
531
SetIntAttribute(SQL_ATTR_OUTPUT_NTS,SQL_FALSE);
534
procedure TODBCEnvironment.SetODBCbehaviour(const Value: Integer);
536
If (Value<>FODBCBehaviour) then
538
If Not (Value in [SQL_OV_ODBC3,SQL_OV_ODBC2]) Then
539
ODBCErrorFmt(SErrInvalidBehaviour,[Value]);
540
SetIntAttribute(SQL_ATTR_ODBC_VERSION,Value);
541
FODBCBehaviour := Value;
545
procedure TODBCEnvironment.SetStringAttribute(const Attr: Integer;
548
CheckODBC(SQLSetEnvAttr(Handle,Attr,SQLPointer(Value),Length(Value)),SErrSettingEnvAttribute);
553
procedure TODBCConnection.CheckActive;
556
ODBCError(SErrNotConnected);
559
procedure TODBCConnection.CheckInActive;
562
ODBCError(SErrConnected);
565
procedure TODBCConnection.Connect;
569
If Assigned (FonBrowseConnection) then
571
else If (FDSN<>'') then
573
else if FDriverName<>'' then
576
ODBCError(SNeedDSNOrDriver);
581
Function ListToBuf(List : Tstrings; Buf : PChar; Sep : Char; MaxLen : Integer) : Boolean;
592
While Result and (I<List.Count) do
595
If I<List.Count-1 then
598
Result:=(Longint(P-Buf)+Len)<=MaxLen;
609
Function BufToList(Buf : PChar;MaxLen : Integer;List : Tstrings;Sep : Char) : Integer;
614
Totlen,Len : Integer;
621
While (P[0]<>#0) or (totlen<Maxlen) do
624
While Not (P[len] in [Sep,#0]) do
638
Procedure TODBCConnection.ConnectBrowsing;
641
Inlist,OutList : TStringList;
643
OutStr: Array[0..SQL_MAX_OPTION_STRING_LENGTH] of Char;
648
InList:=TStringList.Create;
649
OutList:=TstringList.Create;
652
InList.Add('DSN='+FDSN)
653
else If FDriverName<>'' then
655
Inlist.Add('DRIVER='+FDriverName);
656
For I:=0 to DriverParams.Count-1 do
657
Inlist.Add(DriverParams[i]);
660
ListToBuf(Inlist,Instr,';',SQL_MAX_OPTION_STRING_LENGTH);
661
Res:=SQLBrowseConnect(Handle,Instr,SQL_NTS,Outstr,SQL_MAX_OPTION_STRING_LENGTH,Olen);
662
If RES=SQL_NEED_DATA then
665
BufToList(OutStr,Olen,OutList,';');
666
FOnBrowseConnection(Self,InList,OutList);
668
Until (Res<>SQL_NEED_DATA);
669
CheckODBC(Res,SErrBrowseConnecting);
676
Procedure TODBCConnection.ConnectToDSN;
678
CheckODBC(SQLConnect(Handle,PSQLChar(FDSN),SQL_NTS,
679
PSQLChar(FUserName),SQL_NTS,
680
PSQLChar(FPassword),SQL_NTS),SErrDSNConnect);
684
Procedure TODBCConnection.ConnectToDriver;
688
OutStr : Array[0..SQL_MAX_OPTION_STRING_LENGTH] of Char;
690
InList : TStringList;
693
InList:=TStringList.Create;
695
Inlist.Assign(DriverParams);
696
Inlist.Insert(0,'DRIVER={'+DRIVERNAME+'}');
697
ListToBuf(Inlist,InStr,';',SQL_MAX_OPTION_STRING_LENGTH);
701
CheckODBC(SQLDriverConnect(Handle,FWindowHandle,
703
OutStr,SQL_MAX_OPTION_STRING_LENGTH,
704
Olen,FDriverCompletion),SErrDriverConnect);
707
constructor TODBCConnection.Create(Aowner: TComponent);
710
FHandleType:=SQL_HANDLE_DBC;
711
FDriverParams:=TStringList.Create;
712
FDriverCompletion:=SQL_DRIVER_NOPROMPT;
715
destructor TODBCConnection.Destroy;
721
procedure TODBCConnection.Disconnect;
725
CheckODBC(SQLDisconnect(Handle),SErrDisconnecting);
730
function TODBCConnection.GetDriverName: String;
735
function TODBCConnection.GetDriverParams: TStrings;
737
Result:=FDriverParams;
740
function TODBCConnection.GetEnvironment: TODBCEnvironMent;
742
If FEnvironment=Nil then
743
result:=DefaultEnvironment
745
Result:=FEnvironment;
748
procedure TODBCConnection.SetActive(const Value: Boolean);
756
procedure TODBCConnection.SetDriverName(const Value: String);
760
If CompareText(FDriverName,Value)<>0 then
767
procedure TODBCConnection.SetDriverParams(const Value: TStrings);
770
FDriverParams.Assign(Value);
773
procedure TODBCConnection.SetDSN(const Value: String);
779
procedure TODBCConnection.SetEnvironment(const Value: TODBCEnvironMent);
782
If (Value<>Environment) then // !! may be defaultenvironment...
784
If HandleAllocated then
791
function TODBCConnection.ParentHandle: SQLHandle;
793
Result:=Environment.Handle
797
DefEnv : Pointer = Nil;
799
Function DefaultEnvironment : TODBCEnvironment;
803
DefEnv:=TODBCEnvironment.Create(Nil);
804
Result:=TODBCEnvironment(DefEnv);
807
procedure TODBCConnection.GetTableNames(S: TStrings;
808
SystemTables: Boolean);
810
With TODBCTableList.Create(Self) do
812
GetTableNames(S,SystemTables);
818
procedure TODBCConnection.GetFieldNames(TableName: String; S: TStrings);
820
With TODBCFieldNamesList.Create(Self) do
822
GetFieldNames(TableName,S);
828
procedure TODBCConnection.GetPrimaryKeyFields(TableName: String;
831
With TODBCPrimaryKeyFieldsList.Create(Self) do
833
GetPrimaryKeyFields(TableName,S);
839
procedure TODBCConnection.GetProcedureNames(S: TStrings);
841
With TODBCProcedureList.Create(Self) do
849
procedure TODBCConnection.GetProcedureParams(ProcName: String;
850
ParamTypes: TODBCParamTypes; S: TStrings);
852
With TODBCProcedureParams.Create(Self) do
854
GetProcedureParams(ProcName,Paramtypes,S);
863
TODBCFieldBufRec = Record
864
T{ype} : SQlSmallint;
865
B{ufsize} : SQLInteger;
866
{Buftyp}e : SQLSmallint;
871
BufDescr : Array[1..BufDescrCount] of TODBCFieldBufRec =
872
{ Type Bufsize Buftype }
874
(T:SQL_CHAR ;b:-1 ;e: SQL_CHAR),
875
(T:SQL_NUMERIC ;b:sizeof(SQLDouble) ;e: SQL_DOUBLE),
876
(T:SQL_DECIMAL ;b:sizeof(SQLDouble) ;e: SQL_DOUBLE),
877
(T:SQL_INTEGER ;b:sizeof(SQLInteger) ;e: SQL_INTEGER),
878
(T:SQL_SMALLINT ;b:sizeof(SQLSmallInt) ;e: SQL_SMALLINT),
879
(T:SQL_FLOAT ;b:sizeof(SQLDOUBLE) ;e: SQL_DOUBLE),
880
(T:SQL_REAL ;b:sizeof(SQLDOUBLE) ;e: SQL_DOUBLE),
881
(T:SQL_DOUBLE ;b:Sizeof(SQLDOUBLE) ;e: SQL_DOUBLE),
882
(T:SQL_DATE ;b:Sizeof(SQL_DATE_STRUCT) ;e: SQL_DATE),
883
(T:SQL_TIME ;b:sizeof(SQL_TIME_STRUCT) ;e: SQL_TIME),
884
(T:SQL_TIMESTAMP ;b:sizeof(SQL_TIMESTAMP_STRUCT) ;e: SQL_TIMESTAMP),
885
(T:SQL_VARCHAR ;b:-1 ;e: SQL_CHAR),
886
(T:SQL_UNKNOWN_TYPE ;b:0 ;e: SQL_UNKNOWN_TYPE),
887
(T:SQL_LONGVARCHAR ;b:-1 ;e: SQL_CHAR),
888
(T:SQL_BINARY ;b:-2 ;e: SQL_BINARY),
889
(T:SQL_VARBINARY ;b:-2 ;e: SQL_BINARY),
890
(T:SQL_LONGVARBINARY ;b:-2 ;e: SQL_BINARY),
891
(T:SQL_BIGINT ;b:sizeOf(SQLDOUBLE) ;e: SQL_DOUBLE),
892
(T:SQL_TINYINT ;b:Sizeof(SQLSMALLINT) ;e: SQL_SMALLINT),
893
(T:SQL_BIT ;b:sizeof(SQL_CHAR) ;e: SQL_BIT),
894
(T:SQL_WCHAR ;b:-1 ;e: SQL_CHAR),
895
(T:SQL_WVARCHAR ;b:-1 ;e: SQL_CHAR),
896
(T:SQL_WLONGVARCHAR ;b:-1 ;e: SQL_CHAR),
897
(T:SQL_TYPE_DATE ;b:sizeof(SQL_DATE_STRUCT) ;e: SQL_TYPE_DATE),
898
(T:SQL_TYPE_TIME ;b:sizeof(SQL_TIME_STRUCT) ;e: SQL_TYPE_TIME),
899
(T:SQL_TYPE_TIMESTAMP;b:sizeof(SQL_TIMESTAMP_STRUCT) ;e: SQL_TYPE_TIMESTAMP)
905
Function GetColSizeBufType (Coltype: SQLSmallint;
906
Var BufSize : SQLInteger;
907
Var BufType : SQLSmallInt) : Boolean;
915
While (I<=BufDescrCount) and (BufDescr[i].t<>Coltype) do
917
Result:=(i<=BufDescrCount);
920
BufSize:=BufDescr[i].b;
921
BufType:=BufDescr[i].e;
926
procedure TODBCStatement.BindFields(RestrictList : TStrings);
930
CName : Array[0..SQL_NAME_LEN] of Char;
932
CDataType,CDecimals,CNullable,CNameLen: SQLSmallInt;
936
CheckODBC(SQLNumResultCols(Handle,Count),SErrColumnCount);
939
CheckODBC(SQLDescribeCol(Handle,i,CName,SQL_NAME_LEN,CNameLen,
940
CdataType,CSize, CDecimals,CNullable)
941
,SErrColDescription);
942
If Not Assigned(RestrictList) or (RestrictList.IndexOf(Cname)<>-1) then
943
With FFields.Add as TODBCField do
947
FDataType:=CDataType;
949
FDecimalDigits:=CDecimals;
950
FNullable:=(CNullable=SQL_TRUE);
951
GetColsizeBufType(FDataType,FBufSize,FBufType);
957
For I:=0 to Count-1 do
958
With FFields.Items[i] as TODBCField do
959
CheckODBC(SQLBindCol(Handle,FPosition,FBufType,GetData,FBufSize,FBuffer+FBuffOffset)
964
procedure TODBCStatement.ClearFields;
969
constructor TODBCStatement.Create(Aowner: TComponent);
971
FHandleType:=SQL_HANDLE_STMT;
973
If AOwner is TODBCConnection then
974
Connection:=TODBCConnection(Aowner);
975
FFields:=TODBCFieldList.Create(Self);
978
function TODBCStatement.ParentHandle: SQLHandle;
980
If (Connection=Nil) then
981
ODBCError(SErrNoConnectionForStatement);
982
Result:=Connection.Handle;
985
procedure TODBCStatement.SetConnection(const Value: TODBCConnection);
987
If Value<>FConnection then
989
If HandleAllocated then
991
FConnection := Value;
995
Function TODBCStatement.fetch : Boolean;
1001
Res:=SQLFetch(Handle);
1002
Result:=(Res=SQL_SUCCESS);
1003
If Not Result and (Res<>SQL_NO_DATA) then
1004
CheckODBC(Res,SErrFetchingData);
1006
If (Res=SQL_NO_DATA) then
1010
destructor TODBCStatement.Destroy;
1016
{ TODBCSQLStatement }
1018
procedure TODBCSQLStatement.GetFieldList(List : TStrings);
1022
CName : Array[0..SQL_NAME_LEN] of Char;
1023
CSize : SQLUINTEGER;
1024
CDataType,CDecimals,CNullable,CNameLen: SQLSmallInt;
1028
if Not (FState in [ssPrepared,ssBound,ssOpen]) then
1029
ODBCError(SErrNotPrepared);
1031
CheckODBC(SQLNumResultCols(Handle,Count),SErrColumnCount);
1032
For I:=1 to Count do
1034
CheckODBC(SQLDescribeCol(Handle,i,CName,SQL_NAME_LEN,CNameLen,
1035
CdataType,CSize, CDecimals,CNullable)
1036
,SErrColDescription);
1042
procedure TODBCSQLStatement.Unprepare;
1049
FreeStatement(SQL_CLOSE);
1052
FreeStatement(SQL_CLOSE);
1058
procedure TODBCSQLStatement.FreeStatement(Option : SQLUSMALLINT);
1061
SQLFreeStmt(Handle,SQL_CLOSE);
1064
procedure TODBCSQLStatement.Close;
1066
if FState<>ssInactive then
1069
FreeStatement(SQL_CLOSE);
1074
constructor TODBCSQLStatement.Create(Aowner: TComponent);
1077
FSQL:=TStringList.Create;
1080
destructor TODBCSQLStatement.Destroy;
1082
if FState=ssOpen then
1084
else If FState<>ssInactive then
1090
procedure TODBCSQLStatement.ExecSQL;
1093
ssPrepared,ssBound : ExecutePrepared;
1094
ssInactive : ExecuteDirect;
1096
Raise Exception.Create(SErrStatementActive)
1100
procedure TODBCSQLStatement.ExecuteDirect;
1106
if FState<>ssInactive then
1107
ODBCError(SErrStatementActive);
1109
CheckODBC(SQLExecDirect(Handle,PChar(S),SQL_NTS),SErrExecuting);
1112
procedure TODBCSQLStatement.ExecutePrepared;
1114
if Not (FState in [ssPrepared,ssBound]) then
1115
ODBCError(SErrNotPrepared);
1116
CheckODBC(SQLExecute(Handle),SErrExecutingPrepared);
1119
function TODBCSQLStatement.GetActive: Boolean;
1121
Result:=(FState=ssOpen);
1124
procedure TODBCSQLStatement.Open;
1126
if (FState<>ssOpen) then
1128
Writeln('Preparing');
1129
If FState=ssInactive then
1131
Writeln('Bind fields');
1132
if FState=ssPrepared then
1134
Writeln('Executing');
1136
Writeln('Fetching');
1137
If FState=ssBound then
1144
procedure TODBCSQLStatement.Prepare;
1150
If FState<>ssInactive then
1151
ODBCError(SErrNotInactive);
1152
If (FSQL.Count=0) then
1153
ODBCError(SErrNoSQLStatement);
1155
CheckODBC(SQLPrepare(Handle,PChar(S),SQL_NTS),SErrPreparing);
1159
procedure TODBCSQLStatement.SetActive(const Value: Boolean);
1167
procedure TODBCSQLStatement.SetSQL(const Value: TStrings);
1174
procedure TODBCSQLStatement.BindFields(RestrictList: TStrings);
1181
procedure TODBCStatement.AllocBuffers;
1184
I,TotalSize,AddSize : Integer;
1188
For i:=0 to FFields.Count-1 do
1189
With (FFields.Items[i] as TODBCField) do
1192
If FBufSize=-2 then // Blob.
1194
else if FBufSize=-1 then
1195
AddSize:=FSize+1; // some Char variant.
1196
// Store offset temporarily in FData
1197
FBuffOffset:=TotalSize;
1198
Inc(TotalSize,AddSize+SizeOf(SQLinteger));
1200
FBuffer:=GetMem(TotalSize);
1202
For i:=0 to FFields.Count-1 do
1203
With (FFields.Items[i] as TODBCField) do
1204
FBuffer:=Self.FBuffer;
1209
procedure TODBCTableList.GetTableNames(S: TStrings; SystemTables : Boolean);
1213
TType: array[0..SQL_NAME_LEN+1] of char;
1219
Res:=CheckODBC(SQLTables(handle, nil,0,nil,0,nil,0,nil,0),SErrGettingTableNames);
1220
if Res=SQL_SUCCESS then
1222
// Must bind by colno, because names changed between ODBC 2.0 and 3.0 !!
1223
SQLBindCol(handle,3,SQL_CHAR,@TName,SQL_NAME_LEN,@NL);
1224
SQLBindCol(handle,4,SQL_CHAR,@TType,SQL_NAME_LEN,@TL);
1226
if SystemTables or (CompareText(TType,'SYSTEM TABLE')<>0) then
1231
{ TODBCFieldNamesList }
1233
procedure TODBCFieldNamesList.GetFieldNames(TableName: String;
1237
FName : array[0..SQL_NAME_LEN+1] of char;
1243
Res:=CheckODBC(SQLColumns(handle, nil, 0, nil, 0, pchar(TableName), SQL_NTS, nil, 0),SErrFieldNames);
1244
if Res=SQL_SUCCESS then
1246
SQLBindCol(handle, 4, SQL_CHAR, @FNAme, SQL_NAME_LEN, @NF);
1252
{ TODBCPrimaryKeyFieldsList }
1254
procedure TODBCPrimaryKeyFieldsList.GetPrimaryKeyFields(TableName: String;
1257
FName : array[0..SQL_NAME_LEN+1] of char;
1263
Res:=CheckODBC(SQLPrimaryKeys(handle, nil, 0, nil, 0, pchar(TableName), SQL_NTS),SErrPrimaryKeys);
1264
if Res=SQL_SUCCESS then
1266
SQLBindCol(handle, 4, SQL_CHAR, @FNAme, SQL_NAME_LEN, @NF);
1273
{ TODBCProcedureList }
1275
procedure TODBCProcedureList.GetProcedureList(S: TStrings);
1278
PName : array[0..SQL_NAME_LEN+1] of char;
1284
Res:=CheckODBC(SQLProcedures(handle, nil, 0, nil, 0, Nil, 0),SErrProcedureNames);
1285
if Res=SQL_SUCCESS then
1287
SQLBindCol(handle, 3, SQL_CHAR, @PNAme, SQL_NAME_LEN, @NP);
1294
{ TODBCProcedureParams }
1296
procedure TODBCProcedureParams.GetProcedureParams(ProcName: String;
1297
ParamTypes: TODBCParamTypes; S: TStrings);
1300
PName : array[0..SQL_NAME_LEN+1] of char;
1302
Ptype : SQLSmallInt;
1307
Res:=CheckODBC(SQLProcedureColumns(handle, nil, 0, nil, 0, PChar(ProcName),SQL_NTS,Nil, 0),SErrProcedureNames);
1308
if Res=SQL_SUCCESS then
1310
SQLBindCol(handle, 4, SQL_CHAR, @PName, SQL_NAME_LEN, @NP);
1311
SQLBindCol(handle, 5, SQL_SMALLINT, @PType, SizeOf(SQLSmallInt), @NT);
1314
If TODBCParamType(PType) in ParamTypes then
1322
constructor TODBCFieldList.Create(Statement: TODBCStatement);
1324
FStatement:=Statement;
1325
Inherited Create(TODBCField);
1330
function TODBCField.GetAsString: String;
1336
SQL_Smallint : Result:=IntToStr(PSQLSmallInt(Data)^);
1337
SQL_Integer : Result:=IntToStr(PSQLINTEGER(Data)^);
1338
SQL_BIT : Result:=IntToStr(PByte(Data)^);
1339
SQL_CHAR : Result:=StrPas(Data);
1340
SQL_DOUBLE : Result:=FloatToStr(GetAsDouble);
1341
SQL_DATE : result:=DateToStr(AsDateTime);
1342
SQL_TIME : Result:=TimeToStr(AsDateTime);
1343
SQL_TIMESTAMP : result:=datetimeToStr(AsDateTime);
1344
SQL_TYPE_DATE : result:=dateToStr(AsDateTime);
1345
SQL_TYPE_TIMESTAMP : result:=datetimeToStr(AsDateTime);
1346
SQL_TYPE_TIME : Result:=TimeToStr(AsDateTime);
1348
ODBCError(SErrInvalidConversion)
1352
function TODBCField.GetData : Pchar;
1355
Result:=FBuffer+FBuffOffset+SizeOf(SQLinteger);
1358
function TODBCField.GetIsNull : boolean;
1361
Result:=PSQLinteger(FBuffer+FBuffOffset)^=SQL_NULL_DATA;
1364
Function TODBCField.GetAsInteger : Integer;
1371
SQL_Smallint : Result:=PSQLSmallInt(Data)^;
1372
SQL_Integer : Result:=PSQLINTEGER(Data)^;
1373
SQL_BIT : Result:=PByte(Data)^;
1374
SQL_CHAR : Result:=StrToInt(GetAsString);
1375
SQL_DOUBLE : Result:=Round(GetAsDouble);
1381
SQL_TYPE_TIME : Result:=Round(AsDateTime);
1383
ODBCError(SErrInvalidConversion)
1387
Function TODBCField.GetAsBoolean : Boolean;
1394
SQL_Smallint : Result:=PSQLSmallInt(Data)^=0;
1395
SQL_Integer : Result:=PSQLINTEGER(Data)^=0;
1396
SQL_BIT : Result:=PBYTE(Data)^=0;
1397
SQL_CHAR : Result:=(StrToInt(GetAsString)=0);
1398
SQL_DOUBLE : Result:=Round(GetAsDouble)=0;
1404
SQL_TYPE_TIME : Result:=Round(AsDateTime)=0;
1406
ODBCError(SErrInvalidConversion)
1410
Function TODBCField.GetAsDouble : Double;
1417
SQL_Smallint : Result:=PSQLSmallInt(Data)^;
1418
SQL_Integer : Result:=PSQLINTEGER(Data)^;
1419
SQL_BIT : Result:=PBYTE(Data)^;
1420
SQL_CHAR : Result:=StrToFloat(GetAsString);
1421
SQL_DOUBLE : Result:=PSQLDOUBLE(GetData)^;
1427
SQL_TYPE_TIME : Result:=AsDateTime;
1429
ODBCError(SErrInvalidConversion)
1434
function DateStructToDateTime( b:PSQL_DATE_STRUCT):TDateTime;
1435
function DateTimeToDateStruct( b:TDateTime):SQL_DATE_STRUCT;
1436
procedure DateTime2TimeStampStruct( var Value:SQL_TIMESTAMP_STRUCT; b:TDateTime);
1438
Function TODBCField.GetAsDateTime : TDateTime;
1445
SQL_Smallint : Result:=PSQLSmallInt(Data)^;
1446
SQL_Integer : Result:=PSQLINTEGER(Data)^;
1447
SQL_BIT : Result:=PBYTE(Data)^;
1448
SQL_CHAR : Result:=StrToInt(GetAsString);
1449
SQL_DOUBLE : Result:=PSQLDOUBLE(GetData)^;
1450
SQL_DATE : Result:=DateStructToDateTime(PSQL_DATE_STRUCT(Data));
1451
SQL_TIME : Result:=TimeStructToDateTime(PSQL_TIME_STRUCT(Data));
1452
SQL_TIMESTAMP : Result:=TimeStampStructToDateTime(PSQL_TIMESTAMP_STRUCT(Data));
1453
SQL_TYPE_DATE : Result:=DateStructToDateTime(PSQL_DATE_STRUCT(Data));
1454
SQL_TYPE_TIMESTAMP : Result:=TimeStampStructToDateTime(PSQL_TIMESTAMP_STRUCT(Data));
1455
SQL_TYPE_TIME : Result:=TimeStructToDateTime(PSQL_TIME_STRUCT(Data));
1457
ODBCError(SErrInvalidConversion)
1462
If Assigned(DefEnv) then
1463
TODBCEnvironment(DefEnv).Free;