loe
asked on
display table properties via SQL
I need to modify the structure of several tables in an Access database using SQL and ColdFusion. The database is on a live server so I can't open it directly, but I want to verify the changes I made to be sure they are correct. How can I display a list of fields, data types, indexes, keys, etc., using ColdFusion and/or SQL statements?
https://www.experts-exchange.com/questions/20076384/Control-Access-Tables-remotely.html
ASKER
I know how to modify the table. How can I display the table structure when I'm done?
kudos to CFHub for this one....
http://cfhub.com/advanced/ado/index.cfm
<cfscript>
//------------------------ ---------- ---------- ---
// Microsoft ADO
//
// Copyright (c) 1996-1998 Microsoft Corporation.
//
//
//
// ADO constants include file for JavaScript
//(Modified for CFSCRIPT by CFHub.com)
//------------------------ ---------- ---------- ---
//---- CursorTypeEnum Values ----
adOpenForwardOnly ="0";
adOpenKeyset ="1";
adOpenDynamic ="2";
adOpenStatic ="3";
//---- CursorOptionEnum Values ----
adHoldRecords ="0x00000100";
adMovePrevious ="0x00000200";
adAddNew ="0x01000400";
adDelete ="0x01000800";
adUpdate ="0x01008000";
adBookmark ="0x00002000";
adApproxPosition ="0x00004000";
adUpdateBatch ="0x00010000";
adResync ="0x00020000";
adNotify ="0x00040000";
adFind ="0x00080000";
adSeek ="0x00400000";
adIndex ="0x00800000";
//---- LockTypeEnum Values ----
adLockReadOnly ="1";
adLockPessimistic ="2";
adLockOptimistic ="3";
adLockBatchOptimistic ="4";
//---- ExecuteOptionEnum Values ----
adAsyncExecute ="0x00000010";
adAsyncFetch ="0x00000020";
adAsyncFetchNonBlocking ="0x00000040";
adExecuteNoRecords ="0x00000080";
//---- ConnectOptionEnum Values ----
adAsyncConnect ="0x00000010";
//---- ObjectStateEnum Values ----
adStateClosed ="0x00000000";
adStateOpen ="0x00000001";
adStateConnecting ="0x00000002";
adStateExecuting ="0x00000004";
adStateFetching ="0x00000008";
//---- CursorLocationEnum Values ----
adUseServer ="2";
adUseClient ="3";
//---- DataTypeEnum Values ----
adEmpty ="0";
adTinyInt ="16";
adSmallInt ="2";
adInteger ="3";
adBigInt ="20";
adUnsignedTinyInt ="17";
adUnsignedSmallInt ="18";
adUnsignedInt ="19";
adUnsignedBigInt ="21";
adSingle ="4";
adDouble ="5";
adCurrency ="6";
adDecimal ="14";
adNumeric ="131";
adBoolean ="11";
adError ="10";
adUserDefined ="132";
adVariant ="12";
adIDispatch ="9";
adIUnknown ="13";
adGUID ="72";
adDate ="7";
adDBDate ="133";
adDBTime ="134";
adDBTimeStamp ="135";
adBSTR ="8";
adChar ="129";
adVarChar ="200";
adLongVarChar ="201";
adWChar ="130";
adVarWChar ="202";
adLongVarWChar ="203";
adBinary ="128";
adVarBinary ="204";
adLongVarBinary ="205";
adChapter ="136";
adFileTime ="64";
adPropVariant ="138";
adVarNumeric ="139";
adArray ="0x2000";
//---- DataTypeEnum "SimpleType" Array ----
SimpleType = ArrayNew(1);
SimpleType[16] = "Numeric";
SimpleType[2] = "Numeric";
SimpleType[3] = "Numeric";
SimpleType[20] = "Numeric";
SimpleType[17] = "Numeric";
SimpleType[18] = "Numeric";
SimpleType[19] = "Numeric";
SimpleType[21] = "Numeric";
SimpleType[4] = "Numeric";
SimpleType[5] = "Numeric";
SimpleType[6] = "Numeric";
SimpleType[14] = "Numeric";
SimpleType[131] = "Numeric";
SimpleType[11] = "Boolean";
SimpleType[10] = "Other";
SimpleType[132] = "Other";
SimpleType[12] = "Other";
SimpleType[9] = "Other";
SimpleType[13] = "Other";
SimpleType[72] = "Other";
SimpleType[7] = "Date";
SimpleType[133] = "Date";
SimpleType[134] = "Date";
SimpleType[135] = "Date";
SimpleType[8] = "Text";
SimpleType[129] = "Text";
SimpleType[200] = "Text";
SimpleType[201] = "Text";
SimpleType[130] = "Text";
SimpleType[202] = "Text";
SimpleType[203] = "Text";
SimpleType[128] = "Other";
SimpleType[204] = "Other";
SimpleType[205] = "Other";
SimpleType[136] = "Other";
//---- DataTypeEnum "DataBaseType" Array ----
DataBaseType = ArrayNew(1);
DataBaseType[16] = "TinyInt";
DataBaseType[2] = "SmallInt";
DataBaseType[3] = "Integer";
DataBaseType[20] = "BigInt";
DataBaseType[17] = "UnsignedTinyInt";
DataBaseType[18] = "UnsignedSmallInt";
DataBaseType[19] = "Int";
DataBaseType[21] = "UnsignedBigInt";
DataBaseType[4] = "Single";
DataBaseType[5] = "Double";
DataBaseType[6] = "Currency";
DataBaseType[14] = "Decimal";
DataBaseType[131] = "Numeric";
DataBaseType[11] = "Boolean";
DataBaseType[10] = "Error";
DataBaseType[132] = "UserDefined";
DataBaseType[12] = "Variant";
DataBaseType[9] = "IDispatch";
DataBaseType[13] = "IUnknown";
DataBaseType[72] = "GUID";
DataBaseType[7] = "Date";
DataBaseType[133] = "DBDate";
DataBaseType[134] = "DBTime";
DataBaseType[135] = "DBTimestamp";
DataBaseType[8] = "BStr";
DataBaseType[129] = "Char";
DataBaseType[200] = "VarChar";
DataBaseType[201] = "LongVarChar";
DataBaseType[130] = "WChar";
DataBaseType[202] = "VarWChar";
DataBaseType[203] = "LongVarWChar";
DataBaseType[128] = "Binary";
DataBaseType[204] = "VarBinary";
DataBaseType[205] = "LongVarBinary";
DataBaseType[136] = "Chapter";
//---- FieldAttributeEnum Values ----
adFldMayDefer ="0x00000002";
adFldUpdatable ="0x00000004";
adFldUnknownUpdatable ="0x00000008";
adFldFixed ="0x00000010";
adFldIsNullable ="0x00000020";
adFldMayBeNull ="0x00000040";
adFldLong ="0x00000080";
adFldRowID ="0x00000100";
adFldRowVersion ="0x00000200";
adFldCacheDeferred ="0x00001000";
adFldIsChapter ="0x00002000";
adFldNegativeScale ="0x00004000";
adFldKeyColumn ="0x00008000";
adFldIsRowURL ="0x00010000";
adFldIsDefaultStream ="0x00020000";
adFldIsCollection ="0x00040000";
//---- EditModeEnum Values ----
adEditNone ="0x0000";
adEditInProgress ="0x0001";
adEditAdd ="0x0002";
adEditDelete ="0x0004";
//---- RecordStatusEnum Values ----
adRecOK ="0x0000000";
adRecNew ="0x0000001";
adRecModified ="0x0000002";
adRecDeleted ="0x0000004";
adRecUnmodified ="0x0000008";
adRecInvalid ="0x0000010";
adRecMultipleChanges ="0x0000040";
adRecPendingChanges ="0x0000080";
adRecCanceled ="0x0000100";
adRecCantRelease ="0x0000400";
adRecConcurrencyViolation ="0x0000800";
adRecIntegrityViolation ="0x0001000";
adRecMaxChangesExceeded ="0x0002000";
adRecObjectOpen ="0x0004000";
adRecOutOfMemory ="0x0008000";
adRecPermissionDenied ="0x0010000";
adRecSchemaViolation ="0x0020000";
adRecDBDeleted ="0x0040000";
//---- GetRowsOptionEnum Values ----
adGetRowsRest ="-1";
//---- PositionEnum Values ----
adPosUnknown ="-1";
adPosBOF ="-2";
adPosEOF ="-3";
//---- BookmarkEnum Values ----
adBookmarkCurrent ="0";
adBookmarkFirst ="1";
adBookmarkLast ="2";
//---- MarshalOptionsEnum Values ----
adMarshalAll ="0";
adMarshalModifiedOnly ="1";
//---- AffectEnum Values ----
adAffectCurrent ="1";
adAffectGroup ="2";
adAffectAllChapters ="4";
//---- ResyncEnum Values ----
adResyncUnderlyingValues ="1";
adResyncAllValues ="2";
//---- CompareEnum Values ----
adCompareLessThan ="0";
adCompareEqual ="1";
adCompareGreaterThan ="2";
adCompareNotEqual ="3";
adCompareNotComparable ="4";
//---- FilterGroupEnum Values ----
adFilterNone ="0";
adFilterPendingRecords ="1";
adFilterAffectedRecords ="2";
adFilterFetchedRecords ="3";
adFilterConflictingRecords ="5";
//---- SearchDirectionEnum Values ----
adSearchForward ="1";
adSearchBackward ="-1";
//---- PersistFormatEnum Values ----
adPersistADTG ="0";
adPersistXML ="1";
//---- StringFormatEnum Values ----
adClipString ="2";
//---- ConnectPromptEnum Values ----
adPromptAlways ="1";
adPromptComplete ="2";
adPromptCompleteRequired ="3";
adPromptNever ="4";
//---- ConnectModeEnum Values ----
adModeUnknown ="0";
adModeRead ="1";
adModeWrite ="2";
adModeReadWrite ="3";
adModeShareDenyRead ="4";
adModeShareDenyWrite ="8";
adModeShareExclusive ="0xc";
adModeShareDenyNone ="0x10";
adModeRecursive ="0x400000";
//---- RecordCreateOptionsEnum Values ----
adCreateCollection ="0x00002000";
adCreateStructDoc ="0x80000000";
adCreateNonCollection ="0x00000000";
adOpenIfExists ="0x02000000";
adCreateOverwrite ="0x04000000";
adFailIfNotExists ="-1";
//---- RecordOpenOptionsEnum Values ----
adOpenRecordUnspecified ="-1";
adOpenSource ="0x00800000";
adOpenAsync ="0x00001000";
adDelayFetchStream ="0x00004000";
adDelayFetchFields ="0x00008000";
//---- IsolationLevelEnum Values ----
adXactUnspecified ="0xffffffff";
adXactChaos ="0x00000010";
adXactReadUncommitted ="0x00000100";
adXactBrowse ="0x00000100";
adXactCursorStability ="0x00001000";
adXactReadCommitted ="0x00001000";
adXactRepeatableRead ="0x00010000";
adXactSerializable ="0x00100000";
adXactIsolated ="0x00100000";
//---- XactAttributeEnum Values ----
adXactCommitRetaining ="0x00020000";
adXactAbortRetaining ="0x00040000";
//---- PropertyAttributesEnum Values ----
adPropNotSupported ="0x0000";
adPropRequired ="0x0001";
adPropOptional ="0x0002";
adPropRead ="0x0200";
adPropWrite ="0x0400";
//---- ErrorValueEnum Values ----
adErrProviderFailed ="0xbb8";
adErrInvalidArgument ="0xbb9";
adErrOpeningFile ="0xbba";
adErrReadFile ="0xbbb";
adErrWriteFile ="0xbbc";
adErrNoCurrentRecord ="0xbcd";
adErrIllegalOperation ="0xc93";
adErrCantChangeProvider ="0xc94";
adErrInTransaction ="0xcae";
adErrFeatureNotAvailable ="0xcb3";
adErrItemNotFound ="0xcc1";
adErrObjectInCollection ="0xd27";
adErrObjectNotSet ="0xd5c";
adErrDataConversion ="0xd5d";
adErrObjectClosed ="0xe78";
adErrObjectOpen ="0xe79";
adErrProviderNotFound ="0xe7a";
adErrBoundToCommand ="0xe7b";
adErrInvalidParamInfo ="0xe7c";
adErrInvalidConnection ="0xe7d";
adErrNotReentrant ="0xe7e";
adErrStillExecuting ="0xe7f";
adErrOperationCancelled ="0xe80";
adErrStillConnecting ="0xe81";
adErrInvalidTransaction ="0xe82";
adErrUnsafeOperation ="0xe84";
adwrnSecurityDialog ="0xe85";
adwrnSecurityDialogHeader ="0xe86";
adErrIntegrityViolation ="0xe87";
adErrPermissionDenied ="0xe88";
adErrDataOverflow ="0xe89";
adErrSchemaViolation ="0xe8a";
adErrSignMismatch ="0xe8b";
adErrCantConvertvalue ="0xe8c";
adErrCantCreate ="0xe8d";
adErrColumnNotOnThisRow ="0xe8e";
adErrURLIntegrViolSetColum ns ="0xe8f";
adErrURLDoesNotExist ="0xe8f";
adErrTreePermissionDenied ="0xe90";
adErrInvalidURL ="0xe91";
adErrResourceLocked ="0xe92";
adErrResourceExists ="0xe93";
adErrCannotComplete ="0xe94";
adErrVolumeNotFound ="0xe95";
adErrOutOfSpace ="0xe96";
adErrResourceOutOfScope ="0xe97";
adErrUnavailable ="0xe98";
adErrURLNamedRowDoesNotExi st ="0xe99";
adErrDelResOutOfScope ="0xe9a";
adErrPropInvalidColumn ="0xe9b";
adErrPropInvalidOption ="0xe9c";
adErrPropInvalidValue ="0xe9d";
adErrPropConflicting ="0xe9e";
adErrPropNotAllSettable ="0xe9f";
adErrPropNotSet ="0xea0";
adErrPropNotSettable ="0xea1";
adErrPropNotSupported ="0xea2";
adErrCatalogNotSet ="0xea3";
adErrCantChangeConnection ="0xea4";
adErrFieldsUpdateFailed ="0xea5";
adErrDenyNotSupported ="0xea6";
adErrDenyTypeNotSupported ="0xea7";
//---- ParameterAttributesEnum Values ----
adParamSigned ="0x0010";
adParamNullable ="0x0040";
adParamLong ="0x0080";
//---- ParameterDirectionEnum Values ----
adParamUnknown ="0x0000";
adParamInput ="0x0001";
adParamOutput ="0x0002";
adParamInputOutput ="0x0003";
adParamReturnValue ="0x0004";
//---- CommandTypeEnum Values ----
adCmdUnknown ="0x0008";
adCmdText ="0x0001";
adCmdTable ="0x0002";
adCmdStoredProc ="0x0004";
adCmdFile ="0x0100";
adCmdTableDirect ="0x0200";
//---- EventStatusEnum Values ----
adStatusOK ="0x0000001";
adStatusErrorsOccurred ="0x0000002";
adStatusCantDeny ="0x0000003";
adStatusCancel ="0x0000004";
adStatusUnwantedEvent ="0x0000005";
//---- EventReasonEnum Values ----
adRsnAddNew ="1";
adRsnDelete ="2";
adRsnUpdate ="3";
adRsnUndoUpdate ="4";
adRsnUndoAddNew ="5";
adRsnUndoDelete ="6";
adRsnRequery ="7";
adRsnResynch ="8";
adRsnClose ="9";
adRsnMove ="10";
adRsnFirstChange ="11";
adRsnMoveFirst ="12";
adRsnMoveNext ="13";
adRsnMovePrevious ="14";
adRsnMoveLast ="15";
//---- SchemaEnum Values ----
adSchemaProviderSpecific ="-1";
adSchemaAsserts ="0";
adSchemaCatalogs ="1";
adSchemaCharacterSets ="2";
adSchemaCollations ="3";
adSchemaColumns ="4";
adSchemaCheckConstraints ="5";
adSchemaConstraintColumnUs age ="6";
adSchemaConstraintTableUsa ge ="7";
adSchemaKeyColumnUsage ="8";
adSchemaReferentialConstra ints ="9";
adSchemaTableConstraints ="10";
adSchemaColumnsDomainUsage ="11";
adSchemaIndexes ="12";
adSchemaColumnPrivileges ="13";
adSchemaTablePrivileges ="14";
adSchemaUsagePrivileges ="15";
adSchemaProcedures ="16";
adSchemaSchemata ="17";
adSchemaSQLLanguages ="18";
adSchemaStatistics ="19";
adSchemaTables ="20";
adSchemaTranslations ="21";
adSchemaProviderTypes ="22";
adSchemaViews ="23";
adSchemaViewColumnUsage ="24";
adSchemaViewTableUsage ="25";
adSchemaProcedureParameter s ="26";
adSchemaForeignKeys ="27";
adSchemaPrimaryKeys ="28";
adSchemaProcedureColumns ="29";
adSchemaDBInfoKeywords ="30";
adSchemaDBInfoLiterals ="31";
adSchemaCubes ="32";
adSchemaDimensions ="33";
adSchemaHierarchies ="34";
adSchemaLevels ="35";
adSchemaMeasures ="36";
adSchemaProperties ="37";
adSchemaMembers ="38";
adSchemaTrustees ="39";
//---- FieldStatusEnum Values ----
adFieldOK ="0";
adFieldCantConvertValue ="2";
adFieldIsNull ="3";
adFieldTruncated ="4";
adFieldSignMismatch ="5";
adFieldDataOverflow ="6";
adFieldCantCreate ="7";
adFieldUnavailable ="8";
adFieldPermissionDenied ="9";
adFieldIntegrityViolation ="10";
adFieldSchemaViolation ="11";
adFieldBadStatus ="12";
adFieldDefault ="13";
adFieldIgnore ="15";
adFieldDoesNotExist ="16";
adFieldInvalidURL ="17";
adFieldResourceLocked ="18";
adFieldResourceExists ="19";
adFieldCannotComplete ="20";
adFieldVolumeNotFound ="21";
adFieldOutOfSpace ="22";
adFieldCannotDeleteSource ="23";
adFieldReadOnly ="24";
adFieldResourceOutOfScope ="25";
adFieldAlreadyExists ="26";
adFieldPendingInsert ="0x10000";
adFieldPendingDelete ="0x20000";
adFieldPendingChange ="0x40000";
adFieldPendingUnknown ="0x80000";
adFieldPendingUnknownDelet e ="0x100000";
//---- SeekEnum Values ----
adSeekFirstEQ ="0x1";
adSeekLastEQ ="0x2";
adSeekAfterEQ ="0x4";
adSeekAfter ="0x8";
adSeekBeforeEQ ="0x10";
adSeekBefore ="0x20";
//---- ADCPROP_UPDATECRITERIA_ENU M Values ----
adCriteriaKey ="0";
adCriteriaAllCols ="1";
adCriteriaUpdCols ="2";
adCriteriaTimeStamp ="3";
//---- ADCPROP_ASYNCTHREADPRIORIT Y_ENUM Values ----
adPriorityLowest ="1";
adPriorityBelowNormal ="2";
adPriorityNormal ="3";
adPriorityAboveNormal ="4";
adPriorityHighest ="5";
//---- ADCPROP_AUTORECALC_ENUM Values ----
adRecalcUpFront ="0";
adRecalcAlways ="1";
//---- ADCPROP_UPDATERESYNC_ENUM Values ----
//---- ADCPROP_UPDATERESYNC_ENUM Values ----
//---- MoveRecordOptionsEnum Values ----
adMoveUnspecified ="-1";
adMoveOverWrite ="1";
adMoveDontUpdateLinks ="2";
adMoveAllowEmulation ="4";
//---- CopyRecordOptionsEnum Values ----
adCopyUnspecified ="-1";
adCopyOverWrite ="1";
adCopyAllowEmulation ="4";
adCopyNonRecursive ="2";
//---- StreamTypeEnum Values ----
adTypeBinary ="1";
adTypeText ="2";
//---- LineSeparatorEnum Values ----
adLF ="10";
adCR ="13";
adCRLF ="-1";
//---- StreamOpenOptionsEnum Values ----
adOpenStreamUnspecified ="-1";
adOpenStreamAsync ="1";
adOpenStreamFromRecord ="4";
//---- StreamWriteEnum Values ----
adWriteChar ="0";
adWriteLine ="1";
//---- SaveOptionsEnum Values ----
adSaveCreateNotExist ="1";
adSaveCreateOverWrite ="2";
//---- FieldEnum Values ----
adDefaultStream ="-1";
adRecordURL ="-2";
//---- StreamReadEnum Values ----
adReadAll ="-1";
adReadLine ="-2";
//---- RecordTypeEnum Values ----
adSimpleRecord ="0";
adCollectionRecord ="1";
adStructDoc ="2";
</cfscript>
<cfobject type="COM"
name="ConnectionObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="D:\Inetpub\w wwroot\EE\ ee.mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
ConnectionObject.Open("#So urce#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=ConnectionObje ct.OpenSch ema(adSche maColumns) ;
AllRecords=MyFieldList.Fie lds;
/*These prperties are available
through the OpenSchema method.*/
ColumnName=AllRecords.Item ("COLUMN_N AME");
DataType=AllRecords.Item(" DATA_TYPE" );
TableName=AllRecords.Item( "TABLE_NAM E");
while(NOT MyFieldList.EOF){
WriteOutput(TableName.valu e);
WriteOutput(" <br> ");
WriteOutput(ColumnName.val ue);
WriteOutput(" <br> ");
WriteOutput(DataBaseType[D ataType.va lue]);
WriteOutput(" <br> ");
WriteOutput(SimpleType[Dat aType.valu e]);
WriteOutput(" <hr> ");
MyFieldList.MoveNext();
}
</cfscript>
<!--- Close Object--->
<cfset ConnectionObject.Close()>
http://cfhub.com/advanced/ado/index.cfm
<cfscript>
//------------------------
// Microsoft ADO
//
// Copyright (c) 1996-1998 Microsoft Corporation.
//
//
//
// ADO constants include file for JavaScript
//(Modified for CFSCRIPT by CFHub.com)
//------------------------
//---- CursorTypeEnum Values ----
adOpenForwardOnly ="0";
adOpenKeyset ="1";
adOpenDynamic ="2";
adOpenStatic ="3";
//---- CursorOptionEnum Values ----
adHoldRecords ="0x00000100";
adMovePrevious ="0x00000200";
adAddNew ="0x01000400";
adDelete ="0x01000800";
adUpdate ="0x01008000";
adBookmark ="0x00002000";
adApproxPosition ="0x00004000";
adUpdateBatch ="0x00010000";
adResync ="0x00020000";
adNotify ="0x00040000";
adFind ="0x00080000";
adSeek ="0x00400000";
adIndex ="0x00800000";
//---- LockTypeEnum Values ----
adLockReadOnly ="1";
adLockPessimistic ="2";
adLockOptimistic ="3";
adLockBatchOptimistic ="4";
//---- ExecuteOptionEnum Values ----
adAsyncExecute ="0x00000010";
adAsyncFetch ="0x00000020";
adAsyncFetchNonBlocking ="0x00000040";
adExecuteNoRecords ="0x00000080";
//---- ConnectOptionEnum Values ----
adAsyncConnect ="0x00000010";
//---- ObjectStateEnum Values ----
adStateClosed ="0x00000000";
adStateOpen ="0x00000001";
adStateConnecting ="0x00000002";
adStateExecuting ="0x00000004";
adStateFetching ="0x00000008";
//---- CursorLocationEnum Values ----
adUseServer ="2";
adUseClient ="3";
//---- DataTypeEnum Values ----
adEmpty ="0";
adTinyInt ="16";
adSmallInt ="2";
adInteger ="3";
adBigInt ="20";
adUnsignedTinyInt ="17";
adUnsignedSmallInt ="18";
adUnsignedInt ="19";
adUnsignedBigInt ="21";
adSingle ="4";
adDouble ="5";
adCurrency ="6";
adDecimal ="14";
adNumeric ="131";
adBoolean ="11";
adError ="10";
adUserDefined ="132";
adVariant ="12";
adIDispatch ="9";
adIUnknown ="13";
adGUID ="72";
adDate ="7";
adDBDate ="133";
adDBTime ="134";
adDBTimeStamp ="135";
adBSTR ="8";
adChar ="129";
adVarChar ="200";
adLongVarChar ="201";
adWChar ="130";
adVarWChar ="202";
adLongVarWChar ="203";
adBinary ="128";
adVarBinary ="204";
adLongVarBinary ="205";
adChapter ="136";
adFileTime ="64";
adPropVariant ="138";
adVarNumeric ="139";
adArray ="0x2000";
//---- DataTypeEnum "SimpleType" Array ----
SimpleType = ArrayNew(1);
SimpleType[16] = "Numeric";
SimpleType[2] = "Numeric";
SimpleType[3] = "Numeric";
SimpleType[20] = "Numeric";
SimpleType[17] = "Numeric";
SimpleType[18] = "Numeric";
SimpleType[19] = "Numeric";
SimpleType[21] = "Numeric";
SimpleType[4] = "Numeric";
SimpleType[5] = "Numeric";
SimpleType[6] = "Numeric";
SimpleType[14] = "Numeric";
SimpleType[131] = "Numeric";
SimpleType[11] = "Boolean";
SimpleType[10] = "Other";
SimpleType[132] = "Other";
SimpleType[12] = "Other";
SimpleType[9] = "Other";
SimpleType[13] = "Other";
SimpleType[72] = "Other";
SimpleType[7] = "Date";
SimpleType[133] = "Date";
SimpleType[134] = "Date";
SimpleType[135] = "Date";
SimpleType[8] = "Text";
SimpleType[129] = "Text";
SimpleType[200] = "Text";
SimpleType[201] = "Text";
SimpleType[130] = "Text";
SimpleType[202] = "Text";
SimpleType[203] = "Text";
SimpleType[128] = "Other";
SimpleType[204] = "Other";
SimpleType[205] = "Other";
SimpleType[136] = "Other";
//---- DataTypeEnum "DataBaseType" Array ----
DataBaseType = ArrayNew(1);
DataBaseType[16] = "TinyInt";
DataBaseType[2] = "SmallInt";
DataBaseType[3] = "Integer";
DataBaseType[20] = "BigInt";
DataBaseType[17] = "UnsignedTinyInt";
DataBaseType[18] = "UnsignedSmallInt";
DataBaseType[19] = "Int";
DataBaseType[21] = "UnsignedBigInt";
DataBaseType[4] = "Single";
DataBaseType[5] = "Double";
DataBaseType[6] = "Currency";
DataBaseType[14] = "Decimal";
DataBaseType[131] = "Numeric";
DataBaseType[11] = "Boolean";
DataBaseType[10] = "Error";
DataBaseType[132] = "UserDefined";
DataBaseType[12] = "Variant";
DataBaseType[9] = "IDispatch";
DataBaseType[13] = "IUnknown";
DataBaseType[72] = "GUID";
DataBaseType[7] = "Date";
DataBaseType[133] = "DBDate";
DataBaseType[134] = "DBTime";
DataBaseType[135] = "DBTimestamp";
DataBaseType[8] = "BStr";
DataBaseType[129] = "Char";
DataBaseType[200] = "VarChar";
DataBaseType[201] = "LongVarChar";
DataBaseType[130] = "WChar";
DataBaseType[202] = "VarWChar";
DataBaseType[203] = "LongVarWChar";
DataBaseType[128] = "Binary";
DataBaseType[204] = "VarBinary";
DataBaseType[205] = "LongVarBinary";
DataBaseType[136] = "Chapter";
//---- FieldAttributeEnum Values ----
adFldMayDefer ="0x00000002";
adFldUpdatable ="0x00000004";
adFldUnknownUpdatable ="0x00000008";
adFldFixed ="0x00000010";
adFldIsNullable ="0x00000020";
adFldMayBeNull ="0x00000040";
adFldLong ="0x00000080";
adFldRowID ="0x00000100";
adFldRowVersion ="0x00000200";
adFldCacheDeferred ="0x00001000";
adFldIsChapter ="0x00002000";
adFldNegativeScale ="0x00004000";
adFldKeyColumn ="0x00008000";
adFldIsRowURL ="0x00010000";
adFldIsDefaultStream ="0x00020000";
adFldIsCollection ="0x00040000";
//---- EditModeEnum Values ----
adEditNone ="0x0000";
adEditInProgress ="0x0001";
adEditAdd ="0x0002";
adEditDelete ="0x0004";
//---- RecordStatusEnum Values ----
adRecOK ="0x0000000";
adRecNew ="0x0000001";
adRecModified ="0x0000002";
adRecDeleted ="0x0000004";
adRecUnmodified ="0x0000008";
adRecInvalid ="0x0000010";
adRecMultipleChanges ="0x0000040";
adRecPendingChanges ="0x0000080";
adRecCanceled ="0x0000100";
adRecCantRelease ="0x0000400";
adRecConcurrencyViolation ="0x0000800";
adRecIntegrityViolation ="0x0001000";
adRecMaxChangesExceeded ="0x0002000";
adRecObjectOpen ="0x0004000";
adRecOutOfMemory ="0x0008000";
adRecPermissionDenied ="0x0010000";
adRecSchemaViolation ="0x0020000";
adRecDBDeleted ="0x0040000";
//---- GetRowsOptionEnum Values ----
adGetRowsRest ="-1";
//---- PositionEnum Values ----
adPosUnknown ="-1";
adPosBOF ="-2";
adPosEOF ="-3";
//---- BookmarkEnum Values ----
adBookmarkCurrent ="0";
adBookmarkFirst ="1";
adBookmarkLast ="2";
//---- MarshalOptionsEnum Values ----
adMarshalAll ="0";
adMarshalModifiedOnly ="1";
//---- AffectEnum Values ----
adAffectCurrent ="1";
adAffectGroup ="2";
adAffectAllChapters ="4";
//---- ResyncEnum Values ----
adResyncUnderlyingValues ="1";
adResyncAllValues ="2";
//---- CompareEnum Values ----
adCompareLessThan ="0";
adCompareEqual ="1";
adCompareGreaterThan ="2";
adCompareNotEqual ="3";
adCompareNotComparable ="4";
//---- FilterGroupEnum Values ----
adFilterNone ="0";
adFilterPendingRecords ="1";
adFilterAffectedRecords ="2";
adFilterFetchedRecords ="3";
adFilterConflictingRecords
//---- SearchDirectionEnum Values ----
adSearchForward ="1";
adSearchBackward ="-1";
//---- PersistFormatEnum Values ----
adPersistADTG ="0";
adPersistXML ="1";
//---- StringFormatEnum Values ----
adClipString ="2";
//---- ConnectPromptEnum Values ----
adPromptAlways ="1";
adPromptComplete ="2";
adPromptCompleteRequired ="3";
adPromptNever ="4";
//---- ConnectModeEnum Values ----
adModeUnknown ="0";
adModeRead ="1";
adModeWrite ="2";
adModeReadWrite ="3";
adModeShareDenyRead ="4";
adModeShareDenyWrite ="8";
adModeShareExclusive ="0xc";
adModeShareDenyNone ="0x10";
adModeRecursive ="0x400000";
//---- RecordCreateOptionsEnum Values ----
adCreateCollection ="0x00002000";
adCreateStructDoc ="0x80000000";
adCreateNonCollection ="0x00000000";
adOpenIfExists ="0x02000000";
adCreateOverwrite ="0x04000000";
adFailIfNotExists ="-1";
//---- RecordOpenOptionsEnum Values ----
adOpenRecordUnspecified ="-1";
adOpenSource ="0x00800000";
adOpenAsync ="0x00001000";
adDelayFetchStream ="0x00004000";
adDelayFetchFields ="0x00008000";
//---- IsolationLevelEnum Values ----
adXactUnspecified ="0xffffffff";
adXactChaos ="0x00000010";
adXactReadUncommitted ="0x00000100";
adXactBrowse ="0x00000100";
adXactCursorStability ="0x00001000";
adXactReadCommitted ="0x00001000";
adXactRepeatableRead ="0x00010000";
adXactSerializable ="0x00100000";
adXactIsolated ="0x00100000";
//---- XactAttributeEnum Values ----
adXactCommitRetaining ="0x00020000";
adXactAbortRetaining ="0x00040000";
//---- PropertyAttributesEnum Values ----
adPropNotSupported ="0x0000";
adPropRequired ="0x0001";
adPropOptional ="0x0002";
adPropRead ="0x0200";
adPropWrite ="0x0400";
//---- ErrorValueEnum Values ----
adErrProviderFailed ="0xbb8";
adErrInvalidArgument ="0xbb9";
adErrOpeningFile ="0xbba";
adErrReadFile ="0xbbb";
adErrWriteFile ="0xbbc";
adErrNoCurrentRecord ="0xbcd";
adErrIllegalOperation ="0xc93";
adErrCantChangeProvider ="0xc94";
adErrInTransaction ="0xcae";
adErrFeatureNotAvailable ="0xcb3";
adErrItemNotFound ="0xcc1";
adErrObjectInCollection ="0xd27";
adErrObjectNotSet ="0xd5c";
adErrDataConversion ="0xd5d";
adErrObjectClosed ="0xe78";
adErrObjectOpen ="0xe79";
adErrProviderNotFound ="0xe7a";
adErrBoundToCommand ="0xe7b";
adErrInvalidParamInfo ="0xe7c";
adErrInvalidConnection ="0xe7d";
adErrNotReentrant ="0xe7e";
adErrStillExecuting ="0xe7f";
adErrOperationCancelled ="0xe80";
adErrStillConnecting ="0xe81";
adErrInvalidTransaction ="0xe82";
adErrUnsafeOperation ="0xe84";
adwrnSecurityDialog ="0xe85";
adwrnSecurityDialogHeader ="0xe86";
adErrIntegrityViolation ="0xe87";
adErrPermissionDenied ="0xe88";
adErrDataOverflow ="0xe89";
adErrSchemaViolation ="0xe8a";
adErrSignMismatch ="0xe8b";
adErrCantConvertvalue ="0xe8c";
adErrCantCreate ="0xe8d";
adErrColumnNotOnThisRow ="0xe8e";
adErrURLIntegrViolSetColum
adErrURLDoesNotExist ="0xe8f";
adErrTreePermissionDenied ="0xe90";
adErrInvalidURL ="0xe91";
adErrResourceLocked ="0xe92";
adErrResourceExists ="0xe93";
adErrCannotComplete ="0xe94";
adErrVolumeNotFound ="0xe95";
adErrOutOfSpace ="0xe96";
adErrResourceOutOfScope ="0xe97";
adErrUnavailable ="0xe98";
adErrURLNamedRowDoesNotExi
adErrDelResOutOfScope ="0xe9a";
adErrPropInvalidColumn ="0xe9b";
adErrPropInvalidOption ="0xe9c";
adErrPropInvalidValue ="0xe9d";
adErrPropConflicting ="0xe9e";
adErrPropNotAllSettable ="0xe9f";
adErrPropNotSet ="0xea0";
adErrPropNotSettable ="0xea1";
adErrPropNotSupported ="0xea2";
adErrCatalogNotSet ="0xea3";
adErrCantChangeConnection ="0xea4";
adErrFieldsUpdateFailed ="0xea5";
adErrDenyNotSupported ="0xea6";
adErrDenyTypeNotSupported ="0xea7";
//---- ParameterAttributesEnum Values ----
adParamSigned ="0x0010";
adParamNullable ="0x0040";
adParamLong ="0x0080";
//---- ParameterDirectionEnum Values ----
adParamUnknown ="0x0000";
adParamInput ="0x0001";
adParamOutput ="0x0002";
adParamInputOutput ="0x0003";
adParamReturnValue ="0x0004";
//---- CommandTypeEnum Values ----
adCmdUnknown ="0x0008";
adCmdText ="0x0001";
adCmdTable ="0x0002";
adCmdStoredProc ="0x0004";
adCmdFile ="0x0100";
adCmdTableDirect ="0x0200";
//---- EventStatusEnum Values ----
adStatusOK ="0x0000001";
adStatusErrorsOccurred ="0x0000002";
adStatusCantDeny ="0x0000003";
adStatusCancel ="0x0000004";
adStatusUnwantedEvent ="0x0000005";
//---- EventReasonEnum Values ----
adRsnAddNew ="1";
adRsnDelete ="2";
adRsnUpdate ="3";
adRsnUndoUpdate ="4";
adRsnUndoAddNew ="5";
adRsnUndoDelete ="6";
adRsnRequery ="7";
adRsnResynch ="8";
adRsnClose ="9";
adRsnMove ="10";
adRsnFirstChange ="11";
adRsnMoveFirst ="12";
adRsnMoveNext ="13";
adRsnMovePrevious ="14";
adRsnMoveLast ="15";
//---- SchemaEnum Values ----
adSchemaProviderSpecific ="-1";
adSchemaAsserts ="0";
adSchemaCatalogs ="1";
adSchemaCharacterSets ="2";
adSchemaCollations ="3";
adSchemaColumns ="4";
adSchemaCheckConstraints ="5";
adSchemaConstraintColumnUs
adSchemaConstraintTableUsa
adSchemaKeyColumnUsage ="8";
adSchemaReferentialConstra
adSchemaTableConstraints ="10";
adSchemaColumnsDomainUsage
adSchemaIndexes ="12";
adSchemaColumnPrivileges ="13";
adSchemaTablePrivileges ="14";
adSchemaUsagePrivileges ="15";
adSchemaProcedures ="16";
adSchemaSchemata ="17";
adSchemaSQLLanguages ="18";
adSchemaStatistics ="19";
adSchemaTables ="20";
adSchemaTranslations ="21";
adSchemaProviderTypes ="22";
adSchemaViews ="23";
adSchemaViewColumnUsage ="24";
adSchemaViewTableUsage ="25";
adSchemaProcedureParameter
adSchemaForeignKeys ="27";
adSchemaPrimaryKeys ="28";
adSchemaProcedureColumns ="29";
adSchemaDBInfoKeywords ="30";
adSchemaDBInfoLiterals ="31";
adSchemaCubes ="32";
adSchemaDimensions ="33";
adSchemaHierarchies ="34";
adSchemaLevels ="35";
adSchemaMeasures ="36";
adSchemaProperties ="37";
adSchemaMembers ="38";
adSchemaTrustees ="39";
//---- FieldStatusEnum Values ----
adFieldOK ="0";
adFieldCantConvertValue ="2";
adFieldIsNull ="3";
adFieldTruncated ="4";
adFieldSignMismatch ="5";
adFieldDataOverflow ="6";
adFieldCantCreate ="7";
adFieldUnavailable ="8";
adFieldPermissionDenied ="9";
adFieldIntegrityViolation ="10";
adFieldSchemaViolation ="11";
adFieldBadStatus ="12";
adFieldDefault ="13";
adFieldIgnore ="15";
adFieldDoesNotExist ="16";
adFieldInvalidURL ="17";
adFieldResourceLocked ="18";
adFieldResourceExists ="19";
adFieldCannotComplete ="20";
adFieldVolumeNotFound ="21";
adFieldOutOfSpace ="22";
adFieldCannotDeleteSource ="23";
adFieldReadOnly ="24";
adFieldResourceOutOfScope ="25";
adFieldAlreadyExists ="26";
adFieldPendingInsert ="0x10000";
adFieldPendingDelete ="0x20000";
adFieldPendingChange ="0x40000";
adFieldPendingUnknown ="0x80000";
adFieldPendingUnknownDelet
//---- SeekEnum Values ----
adSeekFirstEQ ="0x1";
adSeekLastEQ ="0x2";
adSeekAfterEQ ="0x4";
adSeekAfter ="0x8";
adSeekBeforeEQ ="0x10";
adSeekBefore ="0x20";
//---- ADCPROP_UPDATECRITERIA_ENU
adCriteriaKey ="0";
adCriteriaAllCols ="1";
adCriteriaUpdCols ="2";
adCriteriaTimeStamp ="3";
//---- ADCPROP_ASYNCTHREADPRIORIT
adPriorityLowest ="1";
adPriorityBelowNormal ="2";
adPriorityNormal ="3";
adPriorityAboveNormal ="4";
adPriorityHighest ="5";
//---- ADCPROP_AUTORECALC_ENUM Values ----
adRecalcUpFront ="0";
adRecalcAlways ="1";
//---- ADCPROP_UPDATERESYNC_ENUM Values ----
//---- ADCPROP_UPDATERESYNC_ENUM Values ----
//---- MoveRecordOptionsEnum Values ----
adMoveUnspecified ="-1";
adMoveOverWrite ="1";
adMoveDontUpdateLinks ="2";
adMoveAllowEmulation ="4";
//---- CopyRecordOptionsEnum Values ----
adCopyUnspecified ="-1";
adCopyOverWrite ="1";
adCopyAllowEmulation ="4";
adCopyNonRecursive ="2";
//---- StreamTypeEnum Values ----
adTypeBinary ="1";
adTypeText ="2";
//---- LineSeparatorEnum Values ----
adLF ="10";
adCR ="13";
adCRLF ="-1";
//---- StreamOpenOptionsEnum Values ----
adOpenStreamUnspecified ="-1";
adOpenStreamAsync ="1";
adOpenStreamFromRecord ="4";
//---- StreamWriteEnum Values ----
adWriteChar ="0";
adWriteLine ="1";
//---- SaveOptionsEnum Values ----
adSaveCreateNotExist ="1";
adSaveCreateOverWrite ="2";
//---- FieldEnum Values ----
adDefaultStream ="-1";
adRecordURL ="-2";
//---- StreamReadEnum Values ----
adReadAll ="-1";
adReadLine ="-2";
//---- RecordTypeEnum Values ----
adSimpleRecord ="0";
adCollectionRecord ="1";
adStructDoc ="2";
</cfscript>
<cfobject type="COM"
name="ConnectionObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="D:\Inetpub\w
Source="Driver=#Driver#;DB
ConnectionObject.Open("#So
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=ConnectionObje
AllRecords=MyFieldList.Fie
/*These prperties are available
through the OpenSchema method.*/
ColumnName=AllRecords.Item
DataType=AllRecords.Item("
TableName=AllRecords.Item(
while(NOT MyFieldList.EOF){
WriteOutput(TableName.valu
WriteOutput(" <br> ");
WriteOutput(ColumnName.val
WriteOutput(" <br> ");
WriteOutput(DataBaseType[D
WriteOutput(" <br> ");
WriteOutput(SimpleType[Dat
WriteOutput(" <hr> ");
MyFieldList.MoveNext();
}
</cfscript>
<!--- Close Object--->
<cfset ConnectionObject.Close()>
just change the following to the appropriate path
DataBasePath="D:\Inetpub\w wwroot\EE\ ee.mdb";
DataBasePath="D:\Inetpub\w
Well there is a custom tag that can be used :
pls go thru the code below - there are 2 files !
dbinfo.cfm ========================== ========== ========== ========== ========== ========== =====
<!---
========================== ========== ========== ========== ========== =
utility: cf_dbinfo
author: anuj gakhar
email: jimmy_282@yahoo.com
========================== ========== ========== ========== ========== =
description:
this tag will give you all the information about your MSACCESS,MSSQL and MYsql databases.
For MSAccess:
tables,queries,reports,for ms,modules and macros
For MSSQL:
tables, triggers, stored procedures.
For Mysql:
tables
For MSSQL and Mysql:
this tag can return column names and types of a particular table or yield info abt a sp or trigger.
For MSACESS:
this tag makes use of the msysobjects system object lying inside
your databse. if you get an error like
"there is no read permission on msysobjects"
while executing this tag, then follow these steps.
1) open your .mdb file in microsoft access.
2) go to tools->security->user and group permissions.
3) click on msysobjects and assign it the "read data" permission.
now, if you don't see msysobjects in "user group and permissions"
follow these steps.
1)go to tools->options->view.
2)check the "system objects" checkbox.
3)click ok.
__________________________ __________ __________ _
Keep this header IN here if you are using this tag
Written By : Anuj Gakhar
E-mail : anuj@the-whizkids.com
__________________________ __________ __________ __
--->
<CFPARAM name="proceed" default="yes">
<CFPARAM name="error_message" default="">
<CFPARAM name="attributes.dsn" default="">
<CFPARAM name="attributes.dbusernam e" default="">
<CFPARAM name="attributes.dbpasswor d" default="">
<CFPARAM name="attributes.dbtype" default="MSACCESS">
<CFPARAM name="attributes.object" default=""><!--- check for required attribute --->
<CFIF (isdefined("attributes.dsn ") is "no")>
<CFSET proceed = "no">
<CFSET error_message = "dsn attribute is required!">
</CFIF>
<!--- check that required attribute is not empty --->
<CFIF trim("#attributes.dsn#") eq "">
<CFSET proceed = "no">
<CFSET error_message = "dsn attribute should not be left blank!">
</CFIF>
<CFIF not Listfind("MSACCESS,MSSQL,M YSQL","#at tributes.d btype#")>
<CFSET proceed = "no">
<CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported">
</CFIF>
<CFIF proceed> <!--- send a dummy query to the db to test the connectivity --->
<CFTRY>
<CFQUERY datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select 1;
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed = "no">
<CFSET error_message = "#cfcatch.message#"></CFCA TCH></CFTR Y>
</CFIF>
<!--- everything seems to be fine, so let them in --->
<CFIF proceed>
<CFSWITCH expression="#attributes.db type#">
<CFCASE value="MSACCESS"><!--- tables --->
<CFQUERY name="gettables" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (left$([name],4) <> 'msys')
and (msysobjects.type)=1
order by msysobjects.name;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#name#) #>
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables><!--- queries --->
<CFQUERY name="getqueries" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)=5
order by msysobjects.name;
</CFQUERY>
<CFSET queries = "">
<CFLOOP query="getqueries">
<CFSET tables= #listappend(queries,#name# )#>
</CFLOOP>
<CFSET caller.queries = queries><!--- forms --->
<CFQUERY name="getforms" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)=-32768
order by msysobjects.name;
</CFQUERY>
<CFSET forms="">
<CFLOOP query="getforms">
<CFSET forms = #listappend(forms,#name#)# >
</CFLOOP>
<CFSET caller.forms = forms><!--- reports --->
<CFQUERY name="getreports" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32764
order by msysobjects.name;
</CFQUERY>
<CFSET reports="">
<CFLOOP query="getreports">
<CFSET reports = #listappend(reports,#name# )#>
</CFLOOP>
<CFSET caller.reports=reports><!- -- modules --->
<CFQUERY name="getmodules" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32761
order by msysobjects.name;
</CFQUERY>
<CFSET modules="">
<CFLOOP query="getmodules">
<CFSET modules = #listappend(modules,#name# )#>
</CFLOOP>
<CFSET caller.modules=modules><!- -- macros --->
<CFQUERY name="getmacros" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32766
order by msysobjects.name;
</CFQUERY>
<CFSET macros="">
<CFLOOP query="getmacros">
<CFSET macros = #listappend(macros,#name#) #>
</CFLOOP>
<CFSET caller.macros=macros></CFC ASE>
<CFCASE value="MSSQL">
<CFIF not len("#attributes.object#") > <!--- tables --->
<CFQUERY name="gettables" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name ;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#name#) #>
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables><!--- stored Procedures --->
<CFQUERY name="getsps" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
SELECT name FROM sysobjects
WHERE type='P' AND
ObjectProperty(ID,'IsMSShi pped')=0 ;
</CFQUERY>
<CFSET sps = "">
<CFLOOP query="getsps">
<CFSET #listappend(sps,#name#)#>
</CFLOOP>
<CFSET caller.sps = sps><!--- Triggers --->
<CFQUERY name="gettriggers" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
SELECT name FROM sysobjects WHERE xtype='TR';
</CFQUERY>
<CFSET triggers = "">
<CFLOOP query="gettriggers">
<CFSET #listappend(triggers,#name #)#>
</CFLOOP>
<CFSET caller.triggers = triggers>
<CFELSE>
<!--- see the object passes is a table or sp or trigger--->
<CFQUERY name="ch" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
SELECT name FROM sysobjects WHERE xtype='u' AND name='#trim(attributes.obj ect)#' ORDER BY name ;
</CFQUERY>
<CFIF ch.recordcount> <!--- Its a table --->
<CFTRY>
<CFQUERY name="ch1" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
exec sp_columns @table_name='#trim(attribu tes.object )#';
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed="no">
<CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
</CFOUTPUT>
<CFABORT></CFCATCH></CFTRY >
<CFLOOP query="ch1">
<CFOUTPUT>
#ch1.column_name# --- #ch1.type_name#
<CFIF #ch1.type_name# EQ "varchar" OR #ch1.type_name# EQ "char"> (#ch1.length#)
</CFIF>
<BR>
</CFOUTPUT>
</CFLOOP>
<CFELSE>
<!--- Its a SP or Trigger --->
<CFTRY>
<CFQUERY name="ch1" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
exec sp_helptext '#trim(attributes.object)# ';
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed="no">
<CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
</CFOUTPUT>
<CFABORT></CFCATCH></CFTRY >
<CFLOOP query="ch1">
<CFOUTPUT>
#ch1.text#<BR>
</CFOUTPUT>
</CFLOOP>
</CFIF>
</CFIF>
</CFCASE>
<CFCASE value="MYSQL">
<CFQUERY name="getdb" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
select database() as d;
</CFQUERY>
<CFIF not len("#attributes.object#") > <!--- tables --->
<CFQUERY name="gettables" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
show tables ;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#Evalua te("tables _in_" & #getdb.d#)#)#>
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables>
<CFELSE>
<CFQUERY name="ch1" datasource="#attributes.ds n#" username="#attributes.dbus ername#" password="#attributes.dbpa ssword#">
describe #trim(attributes.object)#;
</CFQUERY>
<STRONG>Field - Type - Null - Key - Default - Extra</STRONG><BR>
<CFLOOP query="ch1">
<CFOUTPUT>
#field# - #type# - #null# - #key# - #default# - #extra#<BR>
</CFOUTPUT>
</CFLOOP>
</CFIF>
</CFCASE>
<CFDEFAULTCASE>
<CFSET proceed = "no">
<CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported"></CFDEFAULTCASE ></CFSWITC H>
<CFELSE>
<CFOUTPUT>
<H4>dbinfo error!<BR>#error_message#< /H4>
</CFOUTPUT>
<CFABORT>
</CFIF>
dbinfotest.cfm============ ========== ========== ========== ========== ========== ========== ==
<!--- Some Example Usage
FOR MSACCESS
<cf_dbinfo dsn="test" dbusername="sa" dbpassword="123">
no need of dbtype here - default is MSACCESS
-------------------------- ---------- ----
FOR MSSQL
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="mytable">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyStoredProc">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyTrigger">
-------------------------- ---------- -
FOR MYSQL
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="root" dbpassword="">
OR
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="sa" dbpassword="123" object = "MyTable">
-------------------------- ---------- -
__________________________ __________ __________ ____
All Errors, Bugs, Feature Requests, Comments to anuj@the-whizkids.com
__________________________ __________ __________ _____
ENJOY!
--->
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
<cfoutput>
<!--- Display Tables --->
<cfif isdefined("tables")>
<cfif listlen(tables)>
<strong><u>Tables</u></str ong><br>
<cfloop from="1" to="#listlen(tables)#" index="i">
#ListGetAt(tables,"#i#")# <br>
</cfloop>
<cfelse>
No Tables!
</cfif>
</cfif>
<br>
<!--- Display Queries --->
<cfif isdefined("queries")>
<cfif listlen(queries)>
<strong><u>Queries</u></st rong><br>
<cfloop from="1" to="#listlen(queries)#" index="i">
#ListGetAt(queries,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Queries!</strong>
</cfif>
</cfif>
<br>
<!--- Display Reports --->
<cfif isdefined("reports")>
<cfif listlen(Reports)>
<strong><u>Reports</u></st rong><br>
<cfloop from="1" to="#listlen(reports)#" index="i">
#ListGetAt(reports,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Reports!</strong>
</cfif>
</cfif>
<br>
<!--- Display Forms --->
<cfif isdefined("forms")>
<cfif listlen(Forms)>
<strong><u>Forms</u></stro ng><br>
<cfloop from="1" to="#listlen(forms)#" index="i">
#ListGetAt(forms,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Forms!</strong>
</cfif>
</cfif>
<br>
<!--- Display Macros --->
<cfif isdefined("macros")>
<cfif listlen(Macros)>
<strong><u>Macros</u></str ong><br>
<cfloop from="1" to="#listlen(macros)#" index="i">
#ListGetAt(macros,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Macros!</strong>
</cfif>
</cfif>
<br>
<!--- Display Modules --->
<cfif isdefined("modules")>
<cfif listlen(Modules)>
<strong><u>Modules</u></st rong><br>
<cfloop from="1" to="#listlen(modules)#" index="i">
#ListGetAt(modules,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Modules!</strong>
</cfif>
</cfif>
<!--- Display Stored Procedures --->
<cfif isdefined("sps")>
<cfif listlen(sps)>
<strong><u>Stored Procedures</u></strong><br >
<cfloop from="1" to="#listlen(sps)#" index="i">
#ListGetAt(sps,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Stored procedures!</strong>
</cfif>
</cfif>
<!--- Display Stored Procedures --->
<cfif isdefined("triggers")>
<cfif listlen(triggers)>
<strong><u>Triggers</u></s trong><br>
<cfloop from="1" to="#listlen(triggers)#" index="i">
#ListGetAt(triggers,"#i#") # <br>
</cfloop>
<cfelse>
<strong>No Triggers!</strong>
</cfif>
</cfif>
</cfoutput>
========================== ========== ========== ========== ========== ========== ==
that shld solve ur worries for "Only MSACCESS,MSSQL and MYSQL"
K'Rgds
Anand
pls go thru the code below - there are 2 files !
dbinfo.cfm ==========================
<!---
==========================
utility: cf_dbinfo
author: anuj gakhar
email: jimmy_282@yahoo.com
==========================
description:
this tag will give you all the information about your MSACCESS,MSSQL and MYsql databases.
For MSAccess:
tables,queries,reports,for
For MSSQL:
tables, triggers, stored procedures.
For Mysql:
tables
For MSSQL and Mysql:
this tag can return column names and types of a particular table or yield info abt a sp or trigger.
For MSACESS:
this tag makes use of the msysobjects system object lying inside
your databse. if you get an error like
"there is no read permission on msysobjects"
while executing this tag, then follow these steps.
1) open your .mdb file in microsoft access.
2) go to tools->security->user and group permissions.
3) click on msysobjects and assign it the "read data" permission.
now, if you don't see msysobjects in "user group and permissions"
follow these steps.
1)go to tools->options->view.
2)check the "system objects" checkbox.
3)click ok.
__________________________
Keep this header IN here if you are using this tag
Written By : Anuj Gakhar
E-mail : anuj@the-whizkids.com
__________________________
--->
<CFPARAM name="proceed" default="yes">
<CFPARAM name="error_message" default="">
<CFPARAM name="attributes.dsn" default="">
<CFPARAM name="attributes.dbusernam
<CFPARAM name="attributes.dbpasswor
<CFPARAM name="attributes.dbtype" default="MSACCESS">
<CFPARAM name="attributes.object" default=""><!--- check for required attribute --->
<CFIF (isdefined("attributes.dsn
<CFSET proceed = "no">
<CFSET error_message = "dsn attribute is required!">
</CFIF>
<!--- check that required attribute is not empty --->
<CFIF trim("#attributes.dsn#") eq "">
<CFSET proceed = "no">
<CFSET error_message = "dsn attribute should not be left blank!">
</CFIF>
<CFIF not Listfind("MSACCESS,MSSQL,M
<CFSET proceed = "no">
<CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported">
</CFIF>
<CFIF proceed> <!--- send a dummy query to the db to test the connectivity --->
<CFTRY>
<CFQUERY datasource="#attributes.ds
select 1;
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed = "no">
<CFSET error_message = "#cfcatch.message#"></CFCA
</CFIF>
<!--- everything seems to be fine, so let them in --->
<CFIF proceed>
<CFSWITCH expression="#attributes.db
<CFCASE value="MSACCESS"><!--- tables --->
<CFQUERY name="gettables" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (left$([name],4) <> 'msys')
and (msysobjects.type)=1
order by msysobjects.name;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#name#)
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables><!--- queries --->
<CFQUERY name="getqueries" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)=5
order by msysobjects.name;
</CFQUERY>
<CFSET queries = "">
<CFLOOP query="getqueries">
<CFSET tables= #listappend(queries,#name#
</CFLOOP>
<CFSET caller.queries = queries><!--- forms --->
<CFQUERY name="getforms" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)=-32768
order by msysobjects.name;
</CFQUERY>
<CFSET forms="">
<CFLOOP query="getforms">
<CFSET forms = #listappend(forms,#name#)#
</CFLOOP>
<CFSET caller.forms = forms><!--- reports --->
<CFQUERY name="getreports" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32764
order by msysobjects.name;
</CFQUERY>
<CFSET reports="">
<CFLOOP query="getreports">
<CFSET reports = #listappend(reports,#name#
</CFLOOP>
<CFSET caller.reports=reports><!-
<CFQUERY name="getmodules" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32761
order by msysobjects.name;
</CFQUERY>
<CFSET modules="">
<CFLOOP query="getmodules">
<CFSET modules = #listappend(modules,#name#
</CFLOOP>
<CFSET caller.modules=modules><!-
<CFQUERY name="getmacros" datasource="#attributes.ds
select msysobjects.name from msysobjects where
(left$([name],1)<>'~')
and (msysobjects.type)= -32766
order by msysobjects.name;
</CFQUERY>
<CFSET macros="">
<CFLOOP query="getmacros">
<CFSET macros = #listappend(macros,#name#)
</CFLOOP>
<CFSET caller.macros=macros></CFC
<CFCASE value="MSSQL">
<CFIF not len("#attributes.object#")
<CFQUERY name="gettables" datasource="#attributes.ds
SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name ;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#name#)
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables><!--- stored Procedures --->
<CFQUERY name="getsps" datasource="#attributes.ds
SELECT name FROM sysobjects
WHERE type='P' AND
ObjectProperty(ID,'IsMSShi
</CFQUERY>
<CFSET sps = "">
<CFLOOP query="getsps">
<CFSET #listappend(sps,#name#)#>
</CFLOOP>
<CFSET caller.sps = sps><!--- Triggers --->
<CFQUERY name="gettriggers" datasource="#attributes.ds
SELECT name FROM sysobjects WHERE xtype='TR';
</CFQUERY>
<CFSET triggers = "">
<CFLOOP query="gettriggers">
<CFSET #listappend(triggers,#name
</CFLOOP>
<CFSET caller.triggers = triggers>
<CFELSE>
<!--- see the object passes is a table or sp or trigger--->
<CFQUERY name="ch" datasource="#attributes.ds
SELECT name FROM sysobjects WHERE xtype='u' AND name='#trim(attributes.obj
</CFQUERY>
<CFIF ch.recordcount> <!--- Its a table --->
<CFTRY>
<CFQUERY name="ch1" datasource="#attributes.ds
exec sp_columns @table_name='#trim(attribu
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed="no">
<CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
</CFOUTPUT>
<CFABORT></CFCATCH></CFTRY
<CFLOOP query="ch1">
<CFOUTPUT>
#ch1.column_name# --- #ch1.type_name#
<CFIF #ch1.type_name# EQ "varchar" OR #ch1.type_name# EQ "char"> (#ch1.length#)
</CFIF>
<BR>
</CFOUTPUT>
</CFLOOP>
<CFELSE>
<!--- Its a SP or Trigger --->
<CFTRY>
<CFQUERY name="ch1" datasource="#attributes.ds
exec sp_helptext '#trim(attributes.object)#
</CFQUERY>
<CFCATCH type="database">
<CFSET proceed="no">
<CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
</CFOUTPUT>
<CFABORT></CFCATCH></CFTRY
<CFLOOP query="ch1">
<CFOUTPUT>
#ch1.text#<BR>
</CFOUTPUT>
</CFLOOP>
</CFIF>
</CFIF>
</CFCASE>
<CFCASE value="MYSQL">
<CFQUERY name="getdb" datasource="#attributes.ds
select database() as d;
</CFQUERY>
<CFIF not len("#attributes.object#")
<CFQUERY name="gettables" datasource="#attributes.ds
show tables ;
</CFQUERY>
<!--- add the tables to a list --->
<CFSET tables = "">
<CFLOOP query="gettables">
<CFSET tables= #listappend(tables,#Evalua
</CFLOOP>
<!--- send this list to the caller template --->
<CFSET caller.tables = tables>
<CFELSE>
<CFQUERY name="ch1" datasource="#attributes.ds
describe #trim(attributes.object)#;
</CFQUERY>
<STRONG>Field - Type - Null - Key - Default - Extra</STRONG><BR>
<CFLOOP query="ch1">
<CFOUTPUT>
#field# - #type# - #null# - #key# - #default# - #extra#<BR>
</CFOUTPUT>
</CFLOOP>
</CFIF>
</CFCASE>
<CFDEFAULTCASE>
<CFSET proceed = "no">
<CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported"></CFDEFAULTCASE
<CFELSE>
<CFOUTPUT>
<H4>dbinfo error!<BR>#error_message#<
</CFOUTPUT>
<CFABORT>
</CFIF>
dbinfotest.cfm============
<!--- Some Example Usage
FOR MSACCESS
<cf_dbinfo dsn="test" dbusername="sa" dbpassword="123">
no need of dbtype here - default is MSACCESS
--------------------------
FOR MSSQL
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="mytable">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyStoredProc">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyTrigger">
--------------------------
FOR MYSQL
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="root" dbpassword="">
OR
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="sa" dbpassword="123" object = "MyTable">
--------------------------
__________________________
All Errors, Bugs, Feature Requests, Comments to anuj@the-whizkids.com
__________________________
ENJOY!
--->
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
<cfoutput>
<!--- Display Tables --->
<cfif isdefined("tables")>
<cfif listlen(tables)>
<strong><u>Tables</u></str
<cfloop from="1" to="#listlen(tables)#" index="i">
#ListGetAt(tables,"#i#")# <br>
</cfloop>
<cfelse>
No Tables!
</cfif>
</cfif>
<br>
<!--- Display Queries --->
<cfif isdefined("queries")>
<cfif listlen(queries)>
<strong><u>Queries</u></st
<cfloop from="1" to="#listlen(queries)#" index="i">
#ListGetAt(queries,"#i#")#
</cfloop>
<cfelse>
<strong>No Queries!</strong>
</cfif>
</cfif>
<br>
<!--- Display Reports --->
<cfif isdefined("reports")>
<cfif listlen(Reports)>
<strong><u>Reports</u></st
<cfloop from="1" to="#listlen(reports)#" index="i">
#ListGetAt(reports,"#i#")#
</cfloop>
<cfelse>
<strong>No Reports!</strong>
</cfif>
</cfif>
<br>
<!--- Display Forms --->
<cfif isdefined("forms")>
<cfif listlen(Forms)>
<strong><u>Forms</u></stro
<cfloop from="1" to="#listlen(forms)#" index="i">
#ListGetAt(forms,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Forms!</strong>
</cfif>
</cfif>
<br>
<!--- Display Macros --->
<cfif isdefined("macros")>
<cfif listlen(Macros)>
<strong><u>Macros</u></str
<cfloop from="1" to="#listlen(macros)#" index="i">
#ListGetAt(macros,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Macros!</strong>
</cfif>
</cfif>
<br>
<!--- Display Modules --->
<cfif isdefined("modules")>
<cfif listlen(Modules)>
<strong><u>Modules</u></st
<cfloop from="1" to="#listlen(modules)#" index="i">
#ListGetAt(modules,"#i#")#
</cfloop>
<cfelse>
<strong>No Modules!</strong>
</cfif>
</cfif>
<!--- Display Stored Procedures --->
<cfif isdefined("sps")>
<cfif listlen(sps)>
<strong><u>Stored Procedures</u></strong><br
<cfloop from="1" to="#listlen(sps)#" index="i">
#ListGetAt(sps,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Stored procedures!</strong>
</cfif>
</cfif>
<!--- Display Stored Procedures --->
<cfif isdefined("triggers")>
<cfif listlen(triggers)>
<strong><u>Triggers</u></s
<cfloop from="1" to="#listlen(triggers)#" index="i">
#ListGetAt(triggers,"#i#")
</cfloop>
<cfelse>
<strong>No Triggers!</strong>
</cfif>
</cfif>
</cfoutput>
==========================
that shld solve ur worries for "Only MSACCESS,MSSQL and MYSQL"
K'Rgds
Anand
anand, this won't give you the fields in the tables, just the higher level objects (forms, table names, modules, etc.) and only if you modify the database security settings.
ASKER
I need to see field names, types, and keys. If you are correct, jyokum, and this utility cannot provide that information, it won't help.
Have any of you tried using it?
I was really hoping for something simple like the DESCRIBE function in some flavors of SQL.
Have any of you tried using it?
I was really hoping for something simple like the DESCRIBE function in some flavors of SQL.
did you try using the code I posted? it displays the field names, types, and lengths
ASKER
I haven't tried it. Can you tell me how to implement it?
just to see it work, copy all the code to a new page.
change the following line to reflect the actual path of your database
DataBasePath="D:\Inetpub\w wwroot\EE\ ee.mdb";
then run it. All the table & fields will be displayed for you.
read the article at CFHub to get the particulars about it.
change the following line to reflect the actual path of your database
DataBasePath="D:\Inetpub\w
then run it. All the table & fields will be displayed for you.
read the article at CFHub to get the particulars about it.
ASKER
Hi jyokum,
Looks like you're on the right track. I found the tutorial you are referring to and am trying to work through the code. I created the adocfscript.cfm file (the one you pasted above, which appears on http://cfhub.com/advanced/ado/constants.cfm), and I'm trying to use the next script in the tutorial to connect to my database and there I'm having trouble. I created this file (based on tutorial part 2 at http://cfhub.com/advanced/ado/connection_string.cfm) and saved it as adotest.cfm:
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm" >
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="..\database\ mydb.mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
//Open a connection to the "northwinds" database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!! </h3>");
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
When I call up the file I get the following error message: "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." Obviously ColdFusion is looking for an ODBC connection, but the code seems to be trying to circumvent ODBC by creating a connection using ADO. How can I make it work?
Looks like you're on the right track. I found the tutorial you are referring to and am trying to work through the code. I created the adocfscript.cfm file (the one you pasted above, which appears on http://cfhub.com/advanced/ado/constants.cfm), and I'm trying to use the next script in the tutorial to connect to my database and there I'm having trouble. I created this file (based on tutorial part 2 at http://cfhub.com/advanced/ado/connection_string.cfm) and saved it as adotest.cfm:
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm"
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="..\database\
Source="Driver=#Driver#;DB
//Open a connection to the "northwinds" database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!!
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
When I call up the file I get the following error message: "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." Obviously ColdFusion is looking for an ODBC connection, but the code seems to be trying to circumvent ODBC by creating a connection using ADO. How can I make it work?
try using a full path instead of a relative path
c:\whatever\data.mdb
instead of
..\data.mdb
c:\whatever\data.mdb
instead of
..\data.mdb
ASKER
That helped, the ADO connection is now working. Now I'm trying to implement some ADO methods. I get errors whenever I run any of them. For example, here's a line of code and the error it produces:
WriteOutput(MyFieldList.Re cordCount) ;
"Error Occurred While Processing Request
Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
Now what?
WriteOutput(MyFieldList.Re
"Error Occurred While Processing Request
Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
Now what?
depending on the type of cursor, recordcount may or may not be available to you.
try using a client cursor
ConnectionObject.CursorLoc ation = adUseClient;
ConnectionObject.Open("#So urce#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
try using a client cursor
ConnectionObject.CursorLoc
ConnectionObject.Open("#So
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
ASKER
Now the whole thing bonks. Here's what I did: Commented out original code and replaced it with your code.
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
ConnectionObject.CursorLoc ation = adUseClient;
ConnectionObject.Open("#So urce#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
Here's the error: "Error resolving parameter CONNECTIONOBJECT.OPEN"
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
ConnectionObject.CursorLoc
ConnectionObject.Open("#So
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
Here's the error: "Error resolving parameter CONNECTIONOBJECT.OPEN"
replace ConnectionObject with MyObject
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
ASKER
Okay, that's fixed. Unfortunately it bonked again on the same line:
WriteOutput(MyFieldList.Re cordCount) ;
"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
WriteOutput(MyFieldList.Re
"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
ASKER
I'm outta here for the weekend. Will reply to any additional comments on Monday. Cheers.
Search for CF_DBINFO(*NEW*) on Macromedia Exchange
Thats my tag which does the same thing as you want.
Thats my tag which does the same thing as you want.
jimmy282,
this doesn't return column names/data types for MS Access
jyokum
this doesn't return column names/data types for MS Access
jyokum
ASKER
jyokum,
Would you continue walking me through the solution you proposed? I followed your suggestions so far, but got stuck at
WriteOutput(MyFieldList.Re cordCount) ;
"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
Would you continue walking me through the solution you proposed? I followed your suggestions so far, but got stuck at
WriteOutput(MyFieldList.Re
"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."
could you post all the code? I was able to output the recordcount without any problem.
ASKER
I got this code to work:
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm" >
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\myisp \data\mydb .mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
//Open a connection to the database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!! </h3>");
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
----------
Then I tried outputting some information about the database:
----------
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm" >
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\mnoff ice.media3 \data\Next Step.mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=MyObject.OpenS chema(adSc hemaColumn s);
WriteOutput(MyFieldList.Re cordCount) ;
WriteOutput("<br>");
WriteOutput("<h3>Success!! </h3>");
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
------------
It works until it hits the line "WriteOutput(MyFieldList.R ecordCount );"
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm"
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\myisp
Source="Driver=#Driver#;DB
//Open a connection to the database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!!
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
----------
Then I tried outputting some information about the database:
----------
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm"
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\mnoff
Source="Driver=#Driver#;DB
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=MyObject.OpenS
WriteOutput(MyFieldList.Re
WriteOutput("<br>");
WriteOutput("<h3>Success!!
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
------------
It works until it hits the line "WriteOutput(MyFieldList.R
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).
ASKER
I got this code to work:
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm" >
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\myisp \data\mydb .mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
//Open a connection to the database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!! </h3>");
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
----------
Then I tried outputting some information about the database:
----------
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm" >
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\mnoff ice.media3 \data\Next Step.mdb";
Source="Driver=#Driver#;DB Q=#DataBas ePath#";
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=MyObject.OpenS chema(adSc hemaColumn s);
WriteOutput(MyFieldList.Re cordCount) ;
WriteOutput("<br>");
WriteOutput("<h3>Success!! </h3>");
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
------------
It works until it hits the line "WriteOutput(MyFieldList.R ecordCount );"
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm"
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\myisp
Source="Driver=#Driver#;DB
//Open a connection to the database
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
WriteOutput("<h3>Success!!
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
----------
Then I tried outputting some information about the database:
----------
<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm"
<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
name="MyObject"
class="ADODB.Connection"
action="CREATE">
<cfscript>
//Use these variables to build a valid connection string
Driver="Microsoft Access Driver (*.mdb)";
DataBasePath="e:\web\mnoff
Source="Driver=#Driver#;DB
//Open a connection to the database
// MyObject.Open("#Source#",
// adModeUnknown,
// adFailIfNotExists,
// adOpenRecordUnspecified);
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
adModeUnknown,
adFailIfNotExists,
adOpenRecordUnspecified);
MyFieldList=MyObject.OpenS
WriteOutput(MyFieldList.Re
WriteOutput("<br>");
WriteOutput("<h3>Success!!
</cfscript>
<!--- Close --->
<cfset MyObject.Close()>
------------
It works until it hits the line "WriteOutput(MyFieldList.R
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried it again, no luck. The error message is "Server busy or unable to fulfill request. The server is unable to fulfill your request due to extremely high traffic or an unexpected internal error. Please attempt your request again (if you are repeatedly unsuccessful you should notify the site administrator). (Location Code: 26)"
you may want to check your server to make sure you don't have a ton of MSACCESS.exe processes sitting out there.
ASKER
I've got a request in to my hosting service. Will keep you posted.
ASKER
A quick update: Today my hosting service told me that the Access locking file (.ldb) is getting in the way of the ADO connection. That makes no sense to me; if that were true, it would never be possible to connect to an Access database via ADO, since the .ldb file is always created when the database is opened. I'm going to keep pushing and see if they can come up with a more convincing reason.
loe,
This has been open 145 days and there hasn't been a comment added in 116 days.
Please select a comment as the solution or give us an update.
jyokum
This has been open 145 days and there hasn't been a comment added in 116 days.
Please select a comment as the solution or give us an update.
jyokum