Database Discussion Boards

Hi Richard, thanks. Smile | :)

Everyday is a school day dealing with the humans I do. Smile | :)

Hi all,

I've done a small database and PHP based web page to be able to track my working hours easily.

Last week a customer asked me how many time in total each month I had spend in one project and I had to get all the times, filter by month manually and sum it all to give him an answer... a matter of 10 minutes, but I want to automate this kind of things...

My question is:

I will have a table that will be able to be grouped by day, week, month, year or not grouped, but then, I have to get the sum of all hours that I worked in my office, the sum of the hours that I worked out of my office, the sum of the hours travelling...

I can only think of doing something like

SQL

SELECT  sumA,
        sumB,
        sumC
FROM
(
  SELECT sumA,
         type,
         "0" as sumB,
         "0" as sumC
  From table1

  WHERE type = A

UNION ALL

  SELECT sumB,
         type,
         "0" as sumA,
         "0" as sumC
  From table1

  WHERE type = B

UNION ALL

  SELECT sumC,
         type,
         "0" as sumA,
         "0" as sumB
  From table1

  WHERE type = C
)
AS VIEW1

Is this the way to do it?

I want to show the data in columns:

            Hours at the office   |   hours out of the office   |   hours travelling
_____________________________________________________________________________________________________
JANUARY    |                      |                             |
_____________________________________________________________________________________________________
FEBRUARY   |                      |                             |
_____________________________________________________________________________________________________
MARCH      |                      |                             |
_____________________________________________________________________________________________________
APRIL      |                      |                             |
_____________________________________________________________________________________________________
...

And I would like to solve this in the database part, not creating different tables and then working in the PHP area...

Sorry for this way too much generic question, I hope I made it clear enough for you to enlighten me.

Thank you in advance!

www.robotecnik.com[^] - robots, CNC and PLC programming

Joan M wrote:
And I would like to solve this in the database part, not creating different tables and then working in the PHP area

Wrong tools for the jobs, database for servicing your data query and PHP for the presentation.

You could store your queries in a table, relate them to a query/stored proc and expose a title in a combo box for the user to select. Display would need to be an auto generated table to support different result sets.

The problem with this is that you may need to support different filtering parameters and you end up with an entire framework to support the reporting when it is faster and simpler to build a result page and query for each report request.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

hmmmm...

I meant this: I have all the data in the database, create a query and get the results that I show then in the web page using PHP.

I have all the "framework" done already, I can tune the queries easily filtering data, ordering and grouping at will...

But, maybe it would be much easier to create a "view" in PHPMyAdmin (I still don't know if this can be achieved but I'll look for it) and then the queries would be much easier.

After having the view, I would not have to create endless joins to get the data mixed correctly.

www.robotecnik.com[^] - robots, CNC and PLC programming

What database are you using?

If it is MS-SQL, then there is a command call PIVOT which will do exactly what you want to do.
Java | [Coffee]

MariaDB in this case, but thanks I'll take a look at PIVOT and see if there is something similar.

Smile | :)

www.robotecnik.com[^] - robots, CNC and PLC programming

First of all: "Sorry for my english!" I recently have created a calibration database via Ms Access DBMS. Here is the model:

https://i.stack.imgur.com/Z2uBf.jpg[^]

Each tool has "name abzarLa" field that is actually "Tool's English Name" that comes from ToolList Table. Here is sample values of "ToolList" Table.

https://i.stack.imgur.com/cPwSk.jpg[^]

now it's time to explain my problem: each Tool based on its "name abzarLa" relates to its specific "Data Table" which contains Calibration data results with specific fields. (One To Many relationship => each tools has multiple row in "Data Table") Here is data table that uses for insert "Pressure gauge" results:

https://i.stack.imgur.com/ZEQ7e.jpg
[^]

And here is data table that uses for insert "Oven", "incubaor", "cold Incubator" results:

https://i.stack.imgur.com/4uzOp.jpg[^]

Ok, So we have a Tools table and multiple "Data Tables". How can I design "Data Tables" and create relationship between "Tools" table and "Data Tables" base on "name abzarLa" field?
One Design is:

https://i.stack.imgur.com/6bhdu.jpg[^]

but I want each tool only relates with its specific [Data Table]

Relationships are defined between tables, not records. Your parent table will need a relationship to all relevant tables. Your query can then join to those tables depending on the data in the parent record.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Bonjour,

Sous visuel studio 2012, je développe une application web .net. J'ai besoin de connecter à une base des données oracle 9i. Je suis en train de chercher un driver pour l'ajouter comme référence dans l'application afin d'interroger la base. Le problème que je n'ai pas trouvé sur le net un driver oracle 9i

Où se trouve ce driver ? Comment l'installer et l'utiliser ?

Merci d'avance pour toute aide.

En Anglais, si'l vous plait.

You can use a newer driver without any problems.
So either you get the nuget package Oracle.ManagedDataAccess, which I strongly suspect isn't existing on VS2012.
So you can download the client from here: Oracle Data Access Components (ODAC) for Windows Downloads[^]

Wrong is evil and must be defeated. - Jeff Ello

I have the next error :

ORA-03111: Break received on communication channel

It seems I was in error about the backwards compatibility of the driver.

Version 11.2 should work though: Oracle Data Access Components (ODAC) for Windows Downloads[^]
Look at the bottom of the link.

Wrong is evil and must be defeated. - Jeff Ello

Bonjour,
Merci

Jörgen Andersson

but 32 bit Or I have windows 64 bit

I find alaways client oracle 11g for windows 64 bit in visual studio 2012

Visual studio is 32bit so you will not be able to debug unless you use a 32-bit client.

If it is a web application it will automatically run as a 64-bit application which is a bit problematic, so you will have to set the application pool to run as a 32-bit application.

Wrong is evil and must be defeated. - Jeff Ello

Bonjour,

what does means this error :

Erreur du serveur dans l'application '/'.
Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.
Description : Une exception non gérée s'est produite au moment de l'exécution de la requête Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code.

Détails de l'exception: System.BadImageFormatException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.

Erreur source:

Une exception non gérée s'est produite lors de l'exécution de la requête Web actuelle. Les informations relatives à l'origine et l'emplacement de l'exception peuvent être identifiées en utilisant la trace de la pile d'exception ci-dessous.

Suivi du chargement de l'assembly: Les informations suivantes peuvent permettre de déterminer la raison pour laquelle l'assembly 'Oracle.DataAccess' n'a pas pu être chargé.


AVT : la journalisation de liaison d'assembly est désactivée.
Pour activer la journalisation des échecs de liaison d'assembly, attribuez la valeur 1 à la valeur de Registre [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD).
Remarque : une certaine perte de performance est associée à l'enregistrement dans le journal des échecs de liaison d'assembly.
Pour désactiver cette fonctionnalité, supprimez la valeur de Registre [HKLM\Software\Microsoft\Fusion!EnableLog].

Trace de la pile:


[BadImageFormatException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0
   System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +37
   System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +159
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) +80
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +22
   System.Reflection.Assembly.Load(String assemblyString) +29
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +39

[ConfigurationErrorsException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +777
   System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +229
   System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +140
   System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +176
   System.Web.Compilation.BuildManager.GetPreStartInitMethodsFromReferencedAssemblies() +99
   System.Web.Compilation.BuildManager.CallPreStartInitMethods(String preStartInitListPath, Boolean& isRefAssemblyLoaded) +310
   System.Web.Compilation.BuildManager.ExecutePreAppStart() +165
   System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +590

[HttpException (0x80004005): Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +10082160
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +99
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +263

Informations sur la version : Version Microsoft .NET Framework :4.0.30319; Version ASP.NET :4.8.4110.0

This means you're trying to run a 64-bit program in a 32-bit environment or the opposite way around.

I'm Assuming you have installed a 32-bit client.

IIS is running all it's processes as 64 bits by default, which means it crashes if the oracle client is 32 bits.
You can change this by creating a new Application Pool for your web application, and open "Advanced settings" and "Enable 32-bit Applications".

Wrong is evil and must be defeated. - Jeff Ello

Bonjour,

what does means this error :

Erreur du serveur dans l'application '/'.
Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.
Description : Une exception non gérée s'est produite au moment de l'exécution de la requête Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code.

Détails de l'exception: System.BadImageFormatException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.

Erreur source:

Une exception non gérée s'est produite lors de l'exécution de la requête Web actuelle. Les informations relatives à l'origine et l'emplacement de l'exception peuvent être identifiées en utilisant la trace de la pile d'exception ci-dessous.

Suivi du chargement de l'assembly: Les informations suivantes peuvent permettre de déterminer la raison pour laquelle l'assembly 'Oracle.DataAccess' n'a pas pu être chargé.


AVT : la journalisation de liaison d'assembly est désactivée.
Pour activer la journalisation des échecs de liaison d'assembly, attribuez la valeur 1 à la valeur de Registre [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD).
Remarque : une certaine perte de performance est associée à l'enregistrement dans le journal des échecs de liaison d'assembly.
Pour désactiver cette fonctionnalité, supprimez la valeur de Registre [HKLM\Software\Microsoft\Fusion!EnableLog].

Trace de la pile:


[BadImageFormatException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0
   System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +37
   System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +159
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) +80
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +22
   System.Reflection.Assembly.Load(String assemblyString) +29
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +39

[ConfigurationErrorsException: Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +777
   System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +229
   System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +140
   System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +176
   System.Web.Compilation.BuildManager.GetPreStartInitMethodsFromReferencedAssemblies() +99
   System.Web.Compilation.BuildManager.CallPreStartInitMethods(String preStartInitListPath, Boolean& isRefAssemblyLoaded) +310
   System.Web.Compilation.BuildManager.ExecutePreAppStart() +165
   System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +590

[HttpException (0x80004005): Impossible de charger le fichier ou l'assembly 'Oracle.DataAccess' ou une de ses dépendances. Tentative de chargement d’un programme de format incorrect.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +10082160
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +99
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +263

Informations sur la version : Version Microsoft .NET Framework :4.0.30319; Version ASP.NET :4.8.4110.0

I am new in Mysql database. Please guide me how to connect shared server database with remote database details.

Port 3306 also open on server. when i try to connect with workbench tool . i got error failed to connect.

It could be virtually anything. Any luck connecting to the same server with the same credentials but with a different Mysql IDE?

The text below is from "Introduction to Database Systems, J. Date". I can't the bolded part. why since Relvar constraints must obviously be checked immediately, must database constraints be checked immediately too? Why database constraints must follow relvar constraints, please?

The previous edition of this book stated that relvar constraints were checked immediately but database constraints were checked at end-of-transaction (a position that many writers concur with, though they usually use different terminology). But The Principle of Interchangeability (of base and derived relvars--see Chapter 9) implies that the very same real-world constraint might be a relvar constraint with one design for the database and a database constraint with another! Since relvar constraints must obviously be checked immediately, it follows that database constraints must be checked immediately too. .

I'm trying to get the project GitHub - improvedk/OrcaMDF: A C# parser for MDF files. Allows you to read tables, metadata and indexes from MDF files without it being attached to a running SQL Server instance.[^] to work with the XML SQL Server data type. When I step in the debugger, I can see that I receive an array byte[8192] with XML-data (each ASCII character seems to occupy 2 bytes) in the file \OrcaMDF\src\OrcaMDF.Core\Engine\Records\TextRecord.cs:line 35. Does anybody know how I can retrieve the proper XML-string? I guess the start index and length of the string must be baked into the array somehow.

You could try

C#

string xml = System.Text.UnicodeEncoding.GetString(theByteArray);

If this does not produce proper result, you could also try one of other encodings provided by the framework: Encoding Class (System.Text) | Microsoft Docs[^].

"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."

I am writing a C# application in Visual Studio 2005 and I need to read, but NOT write, data inside an Azure database and I would like to install as few applications on my computer as possible. It would also be nice if I could simply spend a couple of minutes copying the whole database file to my computer upon startup and from then on access the data completely locally. There is no requirement that I should access the data I want through SQL-queries, if it's feasible to iterate through the database file with while-loops, if-statements, etc that would be perfectly fine with me. Is this feasible or what's the minimum I can get away with?

modified 25-Apr-20 13:31pm.

At the very least you will need to install a Database client to read the database. Which client you need will depend on what database it is that you wish to access.